Re: Per-table resync for logical replication subscriptions

I wanted to follow up on this patch, as there has not been any activity on
the thread for some time.

The proposal is still relevant from my side, and I am happy to rebase the
patch, run additional tests, or address any design concerns or review
feedback.

On Fri, 10 Jul 2026 at 12:38, Cagri Biroglu <cagri(dot)biroglu(at)adyen(dot)com> wrote:

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