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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: bgrimm(at)zaeon(dot)com, pgsql-bugs(at)postgresql(dot)org,Vadim Mikheev <vmikheev(at)sectorbase(dot)com>
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed
Date: 2002-03-12 23:46:51
Message-ID: 200203122346.g2CNkpD20962@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > bgrimm(at)zaeon(dot)com wrote:
> >> the sequence again.  Kill -9 the postmaster.  Tried to insert into the 
> >> table, but to no avail... duplicate key.  currval of the sequence and 
> >> it matched the value right after the checkpoint.  I've been able to 
> >> duplicate that scenario several times.
> 
> > I just tested it here by doing a similar test of several nextval()
> > calls, but then doing an INSERT and kill, and on restart, the sequence
> > counter did have the proper value.
> 
> There have been prior reports of similar problems --- all quite
> unrepeatable in my testing, and despite considerable study of the source
> code I can't see how it could happen.  A reproducible test case would be
> a tremendous help.


I can confirm repeatable case!

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

test=> create table test (x serial, y varchar(255));
NOTICE:  CREATE TABLE will create implicit sequence 'test_x_seq' for SERIAL column 'test.x'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'test_x_key' for table 'test'
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32951 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32951 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32951 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32951 1
...

test=> select nextval('test_x_seq');
 nextval 
---------
      41
(1 row)

test=> checkpoint;
CHECKPOINT
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32991 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32992 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32992 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32992 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 32992 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');


[ kill -9 backend ]


$ sql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=> select nextval('test_x_seq');
 nextval 
---------
      42
(1 row)

-- 
  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: Bruce MomjianDate: 2002-03-12 23:51:52
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed
Previous:From: Tom LaneDate: 2002-03-12 23:36:15
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed

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