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

Re: [HACKERS] Bug #613: Sequence values fall back to previously

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: "'Ben Grimm'" <bgrimm(at)zaeon(dot)com>,Vadim Mikheev <vmikheev(at)sectorbase(dot)com>,Tom Pfau <T(dot)Pfau(at)emCrit(dot)com>, pgsql-bugs(at)postgresql(dot)org,pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Bug #613: Sequence values fall back to previously
Date: 2002-03-15 15:43:25
Message-ID: 200203151543.g2FFhPR05508@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Tom Lane wrote:
> Attached is a patch against current CVS that fixes both of the known
> problems with sequences: failure to flush XLOG after a transaction
> that only does "SELECT nextval()", and failure to force a new WAL
> record to be written on the first nextval after a checkpoint.
> (The latter uses Vadim's idea of looking at the sequence page LSN.)
> I haven't tested it really extensively, but it seems to cure the
> reported problems.

I can confirm that the patch fixes the problem shown in my simple test:

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'
CREATE
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 16561 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 16562 1
test=> insert into test (y) values ('lkjasdflkja sdfl;kj asdfl;kjasdf');
INSERT 16563 1
...

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

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

[ kill -9 to 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 
---------
      56
(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

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2002-03-15 15:44:16
Subject: Re: [HACKERS] Bug #613: Sequence values fall back to previously
Previous:From: Tom LaneDate: 2002-03-15 15:11:35
Subject: Re: User Level Lock question

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2002-03-15 15:44:16
Subject: Re: [HACKERS] Bug #613: Sequence values fall back to previously
Previous:From: Tom LaneDate: 2002-03-15 14:39:04
Subject: Re: Bug #613: Sequence values fall back to previously chec

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