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

From: Peter Smith <smithpb2250(at)gmail(dot)com>
To: shveta malik <shveta(dot)malik(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, 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: Re: Initial table sync copies the whole table when it is no longer published
Date: 2026-11-17 08:40:21
Message-ID: CAHut+Pv=h4zoyi3JyUg=di5WJ=S997QQhrVQ=7jmoGzD9hUi5w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Shveta.

+1 to address this bug.

On Mon, Nov 16, 2026 at 8:45 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
...
>
> 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?
>

TBH, I am not 100% sure if ERROR is best. Maybe it is; maybe not.

IIUC you are not going to get 1x ERROR -- you are going to get ERRORs
forever, until it is resolved.

OTOH, if you chose to give a WARNING that initial data was skipped
then it's not really "silently missing" is it. I think this scenario
might be a use-case for the other thread [1] proposing "Per-table
resync for logical replication subscriptions". e.g. After seeing such
a warning, (and if that other feature existed) then the specific table
could be refreshed. All without millions of errors.

//////////

Anyway, here are some minor comments for patch v1.

======
src/backend/replication/logical/tablesync.c

1.
fetch_remote_table_info

Now there are multiple places checking the row count, so perhaps a
local var would help. I also think putting an Assert may make
subsequent logic clearer.

e.g.

nrows = tuplestore_tuple_count(pubres->tuplestore);

if (nrows == 0)
ereport(ERROR, ... "table \"%s.%s\" is not published..." ...)

if (nrows > 1)
ereport(ERROR, ... "cannot use different column lists..." ...)

Assert(nrows == 1);
...

======
src/test/subscription/t/024_add_drop_pub.pl

2.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT (SELECT count(*) FROM tab_4), (SELECT count(*) FROM tab_5)");
+is($result, qq(0|0),
+ 'check that no data is copied for tables that are no longer published');

Would it be better to also do some publisher-side INSERTS (after the
DROP/ALTER PUBLICATION, but prior to this) just to demonstrate those
are not getting replicated.

~~~

3.
Missing cleanup code for publication "tap_pub_5" ?

======
[1] https://www.postgresql.org/message-id/flat/OS7PR01MB1831757BF5C212B9E04F7E615F5642%40OS7PR01MB18317.jpnprd01.prod.outlook.com#827e952b0eff4db144d28498004cf3d1

Kind Regards,
Peter Smith.
Fujitsu Australia

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bertrand Drouvot 2026-11-17 08:45:52 Re: Assertion failure in update_local_synced_slot(), slotsync.c:364
Previous Message Bertrand Drouvot 2026-11-17 08:22:46 Re: Assertion failure in update_local_synced_slot(), slotsync.c:364