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

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/