Re: sequencesync worker race with REFRESH SEQUENCES

On Fri, 10 Jul 2026 at 10:22, Noah Misch <noah(at)leadboat(dot)com> wrote:
>
> The other findings are gray areas, perhaps.

I analyzed finding 4: "Gathering scan holds a lock on every INIT
relation in one unbounded transaction."

The sequence synchronization worker acquires a RowExclusiveLock on
every INIT sequence while scanning pg_subscription_rel and retains
those locks until the transaction commits. On subscriptions with a
large number of INIT sequences, this can exhaust the shared lock
table, causing repeated "out of shared memory" failures. The attached
v1-0001 patch addresses this by releasing the lock immediately after
synchronizing each sequence, preventing lock accumulation. It also
acquires an AccessShareLock instead of a RowExclusiveLock, since
AccessShareLock is sufficient to ensure that the sequence's identity
(namespace and name) remains stable against concurrent DROP, RENAME,
and SET SCHEMA operations while the sequence is being synchronized.

The attached v1-0002 patch contains a TAP test that reproduces the
issue on HEAD. I'm attaching it in case anyone wants to reproduce the
problem locally, although I don't think this test needs to be
committed.

Regards,
Vignesh