Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ? - Mailing list pgsql-hackers

Hi,

Tom Lane írta:
> Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:
>   
>> Would it be acceptable?
>>     
>
> No, because you can't create index entries when you haven't yet got the
> TID for the heap tuple.  What do you propose doing, insert a dummy index
> entry and then go back to fill it in later?  Aside from approximately
>   

No, I was thinking about breaking up e.g. heap_insert()
to be able to mix with ExecInsertIndexTuples() so I get a
pinned buffer and have the heap_tuple's t_self set first
then doing the uniqueness checks step by step.
BTW, can I use modify_tuple() after doing
RelationGetBufferForTuple() and RelationPutHeapTuple(),
right?

> doubling the work involved, this is fundamentally broken because no
>   

Well, the work wouldn't be doubled as all the unique indexes
have to be checked anyway with the current way, too, to have
the tuple accepted into the database.

> other backend could know what to do upon encountering the dummy index
> entry --- there's no way for it to check if the entry references a live
> tuple or not.  Not to mention that a crash here will leave a permanently
> dummy index entry that there's no way to vacuum.
>
> The other rearrangements you suggest are not any more acceptable;
> we are not going to restructure the entire handling of defaults and
> check constraints around a single badly-designed SQL2003 feature.
>   

My IDENTITY/GENERATED patch broke up the
checks currently this way (CHECK constraints are prohibited
for special case columns):

- normal columns are assigned values (maybe using DEFAULT)
- check NOT NULLs and CHECKs for normal columns

( Up to this point this works the same way as before if you don't  use neither IDENTITY nor GENERATED. )

- assign GENERATED with ther values
- check NOT NULLs for GENERATED
- assign IDENTITY with value
- check NOT NULL for IDENTITY

and

- check UNIQUE for everything

Identity would be special so it doesn't inflate the sequence
if avoidable. Currently the only way if UNIQUE fails
for any index which is still very much makes it unusable.

What I would like to achieve is for IDENTITY to skip
a sequence value and fail to be INSERTed if the IDENTITY
column's uniqe check is failed. Which pretty much means
that there is already a record with that IDENTITY value
regardless of the UNIQUE index is defined for only the IDENTITY
column or the IDENTITY column is part of a multi-column
UNIQUE index.

If I could broke up the order of events the way I described
in my first mail, I could re-enable having CHECK constraints
for both IDENTITY and GENERATED columns.

The point with GENERATED is you have to have
all other columns assigned with values BEFORE
being able to compute a GENERATED column
that reference other columns in its expression so
you _have to_ break up the current order of computing
DEFAULTs. I know a computed column could be done
either in the application or with SELECTs but compare
the amount of work: if you do it in the SELECT you have to
compute the expressions every time the SELECT is run
making it slower. Doing it on UPDATE or INSERT
makes it LESS work in a fewer INSERT/UPDATE +
heavy SELECT workload. Of course, for a heavy UPDATE
workload it makes it more work but only if you actually
use GENERATED columns. It means exatly the same
amount of work if you use IDENTITY as with SERIAL,
it's just made in different order.

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.

Best regards,
Zoltán Böszörményi