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

The lack of field reports could be because the relevant
pg_get_sequence_data() path is new and the window to get this issue is
narrow. Though I agree that this can occur rarely, the proposed fix
seems like a cheap insurance against such a rare but nasty occurrence.

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

Isn't it better to bump the mode in pg_get_sequence_data()? I think
adding a comment for the non-obvious choice of lock in
pg_get_sequence_data() sounds reasonable to me as proposed by Vignesh.

--
With Regards,
Amit Kapila.