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@postgresql.org wrote:
> 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 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@postgresql.org)
>

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