Bruno Wolff III wrote:
On Thu, Mar 01, 2007 at 11:26:23 +0100,
"Florian G. Pflug" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match