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

Re: trigger/for key help

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Bret Hughes <bhughes(at)elevating(dot)com>
Cc: postgresql sql list <pgsql-sql(at)postgresql(dot)org>
Subject: Re: trigger/for key help
Date: 2004-04-11 15:17:52
Message-ID: 20040411080932.Q83278@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Sat, 11 Apr 2004, Bret Hughes wrote:

> S*t s*t s*t.  I have managed to screw up the system tables trying to
> delete a foreign key on a new table I was going to start using tomorrow.
>
>
> elevating-# \d diag_logs
>                                         Table "diag_logs"
>  Column  |          Type          |
> Modifiers
> ---------+------------------------+---------------------------------------------------------------
>  sernum  | integer                | not null default
> nextval('public.diag_logs_sernum_seq'::text)
>  display | integer                | not null
>  tdate   | date                   | not null
>  ttime   | time without time zone | not null
>  tstatus | smallint               | not null
>  ttype   | smallint               | not null
> Indexes: diag_logs_display,
>          diag_logs_tdate,
>          diag_logs_tstatus
> Primary key: diag_logs_pkey
>
> There used to be to foreign key constraints named $1 and $2 (I cut and
> pasted sql from a dump of another table that caused the trigger names
> that I was trying to get rid of)
>
> These were created with alter table like this :
>
> elevating=# ALTER TABLE ONLY diag_logs   ADD CONSTRAINT "$2" FOREIGN KEY
> (ttype) REFERENCES test_types(num);
> ALTER TABLE
>
> based on a bunch of surfing I deleted the six rows in pg_tigger that
> referred to
>
> elevating=# delete from pg_trigger where tgargs like  '%diag_logs%';
> DELETE 6

For future note, in recent versions (IIRC 7.3 and above), you should
probably use ALTER TABLE DROP CONSTRAINT, in versions older than that, you
should select the triggers and use DROP TRIGGER "<name>" so as to have the
system handle the next step for you.

> elevating=# drop table diag_logs;
> ERROR:  2 trigger record(s) not found for relation "diag_logs"

This is because reltriggers in the pg_class row for the table in question
is incorrect.  You can fix this by updating the rows (*).  You'll probably
want to fix pg_constraint as well, but I think that the table will
function properly at least until you try to dump it.

(*) something like this should work
 update pg_class set reltriggers=(select count(*) from pg_trigger where
tgrelid=pg_class.oid) where relname='<insert name here>';

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-04-11 16:00:55
Subject: Re: cursors and for loops?
Previous:From: Bret HughesDate: 2004-04-11 05:43:22
Subject: trigger/for key help

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