Re: Statement-level Triggers For Uniqueness Checks - Mailing list pgsql-hackers

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)