On Thu, Feb 5, 2026 at 10:59 AM Shlok Kyal <[email protected]> wrote:
>
> I have added the fix of the same in the latest v41 patch and added the
> corresponding test in 101_test.pl file.
> I have also merged the v40-0001 and v40-0002 patches to form v41-0001
> patch and v41-0002 has the extended tests.
>
Thank You for the patched Shlok. While testing I found a case where
table-sync and incremental-sync are not replicating same set of
tables.
I have attached the test-case and results in DifferentPubViaRoot.txt
The problem happens when we have a subscriber subscribing to multiple
pubs with different EXCEPT and different PUBLISH_VIA_PARTITION_ROOT
value. Example:
CREATE PUBLICATION pub1 for ALL TABLES EXCEPT table (tab_part_1_p1,
tab_part_2_p2) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
CREATE PUBLICATION pub2 for ALL TABLES EXCEPT table (tab_part_2) WITH
(PUBLISH_VIA_PARTITION_ROOT=false);
We need to decide the behaviour of such a case for Apporach1. It will
be good to analyze row-filter and column-list behaviour for equivalent
cases i.e. publication publishing the same set of tables with
different row-filters and different PUBLISH_VIA_PARTITION_ROOT value.
Once we have clarity on that, we can decide about the behaviour here.
thanks
Shveta
tab_root (RANGE partitioned on range_col)
├── tab_part_1 (range_col: 1 → 1000)
│ ├── tab_part_1_p1 (range_col: 1 → 500)
│ └── tab_part_1_p2 (range_col: 500 → 1000)
└── tab_part_2 (range_col: 1000 → 2000)
├── tab_part_2_p1 (range_col: 1000 → 1500)
└── tab_part_2_p2 (range_col: 1500 → 2000)
CREATE TABLE tab_root (id int, range_col int) PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_1 PARTITION OF tab_root FOR VALUES FROM (1) TO (1000)
PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) TO (2000)
PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_1_p1 PARTITION OF tab_part_1 FOR VALUES FROM (1) TO (500);
CREATE TABLE tab_part_1_p2 PARTITION OF tab_part_1 FOR VALUES FROM (500) TO
(1000);
CREATE TABLE tab_part_2_p1 PARTITION OF tab_part_2 FOR VALUES FROM (1000) TO
(1500);
CREATE TABLE tab_part_2_p2 PARTITION OF tab_part_2 FOR VALUES FROM (1500) TO
(2000);
INSERT INTO tab_root (id, range_col) VALUES (1, 100);
INSERT INTO tab_root (id, range_col) VALUES (2, 600);
INSERT INTO tab_root (id, range_col) VALUES (3, 1100);
INSERT INTO tab_root (id, range_col) VALUES (4, 1600);
CREATE PUBLICATION pub1 for ALL TABLES EXCEPT table (tab_part_1_p1,
tab_part_2_p2) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
CREATE PUBLICATION pub2 for ALL TABLES EXCEPT table (tab_part_2) WITH
(PUBLISH_VIA_PARTITION_ROOT=false);
postgres=# SELECT c.relname, p.pubname, pr.prexcept FROM pg_publication_rel pr
JOIN pg_class c ON c.oid = pr.prrelid JOIN pg_publication p ON p.oid =
pr.prpubid;
relname | pubname | prexcept
---------------+---------+----------
tab_part_1_p1 | pub1 | t
tab_part_2 | pub2 | t
tab_part_2_p2 | pub1 | t
(3 rows)
postgres=# select * from pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+---------------+----------------+-----------
pub1 | public | tab_root | {id,range_col} |
pub2 | public | tab_part_1_p1 | {id,range_col} |
pub2 | public | tab_part_1_p2 | {id,range_col} |
(3 rows)
Sub:
CREATE TABLE tab_root (id int, range_col int);
CREATE TABLE tab_part_1 (id int, range_col int);
CREATE TABLE tab_part_2 (id int, range_col int);
CREATE TABLE tab_part_1_p1 (id int, range_col int);
CREATE TABLE tab_part_1_p2 (id int, range_col int);
CREATE TABLE tab_part_2_p1 (id int, range_col int);
CREATE TABLE tab_part_2_p2 (id int, range_col int);
CREATE SUBSCRIPTION sub1 connection 'dbname=postgres host=localhost user=shveta
port=5433' publication pub1, pub2;
postgres=# SELECT s.subname, c.relname, r.srsubstate, r.srsublsn FROM
pg_subscription_rel r JOIN pg_class c ON r.srrelid = c.oid JOIN pg_subscription
s ON r.srsubid = s.oid;
subname | relname | srsubstate | srsublsn
---------+----------+------------+------------
sub1 | tab_root | r | 0/017958A8
(1 row)
From Log:
Tablesync worker: Executing query to get the initial sync data:
COPY (
SELECT id, range_col FROM public.tab_part_2_p1
UNION ALL
SELECT id, range_col FROM public.tab_part_1
)
TO STDOUT
Output of table-sync:
postgres=# select * from tab_root;
id | range_col
----+-----------
1 | 100 --tab_part_1_p1
2 | 600 --tab_part_1_p2
3 | 1100 --tab_part_2_p1
(3 rows)
Pub (to test incremental sync):
INSERT INTO tab_root (id, range_col) VALUES (1, 200); --missed tab_part_1_p1
data.
INSERT INTO tab_root (id, range_col) VALUES (2, 700); --replicated
tab_part_1_p2
INSERT INTO tab_root (id, range_col) VALUES (3, 1200); --replicated
tab_part_2_p1
INSERT INTO tab_root (id, range_col) VALUES (4, 1700);
Sub: Result of incremental sync alone:
postgres=# select * from tab_root order by range_col;
id | range_col
----+-----------
2 | 700
3 | 1200
postgres=# select * from tab_part_1_p1;
id | range_col
----+-----------
(0 rows)
postgres=# select * from tab_part_1_p2;
id | range_col
----+-----------
(0 rows)
postgres=# select * from tab_part_2_p1;
id | range_col
----+-----------
(0 rows)
postgres=# select * from tab_part_2_p2;
id | range_col
----+-----------
(0 rows)
So tablesync has replicated the table if it was included in any of the
publication while incremental sync has missed publishing tab_part_1_p1 even
though it was included in pub2.
