create subscription with (origin = none, copy_data = on)

Sergey Tatarintsev <s.tatarintsev@postgrespro.ru>

From: Sergey Tatarintsev <s.tatarintsev@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-01-17T08:30:04Z
Lists: pgsql-hackers

Attachments

Hi, hackers!

I am looking at subscription creation command:

CREATE SUBSCRIPTION sub CONNECTION '...' PUBLICATION pub WITH (origin = 
none, copy_data = on);

For now we log a warning if the publisher has subscribed to the same 
table from some other publisher.
However, in case of publication with publish_via_partition_root option, 
we will not raise such warinigs
because SQL command in check_publications_origin() checks only directly 
published tables.
For example:

CREATE TABLE t(id int) PARTITION BY RANGE(id);
CREATE TABLE part1 PARTITION OF t FOR VALUES FROM (0) TO (5);
CREATE TABLE part2 PARTITION OF t FOR VALUES FROM (5) TO (10);
-- subscribe to part2
CREATE SUBSCRIPTION sub_part2 CONNECTION '...' PUBLICATION pub_part2;
CREATE PUBLICATION pub_t FOR TABLE t;
CREATE PUBLICATION pub_t_via_root FOR TABLE t WITH 
(publish_via_partition_root);

and now this command will raise a warning:
CREATE SUBSCRIPTION sub1 CONNECTION '...' PUBLICATION pub_t WITH (origin 
= none, copy_data = on);

but not this:
CREATE SUBSCRIPTION sub1 CONNECTION '...' PUBLICATION pub_t_via_root 
WITH (origin = none, copy_data = on);

We also do not take into account cases of foreign partitions:
CREATE TABLE t(id int) PARTITION BY RANGE(id);
CREATE TABLE part1 PARTITION OF t FOR VALUES FROM (0) TO (5);
CREATE FOREIGN TABLE part2 PARTITION OF t FOR VALUES FROM (5) TO (10) 
SERVER fdw_server;
CREATE PUBLICATION pub_t FOR TABLE t;

Maybe we should raise WARNING (or even ERROR) in such cases?
I would also note that the (origin = none) will work as expected, but in 
case of (origin = any)
it will lead to inappropriate behavior - we will perform an initial sync 
of "t", but we unable to
replicate further updates for "part2".

I have attached patch, demonstrating this problems

Commits

  1. Fix a WARNING for data origin discrepancies.