Re: Bug #613: Sequence values fall back to previously checkpointed - Mailing list pgsql-bugs

bgrimm@zaeon.com wrote:
> 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.

Initially, I agreed with you, but after reading Tom's email, I
understand now.  The point is that if no data mofifying transactions are
committed to the database, do we care if a crash rolls back the
transaction counter?  I don't think we do because the new sequence
values couldn't have been used for anything in the database, so rollback
is OK.  If there is a data modification, the commit does flush the
sequence change to disk, meaning I think we are OK.

So, actually, this now seems like a very low level bug in the sense that
any data modification done would have preserved the sequence change. The
only reason we see the bug is because we are calling 'nextval()' with no
intention of inserting it into the database.

While clearly we should fsync during a transaction that just changes a
sequence counter, it is not clear there is a huge problem with not
preserving it _until_ a data-modifying transaction actually commits.


> > 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.

Now, this seems to be a major report because you are saying you have
done data modifications (INSERT) after nextval() and the counter is not
being preserved. What PostgreSQL version are you running?

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.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026