Re: FIX: BUG #19687: ALTER SEQUENCE missing lock

Hi,

On Tue, 15 Sept 2026 at 12:19, Alexandre Felipe <
o(dot)alexandre(dot)felipe(at)gmail(dot)com> wrote:
>
> Trying to do something different today.
>
> Hunting bugs.
>
> Addressing an issue in `ALTER SEQUENCE` commands [1], by adding
> a missing lock when calling RelationSetNewRelfilenumber and being
> more accurate about the type of lock required in that function's
doc-comment.
>
> The change is sound, even if it is not the complete fix yet.

I posted about the same bug on pgsql-bugs yesterday [2] (quoted below),
so maybe it is better to keep this in one thread.

I tried locking at the RelationSetNewRelfilenumber() call first, but it
deadlocks. ALTER still holds ShareRowExclusiveLock while it waits for the
upgrade, and that conflicts with the RowExclusiveLock a scanning
transaction needs for nextval():

s2: BEGIN; SELECT * FROM seq1; -- holds AccessShareLock
s1: ALTER SEQUENCE seq1 AS int; -- holds ShareRowExclusiveLock,
-- waits for AccessExclusiveLock
s2: SELECT nextval('seq1'); -- waits for s1

An isolation permutation for that gives me:

step s1altertype: ALTER SEQUENCE seq1 AS int; <waiting ...>
step s2nvone: SELECT nextval('seq1');
ERROR: deadlock detected

Taking AccessExclusiveLock at the initial lookup instead avoids this, since
ALTER then holds nothing on the sequence while it waits, and nextval() can
still proceed. That is the one-line change in [2].

The doc-comment correction in your patch looks right either way.

[1]
https://www.postgresql.org/message-id/19687-dd094472b0c48afb%40postgresql.org
[2]
https://www.postgresql.org/message-id/CAJTYsWWH0N-jJUviz3eviLa_ehGVsmumOmpTGufbRAsuDD2Uiw%40mail.gmail.com

Regards,
Ayush