Skip site navigation (1) Skip section navigation (2)

Re: orphaned trigger

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Jonathan Ellis <jellis(at)advocast(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: orphaned trigger
Date: 2001-05-16 22:14:20
Message-ID: Pine.BSF.4.21.0105161513070.29915-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, 16 May 2001, Jonathan Ellis wrote:

> This is in 7.1.1:
> 
> I was playing around with a table called user_comments and then dropped it.
> One of its columns referenced the user_id column of another table, users.
> 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
> 
> Somehow a constraint trigger that should have been dropped wasn't.
> 
> 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
> 
> It's in pg_trigger all right -- and with tgrelid = (select relfilenode from
> pg_class where relname = 'users') -- but it won't drop.  Would manually
> removing it from pg_trigger cause Bad Things to happen?

Yes, and you'll need to double quote the trigger name.
try 
drop trigger "RI_ConstraintTrigger_44349" on users;

How did you drop the table?  Did you reload a dump file (like the one that
referenced it)? 


In response to

Responses

pgsql-bugs by date

Next:From: Jonathan EllisDate: 2001-05-16 22:57:34
Subject: Re: orphaned trigger
Previous:From: Jonathan EllisDate: 2001-05-16 21:19:05
Subject: orphaned trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group