FIX: BUG #19687: ALTER SEQUENCE missing lock
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253793psql -h localhost -U postgresBuilt from patchset v1 (message #1), October 22, 2026 at 12:00 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253793_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253793_1 && git checkout t253793_1Patchset v1 (message #1) is on t253793_1
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.
Hi,
On Tue, 15 Sept 2026 at 12:19, Alexandre Felipe <
o.alexandre.felipe@gmail.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]/messages/by-id/CAJTYsWWH0N-jJUviz3eviLa_ehGVsmumOmpTGufbRAsuDD2Uiw@mail.gmail.com (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]/messages/by-id/CAJTYsWWH0N-jJUviz3eviLa_ehGVsmumOmpTGufbRAsuDD2Uiw@mail.gmail.com.
The doc-comment correction in your patch looks right either way.
[1]: /messages/by-id/19687-dd094472b0c48afb@postgresql.org
/messages/by-id/19687-dd094472b0c48afb@postgresql.org
[2]: /messages/by-id/CAJTYsWWH0N-jJUviz3eviLa_ehGVsmumOmpTGufbRAsuDD2Uiw@mail.gmail.com
/messages/by-id/CAJTYsWWH0N-jJUviz3eviLa_ehGVsmumOmpTGufbRAsuDD2Uiw@mail.gmail.com
Regards,
Ayush
On Tue, Sep 15, 2026 at 9:00 AM Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Hi,
On Tue, 15 Sept 2026 at 12:19, Alexandre Felipe <
o.alexandre.felipe@gmail.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'sdoc-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.
Transaction conflict, my bad :(
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():
Callers elsewhere, even in sequence.c, already lock before calling it,
so acquiring a lock in RelationSetNewRelfilenumber will definitely deadlock.
Regards,
Alexandre
Show quoted text
Hi,
I applied v1 on top of 3c5d9d914f and built it with cassert. regress
and isolation pass, and Alexander's script no longer fails for me
(200 iterations, with and without the forced parallel plan; on HEAD
it fails within 50).
However, the lock upgrade in v1 is exactly what Ayush described
upthread, not a lock inside RelationSetNewRelfilenumber():
+ LockRelationOid(relid, AccessExclusiveLock);
At this point we already hold ShareRowExclusiveLock, so a transaction
that has read the sequence and then calls nextval() deadlocks with us:
s2: BEGIN; SELECT last_value FROM seq1;
s1: BEGIN; ALTER SEQUENCE seq1 AS int; -- waits
s2: SELECT nextval('seq1');
ERROR: deadlock detected
The same deadlock occurs if s2 runs ALTER SEQUENCE instead of
nextval(). On HEAD, s2 waits for s1 in both cases.
Taking AccessExclusiveLock at the initial RangeVarGetRelidExtended()
call avoids these deadlocks: s1 waits for s2, and s2 can finish.
This also fixes the reported failures. ResetSequence() and
SequenceChangePersistence() already use this lock mode.
This change was also discussed in the bug thread:
/messages/by-id/19687-dd094472b0c48afb@postgresql.org
The downside is that ALTER SEQUENCE ... OWNED BY alone would also
block readers, even though it doesn't rewrite the sequence. We could
choose the lock mode from the option list up front to preserve the
current behavior for that case, but I'm not sure the extra complexity
is worth it.
AccessExclusiveLock also covers hot standby, since it is WAL-logged.
On HEAD, a standby query that reads the sequence across the replay of
the ALTER's commit fails with
ERROR: could not open file "base/5/16384": No such file or directory
while with the stronger lock it gets the usual recovery conflict.
Also I think the patch needs a test. An isolation permutation in
sequence-ddl.spec with a cursor open on the sequence works
deterministically: on HEAD the ALTER doesn't wait and the following
FETCH silently returns zero rows, with the fix the ALTER waits.
A SELECT-then-nextval() permutation would catch the deadlock above.
The new comment isn't quite accurate: RelationSetNewRelfilenumber()
doesn't destroy the old file, it is dropped at commit while other
backends may still be reading it.
Best regards,
Andrew Krylosov
On Tue, Sep 15, 2026 at 9:00 AM Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
I posted about the same bug on pgsql-bugs yesterday [2] (quoted below),
so maybe it is better to keep this in one thread.
Hi Ayush, do you want to take care of this, since you already did some good
work on it?
Thank you for your review Andrew
On Sat, Sep 26, 2026 at 11:46 PM Andrew Krylosov <krylosov.andrew@gmail.com>
wrote:
Show quoted text
Hi,
I applied v1 on top of 3c5d9d914f and built it with cassert. regress
and isolation pass, and Alexander's script no longer fails for me
(200 iterations, with and without the forced parallel plan; on HEAD
it fails within 50).However, the lock upgrade in v1 is exactly what Ayush described
upthread, not a lock inside RelationSetNewRelfilenumber():+ LockRelationOid(relid, AccessExclusiveLock);
At this point we already hold ShareRowExclusiveLock, so a transaction
that has read the sequence and then calls nextval() deadlocks with us:s2: BEGIN; SELECT last_value FROM seq1;
s1: BEGIN; ALTER SEQUENCE seq1 AS int; -- waits
s2: SELECT nextval('seq1');
ERROR: deadlock detectedThe same deadlock occurs if s2 runs ALTER SEQUENCE instead of
nextval(). On HEAD, s2 waits for s1 in both cases.Taking AccessExclusiveLock at the initial RangeVarGetRelidExtended()
call avoids these deadlocks: s1 waits for s2, and s2 can finish.
This also fixes the reported failures. ResetSequence() and
SequenceChangePersistence() already use this lock mode.This change was also discussed in the bug thread:
/messages/by-id/19687-dd094472b0c48afb@postgresql.orgThe downside is that ALTER SEQUENCE ... OWNED BY alone would also
block readers, even though it doesn't rewrite the sequence. We could
choose the lock mode from the option list up front to preserve the
current behavior for that case, but I'm not sure the extra complexity
is worth it.AccessExclusiveLock also covers hot standby, since it is WAL-logged.
On HEAD, a standby query that reads the sequence across the replay of
the ALTER's commit fails withERROR: could not open file "base/5/16384": No such file or directory
while with the stronger lock it gets the usual recovery conflict.
Also I think the patch needs a test. An isolation permutation in
sequence-ddl.spec with a cursor open on the sequence works
deterministically: on HEAD the ALTER doesn't wait and the following
FETCH silently returns zero rows, with the fix the ALTER waits.
A SELECT-then-nextval() permutation would catch the deadlock above.The new comment isn't quite accurate: RelationSetNewRelfilenumber()
doesn't destroy the old file, it is dropped at commit while other
backends may still be reading it.Best regards,
Andrew Krylosov
Hi,
On Sun, 27 Sept 2026 at 12:06, Alexandre Felipe <
o.alexandre.felipe@gmail.com> wrote:
On Tue, Sep 15, 2026 at 9:00 AM Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
I posted about the same bug on pgsql-bugs yesterday [2] (quoted below),
so maybe it is better to keep this in one thread.Hi Ayush, do you want to take care of this, since you already did some
good work on it?
Thank you for your review Andrew
On Sat, Sep 26, 2026 at 11:46 PM Andrew Krylosov <
krylosov.andrew@gmail.com> wrote:
Hi,
I applied v1 on top of 3c5d9d914f and built it with cassert. regress
and isolation pass, and Alexander's script no longer fails for me
(200 iterations, with and without the forced parallel plan; on HEAD
it fails within 50).However, the lock upgrade in v1 is exactly what Ayush described
upthread, not a lock inside RelationSetNewRelfilenumber():+ LockRelationOid(relid, AccessExclusiveLock);
At this point we already hold ShareRowExclusiveLock, so a transaction
that has read the sequence and then calls nextval() deadlocks with us:s2: BEGIN; SELECT last_value FROM seq1;
s1: BEGIN; ALTER SEQUENCE seq1 AS int; -- waits
s2: SELECT nextval('seq1');
ERROR: deadlock detectedThe same deadlock occurs if s2 runs ALTER SEQUENCE instead of
nextval(). On HEAD, s2 waits for s1 in both cases.Taking AccessExclusiveLock at the initial RangeVarGetRelidExtended()
call avoids these deadlocks: s1 waits for s2, and s2 can finish.
This also fixes the reported failures. ResetSequence() and
SequenceChangePersistence() already use this lock mode.This change was also discussed in the bug thread:
/messages/by-id/19687-dd094472b0c48afb@postgresql.orgThe downside is that ALTER SEQUENCE ... OWNED BY alone would also
block readers, even though it doesn't rewrite the sequence. We could
choose the lock mode from the option list up front to preserve the
current behavior for that case, but I'm not sure the extra complexity
is worth it.AccessExclusiveLock also covers hot standby, since it is WAL-logged.
On HEAD, a standby query that reads the sequence across the replay of
the ALTER's commit fails withERROR: could not open file "base/5/16384": No such file or directory
while with the stronger lock it gets the usual recovery conflict.
Also I think the patch needs a test. An isolation permutation in
sequence-ddl.spec with a cursor open on the sequence works
deterministically: on HEAD the ALTER doesn't wait and the following
FETCH silently returns zero rows, with the fix the ALTER waits.
A SELECT-then-nextval() permutation would catch the deadlock above.The new comment isn't quite accurate: RelationSetNewRelfilenumber()
doesn't destroy the old file, it is dropped at commit while other
backends may still be reading it.
I'll prepare a patch once we get an initial review from SMEs.
Let's please use that bug thread [1]/messages/by-id/CAJTYsWVeKcAsKCS7YiQJT879SkooJrq1bEXLfvEH+KasjNSi1A@mail.gmail.com for discussion and not continue here.
Regards,
Ayush
[1]: /messages/by-id/CAJTYsWVeKcAsKCS7YiQJT879SkooJrq1bEXLfvEH+KasjNSi1A@mail.gmail.com
/messages/by-id/CAJTYsWVeKcAsKCS7YiQJT879SkooJrq1bEXLfvEH+KasjNSi1A@mail.gmail.com