Re: Bug #613: Sequence values fall back to previously checkpointed

Yikes! I have reproduced this bug. My server logs are:

LOG: database system was shut down at 2002-03-08 17:30:03 CET
LOG: checkpoint record is at 0/46D018
LOG: redo record is at 0/46D018; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 146; next oid: 16561
LOG: database system is ready
ERROR: DefineIndex: operator class "int" not supported by access method "btree"
ERROR: Relation 'test' already exists
LOG: server process (pid 21627) was terminated by signal 9
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing shared memory and semaphores
LOG: database system was interrupted at 2002-03-11 23:22:50 CET
LOG: checkpoint record is at 0/490AB8
LOG: redo record is at 0/490AB8; undo record is at 0/0; shutdown FALSE
LOG: next transaction id: 172; next oid: 24753
LOG: database system was not properly shut down; automatic recovery in progress
LOG: ReadRecord: record with zero length at 0/490AF8
LOG: redo is not required
LOG: database system is ready

I find on reconnection after 'kill -9' that the sequence is indeed set
at 1. I did a checkpoint after the sequence creation.

---------------------------------------------------------------------------

pgsql-bugs(at)postgresql(dot)org wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister
> command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026