Subject: Re: Per-table resync for logical replication subscriptions

From: Cagri Biroglu <[email-masked]>
Date: Fri, 10 Jul 2026 12:38:10 +0200

>
> Thanks a lot for looking at this.
>
> Before the point-by-point: one change after getting feedback on this

from the sketch in my first mail

is that I dropped the WITH (copy_data, truncate) options. For refreshing
> a table that is already subscribed, only one combination is useful, so
> REFRESH TABLE now always truncates the local copy and re-copies (what
> would have been copy_data = true, truncate = true). Without truncating,
> the re-copy hits duplicate-key errors or doubles the rows; skipping the
> copy leaves the drift in place. So neither other combination is
> meaningful, and there is nothing to configure.
>
> > REFRESH TABLE is better to me. It's more consistent with the existing
> > commands such as REFRESH SEQUENCES.
>
> Great, I've kept REFRESH TABLE.
>
> > I think it might be a good idea that the REFRESH TABLE command can
> > take multiple tables rather than one table.
>
> Agreed. v2 makes REFRESH TABLE take a comma-separated list:
>
>     ALTER SUBSCRIPTION name REFRESH TABLE t1, t2, ...
>
> It's all-or-nothing: every named relation is validated (part of the
> subscription, not a sequence, exists) before any of them is reset, so a
> bad table name aborts the whole command without touching the others. The
> listed tables are also truncated together, which lets a set connected by
> foreign keys be re-seeded as a unit.
>
> One note on scope: this doesn't add resource risk over doing one table
> at a time, because the re-copy is still performed by tablesync workers
> that are capped by max_sync_workers_per_subscription; resetting N tables
> just queues them rather than starting N copies at once.
>
> > Why does the REFRESH TABLE command require for the subscription to be
> > disabled while REFRESH PUBLICATIONS doesn't?
>
> Good question, but I'd actually argue the disabled requirement is a
> reasonable design choice here rather than a limitation to remove.
>
> The reason the two commands differ is that REFRESH PUBLICATION only adds
> *new* relations to pg_subscription_rel (in init state), whereas REFRESH
> TABLE resets a relation that is already in ready state, and those hit
> different caches in the apply worker:
>
> - The set of not-ready relations is cached (table_states_not_ready in
>   syncutils.c) and correctly invalidated by the SUBSCRIPTIONRELMAP
>   syscache callback (InvalidateSyncingRelStates). So after the reset the
>   apply worker does notice the table is non-ready and does launch a
>   tablesync worker for it. That part works whether enabled or not.
>
> - However, whether the apply worker applies incoming changes for a
>   relation is decided by should_apply_changes_for_rel(), which reads the
>   per-relation LogicalRepRelMapEntry.state. That state is only refreshed
>   from the catalog while it is not READY:
>
>       /* relation.c, logicalrep_rel_open() */
>       if (entry->state != SUBREL_STATE_READY)
>           entry->state = GetSubscriptionRelState(...);
>
>   For an already-ready table the entry stays READY, and the relcache
>   invalidation only clears localrelvalid (rebuilds the attrmap); it does
>   not reset entry->state. So the running apply worker keeps treating the
>   table as READY and keeps applying live changes to it, at the same time
>   as the freshly launched tablesync worker re-copies it, with no handoff
>   on the sync LSN. In my testing that reliably diverges the table.
>
> For a newly added table (REFRESH PUBLICATION) there is no stale READY
> entry, so the normal init -> datasync -> syncdone -> ready handoff works
> without any disable.
>
> Requiring the subscription to be disabled sidesteps this cleanly. When
> you re-enable, a fresh apply worker starts and reads the reset table's
> state from the catalog as init, so the same init -> datasync -> syncdone
> -> ready handoff kicks in, exactly as for a newly added table. And the
> disable only needs to bracket the quick catalog reset, not the re-copy
> itself: REFRESH TABLE just truncates and flips the state, and after
> ENABLE the tablesync worker does the (possibly long) copy while the
> apply worker resumes the other tables as usual, so the pause is short
> and scoped to the metadata change, not the data copy.
>
> Supporting the enabled case would instead mean reaching into the running
> apply worker to force it to drop and re-read the cached state of the
> reset relation mid-flight. That is doable, but it adds moving parts and
> a new invalidation path to get right, for what is a deliberate,
> occasional maintenance action where briefly disabling the subscription
> does no harm. So my inclination is to keep the disabled requirement here
> rather than trade that simplicity away. Happy to reconsider if you think
> the enabled case matters enough to justify the extra machinery or i am
> missing sth.

Regards,
> Cagri Biroglu