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-18 04:42:45
Message-ID: CAJpy0uA_Y-Vbk5EofE8E73u=0wAYMXajqdNB8kXcMbFwyznYDw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, Nov 18, 2026 at 4:46 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> 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?

Thanks Peter, I gave some thought to your idea, and I agree it would
avoid the repeated errors. But I think it has a similar problem to
marking the table ready: if the table is published again (e.g. the pub
is recreated, or the table is added back), with the ERROR the next
retry simply syncs it, whereas if the worker had already removed it
from pg_subscription_rel, it would stay out of the subscription until
someone runs REFRESH PUBLICATION. We could emit a WARNING with a hint
when removing it, but that could easily go unnoticed, and the table
could then remain unreplicated indefinitely, a bigger problem than the
repeated ERRORs.

Also, currently tables are only added to or removed from
pg_subscription_rel by DDL (CREATE/ALTER/DROP sub, or dropping the
table on the subscriber); the workers only update their sync state.
Having a worker remove tables by itself would be a new behaviour, and
only a partial refresh:
e.g. after a publish_via_partition_root change, it could remove the
partitions but not add the root (the scenario mentioned in [1]), which
only a real REFRESH does. Plus the slot/origin cleanup you mentioned,
which REFRESH PUBLICATION already handles. So, as you said, I think
the plain ERROR is the simpler and safer choice, especially if this is
back-patched.

[1]: https://www.postgresql.org/message-id/CAJpy0uDw0crvrUFU%3DFsqcuvH%3DdJbqPASkUhnDB%3DbdHV1SAc%2B0A%40mail.gmail.com
> >
> > 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.

Sure, will do that.

thanks
Shveta

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-11-18 04:44:02 Re: 041_checkpoint_at_promote.pl might fail due to race condition on child kill
Previous Message Fujii Masao 2026-11-18 04:34:24 Re: pg_basebackup: check timeline history file write failures in ReceiveXlogStream()