Re: trigger/for key help

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>';