can't delete because of constraint from zombie table

From: Jaime Silvela <JSilvela(at)Bear(dot)com>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: can't delete because of constraint from zombie table
Date: 2006-11-17 20:25:42
Message-ID: 455E1AC6.5000805@bear.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm finding a strange sort of 'zombie' table behavior.
I try to delete a row of a database that used to be referenced by a
table that has now been deleted.

I do
delete from staging_deal where staging_deal_id = 1
and get
ERROR: update or delete on "staging_deal" violates foreign key
constraint "staging_deal_fkey" on "staging_document"
DETAIL: Key (staging_deal_id)=(1) is still referenced from table
"staging_document".

I try to find that table
select * from staging_document
but
ERROR: relation "staging_document" does not exist

However pg_class does know that table
select * from pg_class where relname = 'staging_document'
gives me a row

The version is
"PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3"

I've tried reindexing pg_class, but the issue hasn't been solved.
Restarting Postgres doesn't help either.
On thing I tried in a test deployment was to delete the row from
pg_class, but I get
ERROR: could not open relation with OID 22427646
type errors, and it seems to leave me worse off.

Any ideas?

Thank you
Jaime

***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice. You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of: (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wm.A.Stafford 2006-11-17 20:46:23 Copy command to load data into a PostgreSQL DB
Previous Message Stephen Harris 2006-11-17 19:57:19 Shutting down a warm standby database in 8.2beta3