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

On Wed, 29 Jul 2026 at 21:10, Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> On Wed, Jul 29, 2026 at 05:48:46PM +0530, Amit Kapila wrote:
> > On Tue, Jul 28, 2026 at 8:45 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> >> 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.
>
> Well, my point is that I don't think this failure mode is actually new at
> all. pg_get_sequence_data() just changes where it happens.
>
> > Though I agree that this can occur rarely, the proposed fix
> > seems like a cheap insurance against such a rare but nasty occurrence.
>
> I'm okay with bumping up the lock mode in pg_get_sequence_data() if folks
> agree it is worth the risk. I don't have any concrete concerns at the
> moment, but I'll plan on taking a closer look soon.
>
> > 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.
>
> I certainly think Vignesh's proposal is better from a deadlock standpoint,
> but this leaves the race between SELECT and ALTER intact.

The following scenarios behave as expected by blocking when another
backend holds a conflicting lock: a) Concurrent pg_get_sequence_data()
and ALTER SEQUENCE b) Concurrent pg_sequence_last_value() and ALTER
SEQUENCE c) Concurrent setval() and ALTER SEQUENCE d) Concurrent
nextval() and ALTER SEQUENCE

Regarding the SELECT case you mentioned, i.e., running:
SELECT * FROM s1;

concurrently with:
ALTER SEQUENCE s1 ...;

I tested this by pausing execution of select sequence inside
table_beginscan() and then executing ALTER SEQUENCE concurrently. As
expected, ALTER SEQUENCE changes the sequence's relfilenode while the
scan is in progress.

In this case, the SELECT does not report an error. Instead, it returns
an empty result:
postgres=# SELECT * FROM s1;
last_value | log_cnt | is_called
------------+---------+-----------
(0 rows)

The very next SELECT works as expected and returns the updated sequence tuple:
postgres=# SELECT * FROM s1;
last_value | log_cnt | is_called
------------+---------+-----------
10 | 0 | t
(1 row)

Yes, there appears to be an issue with concurrently selecting from a
sequence while running ALTER SEQUENCE.

Regards,
Vignesh