Zoltan Boszormenyi wrote:
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
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-)
A: and B: are meant to denote *two* *different*
transactions running concurrently.
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.
Yes, of course you can prevent gaps by just filling them
with garbage/invalid records of whatever. But I don't see
why this is usefull - either you want, say, your invoice
number to be continuous because it's required by law - or
you don't. But if the law required your invoice numbers to be
continous, surely just filling the gaps with fake invoices
it just as illegal as having gaps in the first place.
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.
I'll have to admit that I haven't actually looked at your patch -
so sorry if I missunderstood things. I got the impression that
tom's main complaint was that you are shuffling too much existing code
around in your patch, and I figured that this is partly because you
try to generate the IDENTITY value as late as possible. Since doing
this won't prevent gaps, but just reduces the probability of creating
them, I thought that a way around tom's concerns might be to drop
I will shut up now, at least until I have read the patch ;-)
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?