Thread: 8.3.7, 'cache lookup failed' for a table
I got that sort of error on 8.3.7 (can't upgrade really), is it something that can be easily resolved ? I do understand that OID is gone from the pg catalogue , but still in memory. Will restart of database help in this case ? Was it fixed in following revisions ?? (8.3.x, x>7). -- GJ
Having seen that all previous problems went unresolved, heres a bit
more info. The system is 32 bit, running on enterprise redhat 4.7. It
is slony's slave node, so it will be hit with quite few updates.
My guess is that it happened when we ere adding/removing slony to the
system for Nth time (due to it sometimes going out of sync).
Another thing that makes me think so, is what I've seen in pg_dump's output:
CREATE TRIGGER _simreplic_denyaccess_208
BEFORE INSERT OR DELETE OR UPDATE ON some_table
FOR EACH ROW
EXECUTE PROCEDURE 28799('_somereplic');
Which is obviously wrong.
hth.
anyone please ?
On 11 May 2010, at 16:19, Grzegorz Jaśkiewicz wrote:
> Another thing that makes me think so, is what I've seen in pg_dump's output:
>
> CREATE TRIGGER _simreplic_denyaccess_208
> BEFORE INSERT OR DELETE OR UPDATE ON some_table
> FOR EACH ROW
> EXECUTE PROCEDURE 28799('_somereplic');
>
> Which is obviously wrong.
I must be missing the obvious then, why is that wrong?
Not that I think I can help you with your problem, it's quite unclear what your problem is from what you described so
far- if it's Slony related you're probably better off asking on the slony lists, if it's not you might want to give us
somecontext, like the actual error message and from what command you got that for example.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4bea7ae010413218958980!
Hi Grzegorz, Is it always the same OID(s)? Usually this means something somewhere has a link to an OID that has been removed. You could try digging through pg_catalog lookng for an oid column that refers to the OID in question. In my experience, when a slony 1.2.x slave is involved, this usually means a relation was dropped without first droppingit from replication using DROP TABLE. In this case it may be a trigger on a table that has been "disabled" by slony,it does this by changing pg_trigger.tgrelid to point to an index on the table in question rather than the table itself. Thus when the table is dropped the trigger is left behind, pointing to an index that isn't there. I' probably startwith "select * from "pg_catalog".pg_trigger where tgrelid = <the OID that doesn't exist>", and prune from there. Glyn --- On Wed, 12/5/10, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > From: Grzegorz Jaśkiewicz <gryzman@gmail.com> > Subject: Re: [GENERAL] 8.3.7, 'cache lookup failed' for a table > To: pgsql-general@postgresql.org > Date: Wednesday, 12 May, 2010, 10:33 > anyone please ? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
no it is not slony related. It is a postgresql problem. my original post: http://archives.postgresql.org/pgsql-general/2010-05/msg00402.php
On Wed, May 12, 2010 at 10:57 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote: > Hi Grzegorz, > > Is it always the same OID(s)? > > Usually this means something somewhere has a link to an OID that has been removed. > > You could try digging through pg_catalog lookng for an oid column that refers to the OID in question. > > In my experience, when a slony 1.2.x slave is involved, this usually means a relation was dropped without first droppingit from replication using DROP TABLE. In this case it may be a trigger on a table that has been "disabled" by slony,it does this by changing pg_trigger.tgrelid to point to an index on the table in question rather than the table itself. Thus when the table is dropped the trigger is left behind, pointing to an index that isn't there. I' probably startwith "select * from "pg_catalog".pg_trigger where tgrelid = <the OID that doesn't exist>", and prune from there. It only happened to me once. You think it is because slony is poking around pg_catalog. schema, and it shouldn't , basically ? -- GJ
Did you not mention that this server was a slony slave at some point though? Just because you have removed slony, and the error comes from postgresql itself does not mean the corruption was not causedby misuse of slony. --- On Wed, 12/5/10, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > From: Grzegorz Jaśkiewicz <gryzman@gmail.com> > Subject: Re: [GENERAL] 8.3.7, 'cache lookup failed' for a table > To: "Alban Hertroys" <dalroi@solfertje.student.utwente.nl> > Cc: pgsql-general@postgresql.org > Date: Wednesday, 12 May, 2010, 10:57 > no it is not slony related. > It is a postgresql problem. > > my original post: > http://archives.postgresql.org/pgsql-general/2010-05/msg00402.php > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
--- On Wed, 12/5/10, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > Glyn Astill <glynastill@yahoo.co.uk> > wrote: > > Hi Grzegorz, > > > > Is it always the same OID(s)? > > > > Usually this means something somewhere has a link to > an OID that has been removed. > > > > You could try digging through pg_catalog lookng for an > oid column that refers to the OID in question. > > > > In my experience, when a slony 1.2.x slave is > involved, this usually means a relation was dropped without > first dropping it from replication using DROP TABLE. In > this case it may be a trigger on a table that has been > "disabled" by slony, it does this by changing > pg_trigger.tgrelid to point to an index on the table in > question rather than the table itself. Thus when the table > is dropped the trigger is left behind, pointing to an index > that isn't there. I' probably start with "select * from > "pg_catalog".pg_trigger where tgrelid = <the OID that > doesn't exist>", and prune from there. > > It only happened to me once. > You think it is because slony is poking around pg_catalog. > schema, and > it shouldn't , basically ? > No, Slony 1.2.x pokes around in pg_catalog because in versions of postgres prior to 8.3 (which 1.2.x has to support) therewas no built in way to disable the triggers. So it's not that it slony shouldn't be poking around there, it's that ifyou choose to use slony you should make sure you drop the relation from replication before dropping it - else you'll makea mess.
On 12 May 2010, at 12:01, Glyn Astill wrote: > Did you not mention that this server was a slony slave at some point though? > > Just because you have removed slony, and the error comes from postgresql itself does not mean the corruption was not causedby misuse of slony. Indeed. I wonder if "when we ere adding/removing slony to the system for Nth time (due to it sometimes going out of sync)"may be caused by that as well. > --- On Wed, 12/5/10, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > >> From: Grzegorz Jaśkiewicz <gryzman@gmail.com> >> Subject: Re: [GENERAL] 8.3.7, 'cache lookup failed' for a table >> To: "Alban Hertroys" <dalroi@solfertje.student.utwente.nl> >> Cc: pgsql-general@postgresql.org >> Date: Wednesday, 12 May, 2010, 10:57 >> no it is not slony related. >> It is a postgresql problem. >> >> my original post: >> http://archives.postgresql.org/pgsql-general/2010-05/msg00402.php >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4bea7e6d10417427874228!
On Wed, May 12, 2010 at 11:09 AM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On 12 May 2010, at 12:01, Glyn Astill wrote: > >> Did you not mention that this server was a slony slave at some point though? >> >> Just because you have removed slony, and the error comes from postgresql itself does not mean the corruption was not causedby misuse of slony. > > Indeed. I wonder if "when we ere adding/removing slony to the system for Nth time (due to it sometimes going out of sync)"may be caused by that as well. > ok, so either upgrade to newer version of slony, or drop all tables, and recreate them every time slony is removed and readded to the database. And I guess the only reason postgresql doesn't like it, is due to slony's behavior. thanks guys. -- GJ
--- On Wed, 12/5/10, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > Alban Hertroys > <dalroi@solfertje.student.utwente.nl> > wrote: > > On 12 May 2010, at 12:01, Glyn Astill wrote: > > > >> Did you not mention that this server was a slony > slave at some point though? > >> > >> Just because you have removed slony, and the error > comes from postgresql itself does not mean the corruption > was not caused by misuse of slony. > > > > Indeed. I wonder if "when we ere adding/removing slony > to the system for Nth time (due to it sometimes going out of > sync)" may be caused by that as well. > > > > ok, so either upgrade to newer version of slony, or drop > all tables, > and recreate them every time slony is removed and readded > to the > database. > Upgrading to slony 2.03 would prevent this from happening, but no there's no need to drop and recreate all tables every timeslony is removed and re-added to the database - you just need you make sure you use slonik SET DROP TABLE *before* droppingany table in postgresql. Look, here http://www.slony.info/documentation/stmtsetdroptable.html > And I guess the only reason postgresql doesn't like it, is > due to > slony's behavior. > Nope, due to slony not being used correctly!
2010/5/12 Glyn Astill <glynastill@yahoo.co.uk>: > --- On Wed, 12/5/10, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > >> Alban Hertroys >> <dalroi@solfertje.student.utwente.nl> >> wrote: >> > On 12 May 2010, at 12:01, Glyn Astill wrote: >> > >> >> Did you not mention that this server was a slony >> slave at some point though? >> >> >> >> Just because you have removed slony, and the error >> comes from postgresql itself does not mean the corruption >> was not caused by misuse of slony. >> > >> > Indeed. I wonder if "when we ere adding/removing slony >> to the system for Nth time (due to it sometimes going out of >> sync)" may be caused by that as well. >> > >> >> ok, so either upgrade to newer version of slony, or drop >> all tables, >> and recreate them every time slony is removed and readded >> to the >> database. >> > > Upgrading to slony 2.03 would prevent this from happening, but no there's no need to drop and recreate all tables everytime slony is removed and re-added to the database - you just need you make sure you use slonik SET DROP TABLE *before*dropping any table in postgresql. Look, here http://www.slony.info/documentation/stmtsetdroptable.html > > >> And I guess the only reason postgresql doesn't like it, is >> due to >> slony's behavior. >> > > Nope, due to slony not being used correctly! > ok, got it. -- GJ
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 >> You think it is because slony is poking around pg_catalog. >> schema, and it shouldn't , basically ? > No, Slony 1.2.x pokes around in pg_catalog because in versions > of postgres prior to 8.3 (which 1.2.x has to support) there was > no built in way to disable the triggers. This is not technically true. We had the same problem when designing Bucardo. The canonical (and completely safe) way is to do ALTER TABLE DISABLE TRIGGER. However, the locking involved in such a call is too expensive for almost all uses of a replication system, so both Slony and Bucardo made the call to muck with the system catalogs instead. This does lead to the occassional "OID not found" message, especially on very busy systems. It should also be noted that Bucardo and Slony disable rules as well as triggers - something that cannot be done at the SQL level. So the catalog tweaks are really necessary after all in order to disable triggers *and* rules. As pointed out, this is now completely solved with the session_replication_role setting, fully supported by Bucardo, and by Slony in the 2.x branch. (The above glosses over a few details between the systems, but it's mostly a moot point now as pre-8.3 systems are becoming relatively rare) - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005121002 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEUEAREDAAYFAkvqtbQACgkQvJuQZxSWSsjnqQCcDOTVMpCFowgLCR8EVG+V0LsD pykAmNr6YTbtBRxHXB55ZXErevG07Js= =LtbH -----END PGP SIGNATURE-----