Bruno Wolff III wrote:
> 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.
Sorry, my formulation was sloppy. What I meant is that you can't
guarantee gaplessness.
greetings, Florian Pflug