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

Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
Cc: Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?
Date: 2007-03-02 22:38:31
Message-ID: 20070302223831.GB28490@wolff.to (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

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

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