Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: bgrimm(at)zaeon(dot)com, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed
Date: 2002-03-11 23:02:25
Message-ID: 200203112302.g2BN2PU26371@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-03-12 05:17:20
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed
Previous:From: pgsql-bugsDate: 2002-03-11 21:59:08
Subject: Bug #613: Sequence values fall back to previously checkpointed value after crash

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group