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

Ben Grimm (bgrimm@zaeon.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
backenddoes crash the result is pretty awful.  When a backend crashes, and subsequently all others are killed off by
thepostmaster 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
columndo 10 committed inserts, crash a backend, and further inserts will fail having duplicate keys.  I've tested this
with7.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
allocatedin 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
lastvalue 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