Thread: cache lookup of relation 165058647 failed
I have a problem with postgresql tables. periodicaly, I would say frequently about 5-10 time per hour i have such errors in my server log file: 2004-04-14 12:23:32 [73692] ERROR: cache lookup of relation 149064743 failed 2004-04-14 12:23:32 [73692] ERROR: Relation "tmp_table1" does not exist 2004-04-14 12:23:32 [73692] ERROR: Relation "tmp_table1" does not exist So turn on debugging options and have that's what i got: 2004-04-14 12:24:54 [74021] LOG: connection received: host=[local] 2004-04-14 12:24:54 [74021] LOG: connection authorized: user=ejob_guest database=ejob 2004-04-14 12:24:54 [74021] LOG: query: begin; select getdatabaseencoding(); commit 2004-04-14 12:24:54 [74021] LOG: duration: 0.000987 sec 2004-04-14 12:24:54 [74021] LOG: query: SELECT d.config_id, d.text1 as text, d.field_name, 2004-04-14 12:24:54 [74021] LOG: duration: 0.005295 sec 2004-04-14 12:24:54 [74021] LOG: query: CREATE TEMP TABLE tmp_table1 AS SELECT advert.id, (SELECT employer.fullname FROM employer WHERE advert.empid=employer.id) as emp_name, (CASE WHEN advert.status1 = 'A' THEN advert.postname1 ELSE advert.postname2 END) as postname, (SELECT cd.name FROM catalog_names cd WHERE advert.industry=cd.catalog_id AND cd.language_id=1) AS f1002, (CASE WHEN status1='A' THEN advert.obligations1 ELSE advert.obligations2 END) as obligations1 FROM good_adv as advert 2004-04-14 12:24:54 [74021] ERROR: cache lookup of relation 165058647 failed 2004-04-14 12:24:54 [74021] LOG: query: SELECT * FROM tmp_table1 2004-04-14 12:24:54 [74021] ERROR: Relation "tmp_table1" does not exist 2004-04-14 12:24:54 [74021] LOG: query: SELECT * FROM tmp_table1 ORDER BY id desc LIMIT 100 2004-04-14 12:24:54 [74021] ERROR: Relation "tmp_table1" does not exist Does anybody have any idea about what can cause such kind of problems and how to overcome it ? Thanks a lot.
On Wednesday 14 April 2004 10:35, Juris Krumins wrote: > 2004-04-14 12:24:54 [74021] LOG: query: SELECT d.config_id, d.text1 as > text, d.field_name, > 2004-04-14 12:24:54 [74021] LOG: duration: 0.005295 sec > 2004-04-14 12:24:54 [74021] LOG: query: CREATE TEMP TABLE tmp_table1 AS > SELECT advert.id, (SELECT employer.fullname FROM employer WHERE > advert.empid=employer.id) as emp_name, (CASE WHEN advert.status1 = 'A' > THEN advert.postname1 ELSE advert.postname2 END) as postname, (SELECT > cd.name FROM catalog_names cd WHERE advert.industry=cd.catalog_id AND > cd.language_id=1) AS f1002, (CASE WHEN status1='A' THEN > advert.obligations1 ELSE advert.obligations2 END) as obligations1 FROM > good_adv as advert > 2004-04-14 12:24:54 [74021] ERROR: cache lookup of relation 165058647 > failed The error is complaining about a relation that used to have an OID of 165058647. This is almost certainly a temporary table. Are there any plpgsql functions referring to a temporary table, or do you create, drop, recreate tmp_table1 within the same connection? -- Richard Huxton Archonet Ltd
On Wed, 14 Apr 2004, Richard Huxton wrote: > On Wednesday 14 April 2004 10:35, Juris Krumins wrote: > > > 2004-04-14 12:24:54 [74021] LOG: query: SELECT d.config_id, d.text1 as > > text, d.field_name, > > 2004-04-14 12:24:54 [74021] LOG: duration: 0.005295 sec > > 2004-04-14 12:24:54 [74021] LOG: query: CREATE TEMP TABLE tmp_table1 AS > > SELECT advert.id, (SELECT employer.fullname FROM employer WHERE > > advert.empid=employer.id) as emp_name, (CASE WHEN advert.status1 = 'A' > > THEN advert.postname1 ELSE advert.postname2 END) as postname, (SELECT > > cd.name FROM catalog_names cd WHERE advert.industry=cd.catalog_id AND > > cd.language_id=1) AS f1002, (CASE WHEN status1='A' THEN > > advert.obligations1 ELSE advert.obligations2 END) as obligations1 FROM > > good_adv as advert > > 2004-04-14 12:24:54 [74021] ERROR: cache lookup of relation 165058647 > > failed > > The error is complaining about a relation that used to have an OID of > 165058647. This is almost certainly a temporary table. Are there any plpgsql > functions referring to a temporary table, or do you create, drop, recreate > tmp_table1 within the same connection? > > -- > Richard Huxton > Archonet Ltd > There is actually no creation, droping, recreation of tmp_table1 within the same connection. The only thing is that good_adv is not actually a table, but a view. Can it be reason for this problem ? The second thing is that queries I'v posted before going actually the same way, first of all create temporary table, then making couple select queries to that temporary table and that's all. The problem is that i use such method all over my web site, that's why create temp table tmp_table1 ... are quite large. Coulde it be problem ?
Richard Huxton <dev@archonet.com> writes:
> The error is complaining about a relation that used to have an OID of
> 165058647. This is almost certainly a temporary table. Are there any plpgsql
> functions referring to a temporary table, or do you create, drop, recreate
> tmp_table1 within the same connection?
The trace suggests this is happening immediately after connect, so the
last part doesn't sound right. It could be a plpgsql function problem,
though the query doesn't look like it invokes any functions.
What I was wondering about was dangling references within a view, that
is a view referring to a table that no longer exists. (That shouldn't
happen anymore in 7.3 and later, but if this is a pre-7.3 system then
it's possible.) Does this query make use of any views, and if so do the
views change from time to time?
regards, tom lane
On Wed, 14 Apr 2004, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > The error is complaining about a relation that used to have an OID of > > 165058647. This is almost certainly a temporary table. Are there any plpgsql > > functions referring to a temporary table, or do you create, drop, recreate > > tmp_table1 within the same connection? > > The trace suggests this is happening immediately after connect, so the > last part doesn't sound right. It could be a plpgsql function problem, > though the query doesn't look like it invokes any functions. > > What I was wondering about was dangling references within a view, that > is a view referring to a table that no longer exists. (That shouldn't > happen anymore in 7.3 and later, but if this is a pre-7.3 system then > it's possible.) Does this query make use of any views, and if so do the > views change from time to time? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > Sorry i didn't mention my version. It is PostgreSQL 7.3.4. As I metioned there is no function invocted from that query. The view I use to make temporary table was made by query: SELECT advert.id, advert.status1, advert.empid, advert.email, advert.postname1, advert.postname2, advert.industry, advert.obligations1, advert.obligations2, advert.chk_edu, advert.chk_lang, advert.chk_compskills, advert.chk_work, advert.chk_travel , advert.chk_drivlic, advert.chk_auto, advert.chk_worktime, advert.chk_workarea, advert.edu_deg, advert.travel, advert.lica, advert. licb, advert.licc, advert.licd, advert.lice, advert.auto, advert.aquirement1, advert.aquirement2, advert.offer1, advert.offer2, adve rt.fut_all_time, advert.salary1, advert.address1, advert."location", advert.work_time1, advert.work_time2, advert.person1, advert.pe rson2, advert.personpost1, advert.personpost2, advert.phone, advert.fax, advert.web, advert.address2, advert.salary2, advert.time_cr eate, advert.enddate, advert.status2, advert.send_email FROM advert, employer WHERE (((((advert.empid = employer.id) AND (employer.s tatus <> 'B'::bpchar)) AND (employer.act_lidz >= date("timestamp"('today'::text)))) AND (employer.act_no <= date("timestamp"('today' ::text)))) AND ((advert.enddate >= date("timestamp"('today'::text))) AND ((advert.status1 = 'A'::bpchar) OR (advert.status2 = 'A'::b pchar)))); So there is no mucj information you can get out of this, but the only thing is true, that information in view change from time to time. I'v checked everything conected with nonexistent tables and things like that. Everything looks fine. Diging through the source coude reveals me that: src/backend/catalog/dependency.c: elog(ERROR, "cache lookup of relation %u failed", relid); src/backend/catalog/heap.c: elog(ERROR, "cache lookup of relation %u failed", src/backend/catalog/pg_constraint.c: elog(ERROR, "cache lookup of relation %u failed",); src/backend/utils/adt/ruleutils.c: elog(ERROR, "cache lookup of relation %u failed", relid); So bacicaly we have only 4 places, which cane generate errors like that, and all this places refer to HeapTupleIsValid function.
Juris Krumins <juriskr@komin.lv> writes:
> So bacicaly we have only 4 places, which cane generate errors like that,
> and all this places refer to HeapTupleIsValid function.
Hmm. What's even more interesting is that none of them are in places
that I would expect to be called during a CREATE TABLE AS operation.
Can you dig into it a bit more and find out which one is failing?
A stack trace back from the error would be useful too.
regards, tom lane
I'v find out that this error occurs in: dependency.c file 2004-04-26 11:09:34 ERROR: dependency.c 1621: cache lookup of relation 149064743 failed 2004-04-26 11:09:34 ERROR: Relation "tmp_table1" does not exist 2004-04-26 11:09:34 ERROR: Relation "tmp_table1" does not exist in getRelationDescription(StringInfo buffer, Oid relid) function. Any ideas what can cause this errors. On Wed, 14 Apr 2004, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > The error is complaining about a relation that used to have an OID of > > 165058647. This is almost certainly a temporary table. Are there any plpgsql > > functions referring to a temporary table, or do you create, drop, recreate > > tmp_table1 within the same connection? > > The trace suggests this is happening immediately after connect, so the > last part doesn't sound right. It could be a plpgsql function problem, > though the query doesn't look like it invokes any functions. > > What I was wondering about was dangling references within a view, that > is a view referring to a table that no longer exists. (That shouldn't > happen anymore in 7.3 and later, but if this is a pre-7.3 system then > it's possible.) Does this query make use of any views, and if so do the > views change from time to time? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >