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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
