Initial Report of PostgreSQL sequences being duplicated during crash

DuplicatingSequences.md

Multiple sessions + Suspending I/O + Autocommit;

## // Terminal 1: Setup
$ pg_ctl start

$ psql -c "DROP SEQUENCE IF EXISTS seqtest" -c "CREATE SEQUENCE seqtest"
$ rm -v seqtest.out

$ for i in {1..10} ; do echo $i; while true; do psql -XAxc "SELECT nextval('seqtest');" &>> seqtest.out; done & done; ##// Note there is no BEGIN;

## // Terminal 2: Tests

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
0

$ sudo dmsetup suspend vg--01--xfstest-lv--xfstest && killall -9 postgres && sudo dmsetup resume vg--01--xfstest-lv--xfstest && pg_ctl start

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
54

$ sudo dmsetup suspend vg--01--xfstest-lv--xfstest && killall -9 postgres && sudo dmsetup resume vg--01--xfstest-lv--xfstest && pg_ctl start

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
120

$ sudo dmsetup suspend vg--01--xfstest-lv--xfstest && killall -9 postgres && sudo dmsetup resume vg--01--xfstest-lv--xfstest && pg_ctl start

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
145

## // Terminal 3: Finishing

$ for i in {1..10}; do kill %$i; done;

Single session + Not Suspending I/O + Explicitly opening transaction;

$ pg_ctl start

$ psql -c "DROP SEQUENCE IF EXISTS seqtest" -c "CREATE SEQUENCE seqtest"
$ rm -v seqtest.out

$ while true; do psql -XAxc "BEGIN; SELECT nextval('seqtest');" &>> seqtest.out; done &

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
0

$ killall -9 postgres
$ pg_ctl start ##// or you can let a loop running with:
               ##// while true; do killall -9 postgres; pg_ctl start; sleep 55; done;

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
12

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
20

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
32

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
59

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
69

(...)

$ grep nextval seqtest.out | cut -d\| -f2 | sort | uniq -d | wc -l
112

sequence.c/nextval_internal - source code at doxygen.postgresql.org

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd6a3f3ad4067f1b8fc28e9de6e99e5936d82161

To fix all this, make nextval() acquire a top level xid, and only wait for
transaction commit if a transaction both acquired a xid and emitted WAL
records.  If only a xid has been assigned we don't uselessly want to
wait just because of writes to temporary/unlogged tables; if only WAL
has been written we don't want to wait just because of HOT prunes.

The xid assignment in nextval() is unlikely to cause overhead in
real-world workloads. For one it only happens SEQ_LOG_VALS/32 values
anyway, for another only usage of nextval() without using the result in
an insert or similar is affected.

Past thread on our mailing lists:
https://www.postgresql.org/message-id/9E91E522-5B17-4ACF-A8B2-047736A50908@gmail.com

Current thread (the one I've opened recently):
https://www.postgresql.org/message-id/CAM9BftxUJ3bH-zywQv-4ywP7i7WTd3BCH39HHESgoCCyHmkQ_g%40mail.gmail.com

Related articles:
https://www.cybertec-postgresql.com/en/sequences-gains-and-pitfalls/