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
- subscription-origin.patch (text/x-patch) patch
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
-
Fix a WARNING for data origin discrepancies.
- b4e0d0c53fab 18.0 landed
- 0ae1245e04ad 17.5 landed
- 1c2a2354c384 16.9 landed