cache lookup of relation 165058647 failed

[Juris Krumins]

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

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

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

[Juris Krumins]

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 ?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Richard Huxton <de*@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

[Juris Krumins]

On Wed, 14 Apr 2004, Tom Lane wrote:

Richard Huxton <de*@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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Juris Krumins <ju*****@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

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

http://archives.postgresql.org

[Juris Krumins]

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 <de*@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

---------------------------(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

[Juris Krumins]

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 <de*@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

---------------------------(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