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>, 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 23:15:50
Message-ID: CAHut+PvHpiqE1YXkvENhNcm8k08XeeTds=H4eKdmMnsemmi3LA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Nov 17, 2026 at 9:03 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> 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.

Your justifications for using ERROR instead of WARNING seemed
reasonable to me, so I am happy if that's how it ends up being.

So, the patch v2-0001 LGTM.

I did have one more idea:

IMO the purpose of REFRESH PUBLICATION is for "discovery" of what is
currently published so the `pg_subscription_rel` can be updated
appropriately with the latest list. But in your scenario, you
*already* discovered that this particular table is no longer
published. From that POV why not just remove this table from the
`pg_subscription_rel` right here-and-now.

That avoids the endless ERRORs and the silent-data-loss problem of
marking it READY, plus it saves the user from manually having to do a
REFRESH PUBLICATION to resolve errors just to re-discover this table
is not published (we already know that).

Having said that, it might need additional slot/origin cleanup from
within the worker, and some other complications, so just the plain
looping ERROR may well be the simpler and safer choice. Thoughts?

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

I agree your fix should not depend on the other thread. But I think it
would be good if you post to that other thread just to mention this
current issue seems like a valid use-case where that new per-table
refresh feature might be useful.

======
Kind Regards,
Peter Smith.
Fujitsu Australia

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message surya poondla 2026-11-17 23:19:26 Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
Previous Message Masahiko Sawada 2026-11-17 22:52:46 Re: [PATCH] Fall back to serializing when parallel apply runs out of DSM segments