I was just fooling around with replacing the existing plain index on
pg_trigger.tgrelid with a unique index on (tgrelid, tgname). In theory
this should not affect anything --- the code already enforced that two
triggers on the same relation can't have the same name. The index
should merely provide a backup check.
So I was a tad surprised to get a regression test failure:
*** ./expected/foreign_key.out Thu Apr 11 15:13:36 2002
--- ./results/foreign_key.out Thu Apr 18 21:26:20 2002
***************
*** 899,905 **** ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable --
fails(1,1) is being referenced (twice) update pktable set base1=3 where base1=1;
! ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable -- this sequence of
twodeletes will work, since after the first there will be no (2,*) references delete from pktable where base2=2; delete
frompktable where base1=2;
--- 899,905 ---- ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable --
fails(1,1) is being referenced (twice) update pktable set base1=3 where base1=1;
! ERROR: <unnamed> referential integrity violation - key referenced from pktable not found in pktable -- this sequence
oftwo deletes will work, since after the first there will be no (2,*) references delete from pktable where base2=2;
deletefrom pktable where base1=2;
======================================================================
This particular test involves a table with a foreign-key reference to
itself, ie, it's both PK and FK. What apparently is happening is that
the two RI triggers are now being fired in a different order than
before. While either of them would have detected an error, we now get
the other error first.
Does this bother anyone? It seems to me that the old code essentially
had no guarantee at all about the order in which the triggers would
fire, and so it was pure luck that the regression test never showed
the other message.
With the modified code, because we load the triggers by scanning
an index on (tgrelid, tgname), it is actually true that triggers are
fired in name order. We've had requests in the past to provide a
well-defined firing order for triggers --- should we document this
behavior and support it, or should we pretend it ain't there?
BTW, the same goes for rules: it would now be pretty easy to guarantee
that rules are fired in name order.
regards, tom lane
