Re: Statement-level Triggers For Uniqueness Checks

So I took a first pass at this, and I got stuck.

The basic design is that instead of creating one row-level trigger per
deferrable unique constraint, we instead create one insert-statement level
trigger and one update-statement level trigger. Both call the function
unique_key_recheck(), which now attempts to walk the inserted transition
table, doing basically the same checks that were done in the per-row
trigger. I'm hoping for some performance advantage for large row
inserts/updates due to N-1 fewer triggers firing and N-1 attempts to lock
the unique index.

The regression diff (attached) seems to imply that the triggers simply are
not firing, though. I have verified that the triggers are created:

test=# CREATE TEMPORARY TABLE test ( x integer PRIMARY KEY DEFERRABLE
INITIALLY DEFERRED );

CREATE TABLE

test=# SELECT * FROM pg_trigger WHERE

oid tgconstraint tgdeferrable tginitdeferred tgnargs
tgqual

tgargs tgconstrindid tgenabled tgisinternal tgnewtable
tgrelid

tgattr tgconstrrelid tgfoid tgname tgoldtable
tgtype

test=# SELECT * FROM pg_trigger WHERE tgrelid = 'test'::regclass;

oid | tgrelid | tgname | tgfoid | tgtype |
tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | t

gdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual |
tgoldtable | tgnewtable

-------+---------+------------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--

------------+----------------+---------+--------+--------+--------+------------+------------------------------

16392 | 16387 | PK_ConstraintTrigger_i_16392 | 1250 | 4 | O
| t | 0 | 16390 | 16391 | t

| t | 0 | | \x | |
| pg_inserted_transition_table

16393 | 16387 | PK_ConstraintTrigger_u_16393 | 1250 | 16 | O
| t | 0 | 16390 | 16391 | t

| t | 0 | | \x | |
| pg_inserted_transition_table

(2 rows)

Any idea where I went wrong?

On Mon, Dec 17, 2018 at 9:56 AM Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
wrote:

> In digging around the codebase (see thread: Referential Integrity Checks
> with Statement-level Triggers), I noticed that unique constraints are
> similarly enforced with a per-row trigger.
>
> The situation with unique indexes is fairly similar to the situation with
> RI checks: there is some overhead to using a transition table, but that
> overhead may be less than the cost of firing a trigger once per row
> inserted/updated.
>
> However, there are some significant differences (apologies to everyone
> already familiar with this part of the code, it's new to me).
>
> For one, there is no analog to RI_Initial_Check(). Instead the constraint
> is initially checked via building/finding the unique index that would
> enforce the uniqueness check.
>
> Then, the actual lookup done in unique_key_recheck has to contend with the
> intricacies of HOT updates, so I don't know if that can be expressed in an
> SPI query. Even if not, I think it should be possible to iterate over
> the EphemeralNamedRelation and that would result itself have a payoff in
> reduced trigger calls.
>
> I'm going to be working on this as a POC patch separate from the RI work,
> hence the separate thread, but there's obviously a lot of overlap.
>
> All advice is appreciated.
>
>