By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Again about temporary table

P: n/a
Couple a weeks ago (19.08.03 Subject: Temporaty tables) I've posted message
with question about errors I'm getting while using create temporaty table
command.
So I'm start digging in src code as Tom Lane did and found about 4 places
where such kind pf errors could happend

PostgreSQL 7.3.3

src/backend/catalog/dependency.c : 1621
src/backend/catalog/heap.c : 1663
src/backend/catalog/pg_constraint.c : 445
src/backend/utils/adt/ruleutils.c : 3125

As I see all this errors depend on HeapTupleIsValid() function return code.
If it's false then error happend.

In
1) src/backend/utils/adt/ruleutils.c : 3125 HeapTupleIsValid() function is
used in generate_relation_name(Oid relid), which is as I understand
resposible to convert relid identificator into relation name
2) src/backend/catalog/dependency.c : 1621 HeapTupleIsValid() function is
used in getRelationDescription(StringInfo buffer, Oid relid) function, which
is getting relation description while specify relid.
3) src/backend/catalog/heap.c : 1663 HeapTupleIsValid() function is used in
SetRelationNumChecks(Relation rel, int numchecks) function. I don't realy
understand the meaning of that function
4) src/backend/catalog/pg_constraint.c : 445 HeapTupleIsValid() function is
used in RemoveConstraintById(Oid conId) which is responsible for delete a
single constraint record.

So any ideas why I'm getting errors like:
Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: cache lookup of
relation 149064743 failed . in table.php on line 169
The query on line 169 is :

CREATE TEMP TABLE tmp_table1 AS SELECT advert.time_create, (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, advert.enddate, advert.id as id FROM good_adv as advert

And right ahead

Warning: pg_exec()[function.pg-exec]:Query failed: ERROR: Relation
"tmp_table1" does not exist.

On query like :

SELECT * FROM tmp_table1

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

http://archives.postgresql.org

Nov 11 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Wednesday 27 August 2003 08:39, Juris Krumins wrote:
Couple a weeks ago (19.08.03 Subject: Temporaty tables) I've posted message
with question about errors I'm getting while using create temporaty table
command.
So I'm start digging in src code as Tom Lane did and found about 4 places
where such kind pf errors could happend So any ideas why I'm getting errors like:
Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: cache lookup of
relation 149064743 failed . in table.php on line 169
The query on line 169 is :

CREATE TEMP TABLE tmp_table1 AS SELECT advert.time_create, (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, advert.enddate, advert.id as id FROM
good_adv as advert

And right ahead

Warning: pg_exec()[function.pg-exec]:Query failed: ERROR: Relation
"tmp_table1" does not exist.

On query like :

SELECT * FROM tmp_table1


I can't find your previous post at the moment, but I sort of remember it.
It looks like the root of the problem is the failure to create the temp table
on line 169 - that would explain why it's not visible later on.

A few things it might be worth trying with that query:
1. select oid,* from pg_class where oid=149064743;
This should show you which relation it's failing to find.
If the number keeps failing, it's presumably failing to find the temp table
(which is odd)
2. Create the temp table then INSERT INTO tmp_table1 SELECT...
3. Does it work if you use a permanent instead of temp table?
4. Does it work as a straightforward SELECT?
5. What happens if you don't alias good_adv?

I'm shooting in the dark here by the way - hoping one of these will point to
what's happening.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.