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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Tom Pfau" <T(dot)Pfau(at)emCrit(dot)com>
Cc: "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM>,"Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>, bgrimm(at)zaeon(dot)com,pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Bug #613: Sequence values fall back to previously chec
Date: 2002-03-14 17:27:23
Message-ID: 16412.1016126843@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
"Tom Pfau" <T(dot)Pfau(at)emCrit(dot)com> writes:
> I'm concerned that the discussion here has been of the opinion that
> since no records were written to the database using the value retrieved
> from the sequence that no damage has been done.

Um, you certainly didn't hear me saying that ;-)

There are two different bugs involved here.  One is the no-WAL-flush-
if-transaction-is-only-nextval problem.  AFAIK everyone agrees we must
fix that.  The other issue is this business about "logging ahead"
(to reduce the number of WAL records written) not interacting correctly
with checkpoints.  What we're arguing about is exactly how to fix that
part.

> We are using database
> sequences to get unique identifiers for things outside the database.  If
> a sequence could ever under any circumstances reissue a value, this
> could be damaging to the integrity of our software.

If you do a SELECT nextval() and then use the returned value externally
*without waiting for a commit acknowledgement*, then I think you are
risking trouble; there's no guarantee that the WAL record (if one is
needed) has hit disk yet, and so a crash could roll back the sequence.

This isn't an issue for a SELECT nextval() standing on its own ---
AFAIK the result will not be transmitted to the client until after the
commit happens.  But it would be an issue for a select executed inside
a transaction block (begin/commit).

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-03-14 17:39:55
Subject: Re: insert statements
Previous:From: Vince VielhaberDate: 2002-03-14 16:27:42
Subject: Re: insert statements

pgsql-bugs by date

Next:From: Dave CramerDate: 2002-03-14 17:54:45
Subject: Re: [HACKERS] Bug #613: Sequence values fall back to previously chec
Previous:From: pgsql-bugsDate: 2002-03-14 16:22:10
Subject: Bug #615: Bug in ilke and ~~* Sql expression

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