Subject: trigger/for key help
From: Bret Hughes <bhughes@elevating.com>
To: postgresql sql list <pgsql-sql@postgresql.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 11 Apr 2004 00:43:22 -0500
Message-Id: <1081662204.6609.72.camel@bretsony>
Mime-Version: 1.0

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

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


so digging around I found that there were still entries in pg_constraint
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs'); 
    conname     | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc 
----------------+--------------+---------+---------------+-------------+----------+----------+-----------+-------------+-------------+---------------+--------+---------+--------+--------
 diag_logs_pkey |         2200 | p       | f             | f          
|  2041950 |        0 |         0 |             |            
|               | {1}    |         |        | 
 $1             |         2200 | f       | f             | f          
|  2041950 |        0 |   1027502 | a           | a           |
u             | {2}    | {1}     |        | 
 $2             |         2200 | f       | f             | f          
|  2041950 |        0 |   2041960 | a           | a           |
u             | {6}    | {1}     |        | 
(3 rows)

elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs') and contype='f'; 
 conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc 
---------+--------------+---------+---------------+-------------+----------+----------+-----------+-------------+-------------+---------------+--------+---------+--------+--------
 $1      |         2200 | f       | f             | f           | 
2041950 |        0 |   1027502 | a           | a           |
u             | {2}    | {1}     |        | 
 $2      |         2200 | f       | f             | f           | 
2041950 |        0 |   2041960 | a           | a           |
u             | {6}    | {1}     |        | 
(2 rows)

elevating=# delete from pg_constraint where conrelid in (select oid from
pg_class where relname = 'diag_logs') and contype='f'; 
DELETE 2

Still no joy now I seem to have deleted the pk constraint too. 

elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs') ; 
 conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc 
---------+--------------+---------+---------------+-------------+----------+----------+-----------+-------------+-------------+---------------+--------+---------+--------+--------
(0 rows)


 I obviously don't understand the system tables well enough to be dinkin
around in here but here I am and wanting to fix it .

Any ideas, anyone?


Bret