Thread: Cache lookup failed for relation, when trying to DROP TABLE.
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.
Mark Gibson <gibsonm@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
Tom Lane wrote: > Mark Gibson <gibsonm@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.
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.
Mark Gibson <gibsonm@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
Tom Lane wrote: > Mark Gibson <gibsonm@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.
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 | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
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.
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. # #================================================== JanWieck@Yahoo.com #