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

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

From: bgrimm(at)zaeon(dot)com
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,pgsql-bugs(at)postgresql(dot)org, Vadim Mikheev <vmikheev(at)sectorbase(dot)com>
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed
Date: 2002-03-12 13:38:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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 

In response to


pgsql-bugs by date

Next:From: Per WinkvistDate: 2002-03-12 19:32:47
Subject: Case sensitive table names ?
Previous:From: JustinDate: 2002-03-12 06:28:59
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed

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