Florian G. Pflug írta:
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 ;
I can understand that. But your example is faulty,
you can't have transaction inside a transaction.
Checkpoints are another story. 8-)
You can have some application tricks to
have continous sequence today with regular
serials but only if don't have a unique index
that doesn't use the serial column. Inserting
a record to that table outside the transaction,
making note of the serial value.
If subsequent processing fails (because of unique,
check constraint, etc) you have to go back to the main
table and modify the record, indicating that the record
isn't representing valid data. But you must keep it with
the serial value it was assigned. I have seen systems
requiring this. My point is that with the identity
column, you will be able to define unique index
on the table that exludes the identity column.
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)
Agreed.
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.
Intrusive, hm? The catalog have to indicate that the column
is IDENTITY, otherwise you cannot know it.
The cost I am thinking now is an extra heap_update()
after heap_insert() without generating the identity value
and inserting index tuples to indexes that doesn't
contain the identity column.
Best regards,
Zoltán Böszörményi
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
