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

"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