On Thu, Mar 01, 2007 at 11:26:23 +0100,
  "Florian G. Pflug" <fgp(at)phlo(dot)org> wrote:
> 
> But just postponing nextval() until after the uniqueness checks
> only decreases the *probability* of non-monotonic values, and
> *does not* preven them. Consindert two transactions
> 
> A: begin ;
> B: Begin ;
> A: insert ... -- IDENTITY generates value 1
> B: insert .. -- IDENTITY generates value 2
> A: rollback ;
> B: commit ;
> 
> Now there is a record with IDENTITY 2, but not with 1. The *only*
> way to fix this is to *not* use a sequence, but rather do
> lock table t in exclusive mode ;
> select max(identity)+1 from t ;
> to generate the identity - but of course this prevents any concurrent
> inserts, which will make this unuseable for any larger database.

While this demonstrates that you can get holes in the sequence, it doesn't
show an example that is not monotonic.

> Note that this is not a deficency of postgres sequences - there is no
> way to guarantee stricly monotonic values while allowing concurrent
> selects at the same time. (Other than lazyly assigning the values, but
> this needs to be done by the application)

With in a single session and barring wrap-around you will get monotonicly
increasing values. You are correct that there is no such guaranty between
separate sessions that overlap in time.

In response to

• Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ? at 2007-03-01 10:26:23 from Florian G. Pflug

Responses

• Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ? at 2007-03-03 00:45:21 from Florian G. Pflug

pgsql-hackers by date

Next:	From: Bruce Momjian	Date: 2007-03-02 22:46:40
	Subject: Re: [HACKERS] Deadlock with pg_dump?
Previous:	From: Tom Lane	Date: 2007-03-02 22:37:33
	Subject: Re: [HACKERS] Deadlock with pg_dump?