Cache lookup failed for relation, when trying to DROP TABLE.

[Mark Gibson]

Hello,
I've just encountered a problem that I don't know how to deal with.
After having a play with SlonyI, I dropped the entire slony cluster
schema, and then tried to drop a schema which held some slave tables
for the slony cluster.

I kept getting the following error:

ERROR: cache lookup failed for relation 4667548

So, I cleared out everything I could manually from the schema,
leaving four remaining tables that refuse to be dropped, giving
the error above (the relation oid varies though).

Anyone know what this means?

Is there a way to forcefully remove these tables and the schema
using the pg_catalog tables?

Is there any other information I should provide that may help?

Specs:
Redhat Enterprise Linux 3
PostgreSQL 7.4.5
Slony-I 1.0.2

Cheers
--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Mark Gibson <gi*****@cromwell.co.uk> writes:

I kept getting the following error:
ERROR: cache lookup failed for relation 4667548

This implies that something someplace still has a link to the table with
that OID. You could do "\set VERBOSITY verbose" so that the code
location the error occurs at gets reported; that might be enough
information to guess what sort of thing is linking to the table.
Or you could just dig through the system catalogs looking for 4667548
in an OID column that links to pg_class.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mark Gibson]

Tom Lane wrote:

Mark Gibson <gi*****@cromwell.co.uk> writes:

I kept getting the following error:
ERROR: cache lookup failed for relation 4667548

This implies that something someplace still has a link to the table with
that OID. You could do "\set VERBOSITY verbose" so that the code
location the error occurs at gets reported; that might be enough
information to guess what sort of thing is linking to the table.
Or you could just dig through the system catalogs looking for 4667548
in an OID column that links to pg_class.

"\set VERBOSITY verbose" gave me the following:

ERROR: XX000: cache lookup failed for relation 4667548
LOCATION: getRelationDescription, dependency.c:1755
and...

After sticking 4667548 everywhere an oid can be stuck, I've found an
entry in the pg_rewrite table.
It's from a rule I added myself, but I dropped it manually, strange that
is still lurking around.

So, I tried deleting the offending entries from pg_rewrite, and now get:

ERROR: XX000: could not find tuple for rule 5173132
LOCATION: getObjectDescription, dependency.c:1669

I'm guessing I'm gonna have to route through pg_catalog for this and
delete all deps manually, but it this going to be safe?
Would I be better off dumping and restoring the whole database?

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Mark Gibson]

Mark Gibson wrote:

I'm guessing I'm gonna have to route through pg_catalog for this and
delete all deps manually, but it this going to be safe?
Would I be better off dumping and restoring the whole database?

Right then, I think I've got this sorted,
DROP TABLE worked after a swift:

DELETE FROM pg_depend WHERE objid = 5173132;

Then DROP SCHEMA also worked.

Do you think I should be safe to continue working with the database now,
without a full dump and restore?
(It's not in production use, but it would take quite some time for
dump/restore)

Cheers.
--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Mark Gibson <gi*****@cromwell.co.uk> writes:

Right then, I think I've got this sorted,
DROP TABLE worked after a swift:
DELETE FROM pg_depend WHERE objid = 5173132;
There's something awfully flaky going on here. The system should never
have let you get into this state in the first place: the entire point of
pg_depend is that you can't delete the referenced object without
deleting the referencing object. Had you been doing anything odd before
this (like perhaps manually deleting catalog rows)? You mentioned
having dropped the troublesome rule; did you do that by-the-book with
DROP RULE, or did you just DELETE FROM pg_rewrite?
Do you think I should be safe to continue working with the database now,
without a full dump and restore?

Hard to say. If you haven't been sticking your fingers where they
shouldn't go, then this definitely represents a failure of the
dependency mechanism. Aside from the possibility of plain old bugs,
I'd be wondering about corrupted indexes on pg_depend.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Mark Gibson]

Tom Lane wrote:

Mark Gibson <gi*****@cromwell.co.uk> writes:

Right then, I think I've got this sorted,
DROP TABLE worked after a swift:
DELETE FROM pg_depend WHERE objid = 5173132;

There's something awfully flaky going on here. The system should never
have let you get into this state in the first place: the entire point of
pg_depend is that you can't delete the referenced object without
deleting the referencing object. Had you been doing anything odd before
this (like perhaps manually deleting catalog rows)? You mentioned
having dropped the troublesome rule; did you do that by-the-book with
DROP RULE, or did you just DELETE FROM pg_rewrite?

I haven't modified anything manually in pg_catalog.
The rule was dropped with DROP RULE.
Although I have been testing out Slony-I, I'm not sure if that has
delved into pg_catalog, I'll have to check with the Slony folks.
I had to remove Slony's schema manually as I was having problems
with it. I was in the process of removing all Slony related stuff,
and all my slave tables when this problem occurred, and was going to
start again from scratch.

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Andrew Sullivan]

On Wed, Oct 06, 2004 at 05:25:58PM +0100, Mark Gibson wrote:

I had to remove Slony's schema manually as I was having problems
with it. I was in the process of removing all Slony related stuff,
and all my slave tables when this problem occurred, and was going to
start again from scratch.

Did your problem happen on a replica, or on the origin? There's a
current dirty, evil hack in Slony that does extremely naughty things
in the catalogues on the replicas. This is slated to go away in the
future, but at the moment it's possible to trip over it if you don't
use Slony's own admin tools.

A

--
Andrew Sullivan | aj*@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Mark Gibson]

Andrew Sullivan wrote:

On Wed, Oct 06, 2004 at 05:25:58PM +0100, Mark Gibson wrote:

I had to remove Slony's schema manually as I was having problems
with it. I was in the process of removing all Slony related stuff,
and all my slave tables when this problem occurred, and was going to
start again from scratch.

Did your problem happen on a replica, or on the origin? There's a
current dirty, evil hack in Slony that does extremely naughty things
in the catalogues on the replicas. This is slated to go away in the
future, but at the moment it's possible to trip over it if you don't
use Slony's own admin tools.

Yes it was on the slave. After a bit more playing with Slony, I've
discovered the cause. I'd created rules on a slave table before
subscribing it to the master, Slony was disabling the rule from
within pg_catalog, so when I manually removed Slony I had some
rogue rules floating around. PostgreSQL didn't know it needed to
drop the rules but it was being restricted from dropping the table
by unknown deps in pg_depend.

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jan Wieck]

On 10/15/2004 4:20 AM, Mark Gibson wrote:

Andrew Sullivan wrote:

On Wed, Oct 06, 2004 at 05:25:58PM +0100, Mark Gibson wrote:

I had to remove Slony's schema manually as I was having problems
with it. I was in the process of removing all Slony related stuff,
and all my slave tables when this problem occurred, and was going to
start again from scratch.

Did your problem happen on a replica, or on the origin? There's a
current dirty, evil hack in Slony that does extremely naughty things
in the catalogues on the replicas. This is slated to go away in the
future, but at the moment it's possible to trip over it if you don't
use Slony's own admin tools.

Yes it was on the slave. After a bit more playing with Slony, I've
discovered the cause. I'd created rules on a slave table before
subscribing it to the master, Slony was disabling the rule from
within pg_catalog, so when I manually removed Slony I had some
rogue rules floating around. PostgreSQL didn't know it needed to
drop the rules but it was being restricted from dropping the table
by unknown deps in pg_depend.

Yes, this is the ugly bit of catalog scrbbling Slony-I 1.0 does. We have
ideas to clean this up in 1.1.
Jan

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings