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

On Tue, 12 Mar 2002, Tom Lane wrote:

>
> The "no commit record" part of the logic seems okay to me, but we need
> an independent test to decide whether to write/flush XLog. If we have
> reported a nextval() value to the client then it seems to me we'd better
> be certain that XLOG record is flushed to XLog before we report commit
> to the client.

I think the part I don't understand is why WAL is being used to update
sequence values in the first place when sequences exist independantly of
transactions. In previous releases a sequence basically just existed
on disk in a specific location and updates to it updated the on disk
copy directly since there are no concurrency issues. I do realize that
running everything through WAL gives other benefits, so it's not likely
to revert back to the old way. But it would seem that the only way to
fix it is to flush the XLOG record immediately after the XLogInsert is
called, just as if the operation took place within its own transaction.

> This is certainly fixable. However, here's the kicker: essentially what
> this means is that we are not treating *reporting a nextval() value to
> the client* as a commit-worthy event. I do not think this bug explains
> the past reports that claim a nextval() value *inserted into the
> database* has been rolled back. Seems to me that a subsequent tuple
> insertion would create a normal XLog record which we'd flush before
> commit, and thereby also flush the sequence-update XLog record.
>
> Can anyone see a way that this mechanism explains the prior reports?
>

Actually, that doesn't appear to be the case either because in some of
my tests I used a serial column type and I was just inserting data into
a table. It would appear that if the sequence is in the same tuple as
the data you modified then it won't get logged. What I did was create
a table with a serial column and a varchar(255). Inserted 100 rows
filled with data, committed. Ran a checkpoint. Checked my sequence
values, inserted 10 more rows of data, committed, checked the value of
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.

-- Ben