Re: Race between pg_dump and ALTER SEQUENCE can cause read failure

On Tue, Jul 28, 2026 at 8:45 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> On Tue, Jul 28, 2026 at 07:12:58PM +0530, vignesh C wrote:
> > While testing concurrent operations involving pg_dump and ALTER
> > SEQUENCE, I found a race that can cause pg_dump to fail while reading
> > sequence data.
>
> Thanks for reporting.
>
> > I think we should instead acquire a RowExclusiveLock rather than an
> > AccessShareLock in pg_get_sequence_data(), so that a concurrent ALTER
> > SEQUENCE waits until pg_get_sequence_data() has finished reading the
> > sequence. Attached
> > v1-0001-Prevent-concurrent-ALTER-SEQUENCE-from-racing-wit.patch which
> > has the changes for the same.
> >
> > This appears to be an old issue from commit
> > 7a485bd641b7bbf072146b97f70f9eb2c89f606a, where pg_get_sequence_data()
> > uses AccessShareLock. I was able to reproduce it on PostgreSQL 18 as
> > well.
>
> I'm of two minds about this. I think this has been a problem for much
> longer, just with different symptoms. Even today, a SELECT run
> concurrently with ALTER on a sequence might return 0 rows, which caused
> pg_dump to error before it used pg_get_sequence_data(). On the other hand,
> scary errors are no good, and the deadlock risk of bumping
> pg_get_sequence_data() to RowExclusiveLock might be minimal.
>
> I'm tempted to suggest that living with the bug is the better option here,
> especially considering the apparent lack of field reports. IIUC to
> actually fix the root cause we'd need to bump the lock mode of ALTER
> SEQUENCE, and I suspect that's not really viable. I'm curious what others
> think.
>

I think logical sequence-sync worker can also hit this problem as
worker fetches values from publisher using pg_get_sequence_data.
Vignesh, right? It may not be that harmful though, as the worker will
start again and get it right the next time. But whatever we decide we
need to keep the seq-synchronization case in mind as well.

thanks
Shveta