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.huin...@gmail.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.
>
>
regression.diffs
Description: Binary data
0001-Refactor-per-row-unique-key-deferred-constraint-trig.patch
Description: Binary data
