trigger/for key help - Mailing list pgsql-sql

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 nulltdate   | date
|not nullttime   | time without time zone | not nulltstatus | smallint               | not nullttype   | 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