Initial table sync copies the whole table when it is no longer published

From: shveta malik <shveta(dot)malik(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: shveta malik <shvetamalik(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Subject: Initial table sync copies the whole table when it is no longer published
Date: 2026-11-16 09:44:35
Message-ID: CAJpy0uAWSLH-SMeH0JoqvGACv8truSfXiwFp4PCkTJE93CGigA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi All,
(CCing the people involved in commit 7c99dc5, which handled a related case.)

While working on BUG #19864, I found an issue in tablesync that I
think is worth fixing. It is somewhat similar to the case handled by
commit 7c99dc5 (a missing publication in the apply worker), but
happens during the initial sync.

Steps to reproduce:

Pub:
create table tab1(a int, b int, c int);
insert into tab1 select i, i, i from generate_series(1, 5) i;
create publication pub1 for table tab1(c) where (c > 3);

Sub:
create table tab1(a int, b int, c int);
create subscription sub1 connection '...' publication pub1
WITH(enabled='false');

Pub:
drop publication pub1;

Sub:
alter subscription sub1 ENABLE;

-- wait a few seconds for the sync
select * from tab1 order by c;
select srrelid::regclass, srsubstate FROM pg_subscription_rel;

Result:
a | b | c
---+---+---
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 | 4
5 | 5 | 5

srrelid | srsubstate
---------+------------
tab1 | r

Here tablesync ends up copying all the columns and all the rows of a
table, ignoring the publication's column list and row filter. It
happens when the publication is dropped (or the table is removed from
the publication) before the table's initial sync starts. The table is
marked ready and no error is reported on either side. Without the DROP
PUBLICATION step, we get only (NULL, NULL, 4) and (NULL, NULL, 5), as
expected.

The cause is in fetch_remote_table_info(). It fetches the column list
and the row filter from the publisher, one row per publication. When
the publication is gone, both queries return no rows, and that is
treated the same as "no column list" and "no row filter", so the COPY
takes the whole table. Note that a table published without a column
list or row filter still returns one row from these queries, with NULL
values. Only a table that no publication publishes returns no rows.

ALTER SUBSCRIPTION ... REFRESH PUBLICATION does not really help here.
It can't be run on a disabled subscription, and once we enable the
subscription, the sync may copy the wrong data before the refresh
runs.

It is also not just about the ENABLE command; any table still waiting
for its initial sync can hit this when its turn comes, e.g. when many
tables are queued for a limited number of sync workers, even though
the sub was enabled long before the publication was dropped. REFRESH
PUBLICATION can only prevent this if it runs after the drop and before
the table’s sync starts, which is a race the user can’t control.

And if the refresh is run after the sync, it removes the table from
the subscription, but the wrongly copied data stays on the subscriber.

Attached patch fixes this by raising an error when the column-list and
row-filter queries on the publisher return no rows, since that means
none of the subscription's publications publishes the table anymore,
instead of treating it as "no column list" and "no row filter" and
copying everything. Unlike commit 7c99dc5, which skips a missing
publication with a WARNING in the apply worker, I think raising an
error is the right choice here. A tablesync error only affects that
one table’s sync, and the other tables and the apply worker continue.
Skipping, on the other hand, would mean marking the table ready
without copying it, so if the table is published again, its initial
data would be silently missing on the subscriber. Thoughts?

Thanks
Shveta

Attachment Content-Type Size
v1-0001-Do-not-copy-the-whole-table-in-tablesync-when-no-.patch application/octet-stream 6.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dean Rasheed 2026-11-16 10:00:40 Re: [PATCH] Normalize numeric/bigint scientific notation after rounding
Previous Message Bertrand Drouvot 2026-11-16 09:36:12 Re: Document prepared transaction lock waits with two_phase subscriptions