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

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

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu>
Cc: 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-01 10:26:23
Message-ID: 45E6AA4F.4030907@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Zoltan Boszormenyi wrote:
> The GENERATED column is an easy of use feature
> with possibly having less work, whereas the IDENTITY
> column is mandatory for some applications (e.g. accounting
> and billing is stricter in some countries) where you simply
> cannot skip a value in the sequence, the strict monotonity is
> not enough.

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.

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)

I agree that I'd be nice to generate the identity columns as late as
possible to prevents needless gaps, but not if price is a for more
intrusive patch, or much higher complexity.

greetings, Florian Pflug


In response to

Responses

pgsql-hackers by date

Next:From: Zoltan BoszormenyiDate: 2007-03-01 10:48:02
Subject: Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?
Previous:From: Gregory StarkDate: 2007-03-01 10:04:20
Subject: Re: COMMIT NOWAIT Performance Option

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