Bug #613: Sequence values fall back to previously checkpointed value after crash

Ben Grimm (bgrimm(at)zaeon(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Sequence values fall back to previously checkpointed value after crash

Long Description
It's hard to decide if it's devestating or not, since the bug is only apparent when a backend crashes. But when a backend does crash the result is pretty awful. When a backend crashes, and subsequently all others are killed off by the postmaster to avoid shared memory corruption, sequences fall back to whatever value they had the last time the db checkpointed. I say checkpoint because this happens independantly of commits, so you could have a table with a serial column do 10 committed inserts, crash a backend, and further inserts will fail having duplicate keys. I've tested this with 7.2rc2 and 7.2 STABLE using a stock postgresql.conf (all defaults).

It seems impossible to me that this is happening. I've looked at the code and seen the comment about how sequences are allocated in advance. So I figured I'd report it...

Steps to reproduce the bug:
- Create a sequence, assign it a value
- Checkpoint (optional)
- Connect to one or more backend
- select nextval (on any/all of the connections opened above) from that sequence several times, noting the first and last value returned
- kill -9 (or -11) any of the backend processes, the database will automatically kill off all the other backends.
- reconnect and select nextval from the sequence and it will be return the first value (from above).

Sample Code

No file was uploaded with this report