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

From: Sergey Tatarintsev <s(dot)tatarintsev(at)postgrespro(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: create subscription with (origin = none, copy_data = on)
Date: 2025-01-17 08:30:04
Message-ID: 5eda6a9c-63cf-404d-8a49-8dcb116a29f3@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
subscription-origin.patch text/x-patch 6.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ryo Kanbayashi 2025-01-17 08:46:57 Re: ecpg command does not warn COPY ... FROM STDIN;
Previous Message Yura Sokolov 2025-01-17 08:28:38 Re: Increase NUM_XLOGINSERT_LOCKS