From: Bret Hughes <bhughes@elevating.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: trigger/for key help
Date: 11 Apr 2004 14:19:14 -0500
Message-ID: <1081711155.6399.165.camel@bretsony> (raw)
In-Reply-To: <20040411080932.Q83278@megazone.bigpanda.com>
References: <1081662204.6609.72.camel@bretsony>
<20040411080932.Q83278@megazone.bigpanda.com>
On Sun, 2004-04-11 at 10:17, Stephan Szabo wrote:
>
> 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=# 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>';
>
ok I updated the rows by relname individually so I could limit issues
should I screw it up.
I can now get a dump of the db but still cannot drop the two tables I
added. I get a different message now :
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
elevating=# drop table diag_logs;
ERROR: could not find tuple for constraint 2041954
and
elevating=# \d test_types;
Table "test_types"
Column | Type | Modifiers
----------------+---------------+--------------------
num | smallint | not null
progname | character(25) | not null
severity_level | smallint | not null default 1
Primary key: test_types_pkey
elevating=# drop table test_types;
ERROR: could not find tuple for constraint 2041971
elevating=#
I can find no reference to these oids anywhere.
I wasgonig to restart postmaster incase something is cached but I hate
to since I don't know if it will restart properly.
TIA
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, sszabo@megazone.bigpanda.com
Subject: Re: trigger/for key help
In-Reply-To: <1081711155.6399.165.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