473,737 Members | 7,907 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Again about temporary table

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 HeapTupleIsVali d() function return code.
If it's false then error happend.

In
1) src/backend/utils/adt/ruleutils.c : 3125 HeapTupleIsVali d() function is
used in generate_relati on_name(Oid relid), which is as I understand
resposible to convert relid identificator into relation name
2) src/backend/catalog/dependency.c : 1621 HeapTupleIsVali d() function is
used in getRelationDesc ription(StringI nfo buffer, Oid relid) function, which
is getting relation description while specify relid.
3) src/backend/catalog/heap.c : 1663 HeapTupleIsVali d() function is used in
SetRelationNumC hecks(Relation rel, int numchecks) function. I don't realy
understand the meaning of that function
4) src/backend/catalog/pg_constraint.c : 445 HeapTupleIsVali d() function is
used in RemoveConstrain tById(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_cre ate, (SELECT
employer.fullna me FROM employer WHERE advert.empid=em ployer.id) as emp_name,
(CASE WHEN advert.status1 = 'A' THEN advert.postname 1 ELSE advert.postname 2
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
1 4575
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_cre ate, (SELECT
employer.fullna me FROM employer WHERE advert.empid=em ployer.id) as
emp_name, (CASE WHEN advert.status1 = 'A' THEN advert.postname 1 ELSE
advert.postname 2 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
13734
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB object, and I am able to get the binary input stream from this blob. However, when I invoke InputStream.read(byte) on this input stream, I get the following exception: java.io.IOException: ORA-01410: invalid ROWID
5
33654
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. Performance: 4 seconds, the system is doing a full-table scan of the second table, and the Explain Plan output
0
8416
by: Didier ROS | last post by:
Hi, I am a newbie I want to create a temporary table and I get the following error message : mysql> CREATE TEMPORARY TABLE tempemp AS SELECT * FROM emp; ERROR 1044: Access denied for user: '@localhost' to database 'test1' Any help would be appreciated
4
1648
by: MAB | last post by:
I want the sum of the last payments (amount) for all customers. The last payment is with one with most recent date. And if there are more than one payment on the most recent date then the one with the higher paymentid is the last payment. for example in the given data the insert statement that starts with capital I is the last payment of that customer. The correct answer should be 2100 as given below. both queries by Erland and Anith give...
4
49441
by: gonzal | last post by:
Hi Dose any body know why a temporary table gets deleted after querying it the first time (using SELECT INTO)? When I run the code bellow I'm getting an error message when open the temp table for the second time. Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E37) Invalid object name '#testtable'.
11
16298
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the equivalent to the SQL 'with' using TSQL? If there is not one, what is the TSQL solution to creating a temporary table that is associated with an SQL statement? Examples would be appreciated. Thank you!!
0
2807
by: nedbollard | last post by:
Hi, Having checked out a declared (rather than created) temporary table successfully in SPUFI, I have hit a problem in running the same SQL in the cobol prog I am ameding to use it. I have: existing SQL declare temporary table insert in temporary table
4
12049
by: prasad | last post by:
I am getting sql error during binding a program which access a temporary table. The temporary table declaration and access methods are given below. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEM88 LIKE SYSIBM.SYSDUMMY1 END-EXEC. EXEC SQL INSERT INTO TEM88 SELECT * FROM SYSIBM.SYSDUMMY1 END-EXEC.
7
3278
by: Larry | last post by:
Hi, I have unbelievable problems just to save a record! I make an input to a record in a subform, which has a temporary table as its recordsource. When I am done, and want to save the record, I want to bring the data from the temporary table over to the regular table. But the data isn't in the temporary table yet, it is still
5
2996
by: Adam W. Saxton | last post by:
We have a few existing stored procedures which create a Global Temporary Table (##), do some work on the table and then delete the table. The issue we have is that if our Server application is using these is running and then our Web application fires off, the Temp table is getting deleted. My thought was to change the Global Temporary Table to a local temporary table (# instead of ##) to minimize the amount of change to these...
0
8787
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9334
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6750
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6053
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4569
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4824
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3279
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2744
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2193
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.