Fix ALTER SEQUENCE locking In 1753b1b027035029c2a2a1649065762fafbf63f3, the pg_sequence system catalog was introduced. This made sequence metadata changes transactional, while the actual sequence values are still behaving nontransactionally. This requires some refinement in how ALTER SEQUENCE, which operates on both, locks the sequence and the catalog.
The main problems were: - Concurrent ALTER SEQUENCE causes "tuple concurrently updated" error, caused by updates to pg_sequence catalog. - Sequence WAL writes and catalog updates are not protected by same lock, which could lead to inconsistent recovery order. - nextval() disregarding uncommitted ALTER SEQUENCE changes. To fix, nextval() and friends now lock the sequence using RowExclusiveLock instead of AccessShareLock. ALTER SEQUENCE locks the sequence using ShareRowExclusiveLock. This means that nextval() and ALTER SEQUENCE block each other, and ALTER SEQUENCE on the same sequence blocks itself. (This was already the case previously for the OWNER TO, RENAME, and SET SCHEMA variants.) Also, rearrange some code so that the entire AlterSequence is protected by the lock on the sequence. As an exception, use reduced locking for ALTER SEQUENCE ... RESTART. Since that is basically a setval(), it does not require the full locking of other ALTER SEQUENCE actions. So check whether we are only running a RESTART and run with less locking if so. Reviewed-by: Michael Paquier <michael.paqu...@gmail.com> Reported-by: Jason Petersen <ja...@citusdata.com> Reported-by: Andres Freund <and...@anarazel.de> Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/f8dc1985fd390774aab4ab0ba71036d6d5e631a9 Modified Files -------------- doc/src/sgml/ref/alter_sequence.sgml | 7 +++ src/backend/commands/sequence.c | 69 +++++++++++++++------- src/test/isolation/expected/sequence-ddl.out | 85 ++++++++++++++++++++++++++++ src/test/isolation/isolation_schedule | 1 + src/test/isolation/specs/sequence-ddl.spec | 39 +++++++++++++ 5 files changed, 180 insertions(+), 21 deletions(-) -- Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers
