Vik Fearing <vik@2ndquadrant.fr> writes:
> For the archives, the test case at that link is:
Thanks for copying that into the archives.
The "bar" trigger is actually irrelevant and can be dropped from the
example. The problem is that because the FK constraint on "b" is
deferred, there is an FK-check trigger event waiting to be fired
at the end of the transaction, and we're deleting the pg_trigger
row out from under it.
Ordinarily, we'd prevent you from dropping a trigger that has
unfired events, but the code in tablecmds.c is only checking the
"b" table, and the event in question is for "a"'s trigger.
With the attached patch, you get
ERROR: cannot ALTER TABLE "a" because it has pending trigger events
which I think is a sane behavior, although maybe the precise wording
of the error could be quibbled with. (But improving that would
require fooling with the API of CheckTableNotInUse, which does not
seem worthwhile.)
The other approach we could take is to allow the DROP CONSTRAINT and
silently ignore queued trigger events if we can't find their pg_trigger
row ... but that seems pretty scary in terms of its ability to mask bugs,
and it's not consistent with past choices.
Barring objection, I'll add a test case to this and push it.
regards, tom lane
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f97bee5..6322fa7 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*************** ATExecDropConstraint(Relation rel, const
*** 7723,7728 ****
--- 7723,7746 ----
is_no_inherit_constraint = con->connoinherit;
/*
+ * If it's a foreign-key constraint, we'd better lock the referenced
+ * table and check that that's not in use, just as we've already done
+ * for the constrained table (else we might, eg, be dropping a trigger
+ * that has unfired events). But we can/must skip that in the
+ * self-referential case.
+ */
+ if (con->contype == CONSTRAINT_FOREIGN &&
+ con->confrelid != RelationGetRelid(rel))
+ {
+ Relation frel;
+
+ /* Must match lock taken by RemoveTriggerById: */
+ frel = heap_open(con->confrelid, AccessExclusiveLock);
+ CheckTableNotInUse(frel, "ALTER TABLE");
+ heap_close(frel, NoLock);
+ }
+
+ /*
* Perform the actual constraint deletion
*/
conobj.classId = ConstraintRelationId;
