From: Bret Hughes <bhughes@elevating.com>
To: postgresql sql list <pgsql-sql@postgresql.org>
Subject: trigger/for key help
Date: 11 Apr 2004 00:43:22 -0500
Message-ID: <1081662204.6609.72.camel@bretsony> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bhughes@elevating.com
Subject: Re: trigger/for key help
In-Reply-To: <1081662204.6609.72.camel@bretsony>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox