Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

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