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 two deletes will work, since after the first there will be no (2,*) references delete from pktable where base2=2; delete from pktable 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 of two deletes will work, since after the first there will be no (2,*) references delete from pktable where base2=2; delete from 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org