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

From: shveta malik <shveta(dot)malik(at)gmail(dot)com>
To: Peter Smith <smithpb2250(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, vignesh C <vignesh21(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, shveta malik <shvetamalik(at)gmail(dot)com>
Subject: Re: Initial table sync copies the whole table when it is no longer published
Date: 2026-11-17 10:03:25
Message-ID: CAJpy0uDRVoV_ss+dBSu_8si7XcPu+a7CERWKKW2H+vcgOkOTEQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Nov 17, 2026 at 2:10 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> 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.

Thanks for the review Peter.

Yes, the ERROR repeats until it is resolved, but that is the same
behaviour as other tablesync errors (e.g. "table \"%s.%s\" not found
on publisher"). The errors are also counted in
pg_stat_subscription_stats, so users can monitor them.

With the ERROR, the table stays in the 'd' (data sync) state, which is
the correct state IMO. With a WARNING and skip, the table would
wrongly be marked 'ready' instead, so its initial data would never be
copied, even after a REFRESH PUBLICATION (provided the table is added
back or the publication is created).

We could instead give a WARNING but keep the table in 'd' rather than
marking it ready, but then it would keep retrying the same way,
restarting the tablesync worker every time, just with WARNINGs instead
of ERRORs, and without the sync_error_count and disable_on_error
handling. Keeping the table in 'd' with a WARNING would also require
extra code to exit the tablesync-worker cleanly from the middle of the
copy, which I don't think is worth it for this rare scenario. But
let's see what others think about this.

The per-table resync feature you mentioned would make recovery easier
if it is committed, but I don’t think this fix should depend on it. If
that feature gets in, we can revisit whether a WARNING would be better
here. Thoughts?

> //////////
>
> Anyway, here are some minor comments for patch v1.

Agree with all, addressed in v2.

> ======
> 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

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Sharma 2026-11-17 10:06:05 Proposal: cross-database unlogged relation size reporting
Previous Message Andrei Lepikhov 2026-11-17 09:58:50 Use local buffer for packed numerics