On Thu, Mar 01, 2007 at 11:26:23 +0100, "Florian G. Pflug" <fgp@phlo.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.
