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

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

From: 'Ben Grimm' <bgrimm(at)zaeon(dot)com>
To: "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tom Pfau <T(dot)Pfau(at)emCrit(dot)com>,Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, 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-15 01:55:23
Message-ID: 20020314195523.A15774@zaeon.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
On Thu, 14 Mar 2002, Mikheev, Vadim wrote:

> And it's not. But behaviour of application *must* be
> conditional on was transaction committed or not.
> 
> What's the problem for application that need nextval() for
> external (out-of-database) purposes to use sequence values
> only after transaction commit? What's *wrong* for such application
> to behave the same way as when dealing with other database objects
> which are under transaction control (eg only after commit you can
> report to user that $100 was successfully added to his/her account)?

But sequences should not be under transaction control.  Can you 
safely rollback a sequence?  No!  The only way to ensure that would
be to lock the sequence for the duration of the transaction.  If 
you want an ACID compliant sequential value, you implement it using
a transaction safe method (e.g. a table with rows you can lock for
the duration of a transaction).  If you want a number that is 
guaranteed to always move in one direction, return the next value
without requiring locks, and may have gaps in the numbers returned,
you choose a sequence.

Placing a restriction on an application that says it must treat 
the values returned from a sequence as if they might not be committed
is absurd.  What about applications that don't use explicit 
transactions?  As soon as a result comes back it should be considered
'live', on disk, never think about it again. 
 
> I agree that if nextval-s were only "write" actions in transaction
> and they made some XLogInsert-s then WAL must be flushed at commit
> time. But that's it. Was this fixed? Very easy.

But aren't the nextval's always going to be the only write actions
in their transactions since the nextval isn't really a part of the 
transaction that called it?  If it were, then it could be rolled 
back along with that transaction.  This is why you can, right now, 
insert data into a table with a serial column, committing after
each row, crash the database and STILL have the sequence fall back
to its initial state.  The XLogInserts that occur from the table
inserts must not happen in the same xact as the nextval's 
XLogInserts.  I can demonstrate the behavior quite easilly, and 
Bruce posted results that confirmed it.

-- Ben

In response to

Responses

pgsql-hackers by date

Next:From: Michael Alan DormanDate: 2002-03-15 02:33:27
Subject: Re: insert statements
Previous:From: Christopher Kings-LynneDate: 2002-03-15 01:45:29
Subject: Re: privileges regression problem on freebsd/alpha

pgsql-bugs by date

Next:From: Vadim MikheevDate: 2002-03-15 09:05:33
Subject: Re: Bug #613: Sequence values fall back to previously chec
Previous:From: Mikheev, VadimDate: 2002-03-15 00:17:39
Subject: Re: Bug #613: Sequence values fall back to previously chec

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