Forum OpenACS Q&A: orphaned trigger?

1: orphaned trigger?

Posted by Jonathan Ellis on 05/16/01 05:34 AM

I was playing around with a table called user_comments and then
dropped it. One of its columns referenced users.user_id. Now
whenever I try to update users, I get results like this:

bf2-new=# update users set last_visit=sysdate() where user_id=4;
ERROR: Relation 'user_comments' does not exist

It looks like somehow a trigger that should have been dropped
wasn't. I tried playing with pg_trigger and pg_class but if I'm not
mistaken (select tgname from pg_trigger where tgrelid = (select
relfilenode from pg_class where relname = 'users')) I have 466
triggers on the users table, mostly "annonymous" referential
integrity constraints. RI_ConstraintTrigger_43426, etc. Is there
any way to drop the right ones without going through a dump and
manually whacking out references to user_comments?

PG 7.1.1 btw.

2: Response to orphaned trigger? (response to 1)

Posted by Don Baccus on 05/16/01 06:05 AM

This sort of problem isn't unique to Postgres, and is one reason why aD started assigning names to nearly all constraints in ACS 4.

In PG, referential integrity triggers should be dropped automatically when you drop a referencing table, so I would concentrate on the triggers that *aren't* anonymous in this case. Try to track it down to the trigger name in one of the .sql files.

This is tedious, no doubt. This is one reason why packages should provide well-bundled "drop" sql scripts. The package/ACS 4 approach tries to do this but it isn't complete in the Oracle version supplied by aD. ACS 3.2 (the source for OpenACS 3.2.5) didn't even try.

For better or worse, you're seeing one reason why aD tried to get this right via *drop* scripts in ACS 4 and why our OpenACS 4 project will try to solve the problem after we've ported the existing code.

3: Response to orphaned trigger? (response to 1)

Posted by Jonathan Ellis on 05/16/01 03:04 PM

I didn't manually create any triggers on this table. I looked at the dump file and this is the only reference to user_comments:

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "users" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'user_comments', 'users', 'UNSPECIFIED', 'user_id', 'user_id');

but I am not out of the woods here because it won't let me drop it:

bf2-new=# drop trigger RI_ConstraintTrigger_44349 on users;
ERROR: DropTrigger: there is no trigger ri_constrainttrigger_44349 on relation users

would manually removing it from pg_trigger cause Bad Things to happen?

4: Response to orphaned trigger? (response to 1)

Posted by Don Baccus on 05/16/01 07:42 PM

Well, this is a bug in PG 7.1.1 (and probably previous versions). If you're feeling public spirited, the thing to do is to talk to the PG developers (via the pg-bugs mailing list). They'll probably want a dump of the database to look at.

It would help if you could figure out a way to reproduce the problem reliably. Have you done odd stuff with granting permissions and that sort of thing in the database or is this just a vanilla one-user situtation?

Mucking around with pg_trigger might get you there, I've never ran into a case like this so don't know for sure. But I'd report the bug first and wait for feedback ...

5: Response to orphaned trigger? (response to 1)

Posted by Jonathan Ellis on 05/17/01 04:55 AM

Here's what Stephan Sbazo on pgsql-bugs had to say:

Wait, okay, I think I know what happened. There was a problem with 7.0.? dumps not having the information about the "other" table in the references constraint written out so when you dropped the table it didn't realize it needed to drop the trigger. You may want to drop the constraint triggers and recreate them with alter table add constraint.

(I'd created the table in 7.0.2 before upgrading.)

6: Response to orphaned trigger? (response to 1)

Posted by Jonathan Ellis on 07/25/01 06:43 AM

because I am stupid I forgot the solution to this is drop trigger "RI_ConstraintTrigger_44349" on users; (with the quotes); you get the number from the comments in the dump file. I found out the hard way today what manually deleting rows from pg_trigger does. :(