X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 64026D1DC7D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Apr 2004 12:17:50 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 50310-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 11 Apr 2004 12:17:53 -0300 (ADT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 92B70D1DACB
	for <pgsql-sql@postgresql.org>; Sun, 11 Apr 2004 12:17:48 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 057F83575D; Sun, 11 Apr 2004 08:17:53 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 0226835758; Sun, 11 Apr 2004 08:17:53 -0700 (PDT)
Date: Sun, 11 Apr 2004 08:17:52 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Bret Hughes <bhughes@elevating.com>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: trigger/for key help
In-Reply-To: <1081662204.6609.72.camel@bretsony>
Message-ID: <20040411080932.Q83278@megazone.bigpanda.com>
References: <1081662204.6609.72.camel@bretsony>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200404/105
X-Sequence-Number: 17480


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