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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
