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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-03-12 23:51:52 | Re: Bug #613: Sequence values fall back to previously checkpointed |
Previous Message | Tom Lane | 2002-03-12 23:36:15 | Re: Bug #613: Sequence values fall back to previously checkpointed |