Обсуждение: Cache lookup error
Hi, I am new to user lists, so please don't be to hard on me if I am doing anything wrong. I have been running a postgres database with slony replication for some years now as a backend for a apache webserver and lately some mono (.net) programs. The database and webserver is running on a Ubuntu LTS server and I compile the programs myself to be reasonable updated. Current version is postgres 8.3.7 and Slony1-2.16 Yesterday I got a lot of errormessages in the postgres log. 2009-11-12 06:11:51 CET INSERTERROR: cache lookup failed for type 19218 2009-11-12 06:11:51 CET INSERTCONTEXT: SQL statement "INSERT INTO _slony_webtrim.sl_log_1 (log_origin, log_xid, log_tableid, log_actionseq, log_cmdtype, log_cmddata) VALUES (1, $1, $2, nextval('_slony_webtrim.sl_action_seq'), $3, $4);" 2009-11-12 06:11:51 CET INSERTSTATEMENT: INSERT INTO reservation1 (activityid, activitydate, clientno, deptno, reservations, activityname, room, dayofweek, starttime, maingroupname, activitystartdate, activityenddate) SELECT activityid, '2009-11-12' AS activitydate, clientno, deptno, 1 AS reservations, activityname, room, dayofweek, starttime, maingroupname, activitystartdate, activityenddate FROM activity WHERE activityid = 5374 2009-11-12 06:27:48 CET UPDATEERROR: cache lookup failed for type 19218 2009-11-12 06:27:48 CET UPDATECONTEXT: SQL statement "INSERT INTO _slony_webtrim.sl_log_1 (log_origin, log_xid, log_tableid, log_actionseq, log_cmdtype, log_cmddata) VALUES (1, $1, $2, nextval('_slony_webtrim.sl_action_seq'), $3, $4);" 2009-11-12 06:27:48 CET UPDATESTATEMENT: UPDATE reservation2 SET datetimereceipt = '2009-11-12 06:27:48' WHERE clientno = 342 AND refno = 945005 The applications worked happily as if everything were ok, but the data was never written to disk and is lost. The error came after I dropped (cascade) the slony schema and reinstalled it. I were not able to dump the database (just hanging), but dumping table by table went ok. After reinstalling the database (initdb) and install slony, everything seems ok. I have scripts for installing slony, so there should not be any differences in the way it is done. I have had a database error once before, also after reinstalling slony. I would appreciate very much if someone could tell me what kind of error this is, and maybe some hints so I might avoid it in the future. Best regards Gunnar
Gunnar Sønsteby wrote: > Hi, > > I am new to user lists, so please don't be to hard on me if I am doing > anything wrong. > > I have been running a postgres database with slony replication for some > years now as a backend for a apache webserver and lately some mono > (.net) programs. The database and webserver is running on a Ubuntu LTS > server and I compile the programs myself to be reasonable updated. > > Current version is postgres 8.3.7 and Slony1-2.16 OK. > Yesterday I got a lot of errormessages in the postgres log. > > 2009-11-12 06:11:51 CET INSERTERROR: cache lookup failed for type 19218 > 2009-11-12 06:11:51 CET INSERTCONTEXT: SQL statement "INSERT INTO > _slony_webtrim.sl_log_1 ... > The error came after I dropped > (cascade) the slony schema and reinstalled it. I were not able to dump > the database (just hanging), but dumping table by table went ok. After > reinstalling the database (initdb) and install slony, everything seems > ok. I have scripts for installing slony, so there should not be any > differences in the way it is done. Well, the error suggests something didn't get dropped/reinstalled cleanly. It's looking up a type definition with OID 19218 and not finding it. Typically you would get this with a function in pl<something> or in a trigger, which ties in with slony again. So - something is trying to access a slony type via its old OID rather than its new one (or something like that). Not sure what this would be, since the drop-schema + cascade should have removed anything that could do this. It's probably impossible to identify precisely what it was since it's not there any more. Next time, perhaps try "SLONIK DROP NODE" before manually dropping the schema. http://www.slony.info/documentation/dropthings.html -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes: > Gunnar Sønsteby wrote: >> 2009-11-12 06:11:51 CET INSERTERROR: cache lookup failed for type 19218 > So - something is trying to access a slony type via its old OID rather > than its new one (or something like that). Not sure what this would be, > since the drop-schema + cascade should have removed anything that could > do this. > It's probably impossible to identify precisely what it was since it's > not there any more. Well, there's still a reference to it somewhere in the system catalogs, and locating the reference would give you a pretty good clue what it was --- not to mention being a prerequisite step for cleaning up. I'd try searching pg_class.reltype and pg_proc.prorettype for starters. If no luck there, go through all the columns that are cited as linking to pg_type.oid in http://www.postgresql.org/docs/8.3/static/catalogs.html regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> Gunnar Sønsteby wrote: >>> 2009-11-12 06:11:51 CET INSERTERROR: cache lookup failed for type 19218 > >> So - something is trying to access a slony type via its old OID rather >> than its new one (or something like that). Not sure what this would be, >> since the drop-schema + cascade should have removed anything that could >> do this. > >> It's probably impossible to identify precisely what it was since it's >> not there any more. > > Well, there's still a reference to it somewhere in the system catalogs, > and locating the reference would give you a pretty good clue what it > was --- not to mention being a prerequisite step for cleaning up. > I'd try searching pg_class.reltype and pg_proc.prorettype for starters. > If no luck there, go through all the columns that are cited as linking > to pg_type.oid in > http://www.postgresql.org/docs/8.3/static/catalogs.html I was thinking it was a reference inside a function or similar. I'm guessing "compiled" plpgsql is opaque? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Tom Lane wrote: > > Richard Huxton <dev@archonet.com> writes: > >> Gunnar Sønsteby wrote: > >>> 2009-11-12 06:11:51 CET INSERTERROR: cache lookup failed for type 19218 > > > >> So - something is trying to access a slony type via its old OID rather > >> than its new one (or something like that). Not sure what this would be, > >> since the drop-schema + cascade should have removed anything that could > >> do this. > > > >> It's probably impossible to identify precisely what it was since it's > >> not there any more. > > > > Well, there's still a reference to it somewhere in the system catalogs, > > and locating the reference would give you a pretty good clue what it > > was --- not to mention being a prerequisite step for cleaning up. > > I'd try searching pg_class.reltype and pg_proc.prorettype for starters. > > If no luck there, go through all the columns that are cited as linking > > to pg_type.oid in > > http://www.postgresql.org/docs/8.3/static/catalogs.html > > I was thinking it was a reference inside a function or similar. I'm > guessing "compiled" plpgsql is opaque? That doesn't persist across a restart, though. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.