Skip site navigation (1) Skip section navigation (2)

BUG #2440: TEMP TABLES in Postgres 8.1.3

From: "Peter Exner" <exner(at)his(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2440: TEMP TABLES in Postgres 8.1.3
Date: 2006-05-16 08:04:22
Message-ID: 200605160804.k4G84M0f070890@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2440
Logged by:          Peter Exner
Email address:      exner(at)his(dot)de
PostgreSQL version: 8.0.3 / 8.1.3
Operating system:   SuSe Linux
Description:        TEMP TABLES in Postgres 8.1.3
Details: 

Hello,

there is a problem with my "tmp_table". My C++ program creates it with
"SELECT ... INTO tmp_table", then uses it and finally drops it. With
Postgres 7.x and Postgres 8.0.3 everything works fine.
Now I switched to Postgres 8.1.3, and an error occurs, as shown below.

Is it a bug, or what can I do?

Thanks for answers!
Peter


correct (with Postgres 8.0.3):

1. create "tmp_table" 

SQL-Statement: 
SELECT MAX(table1_id) AS max_id, SUM(value1) AS value1_sum INTO tmp_table
FROM table1 GROUP BY value2;

2. do something with "tmp_table" 

SQL-Statement: 
UPDATE table1 SET value1 = value1 - (SELECT value1_sum FROM tmp_table WHERE
table1.table1_id = tmp_table.max_id) WHERE split_anteil < 10000 AND afa_ende
= 24061 AND table1.table1_id = tmp_table.max_id;

3. drop  "tmp_table" 

SQL-Statement: 
DROP TABLE tmp_table;

everything o.k.!
	
 
false (with Postgres 8.1.3), same SQL-statements as above

1. create "tmp_table" 

SQL-Statement: 
SELECT MAX(table1_id) AS max_id, SUM(value1) AS value1_sum INTO tmp_table
FROM table1 GROUP BY value2;

2. do something with "tmp_table"

SQL-Statement: 
UPDATE table1 SET value1 = value1 - (SELECT value1_sum FROM tmp_table WHERE
table1.table1_id = tmp_table.max_id) WHERE split_anteil < 10000 AND afa_ende
= 24061 AND table1.table1_id = tmp_table.max_id;

doesn't work:

ODBC-Error - SQLExecDirect/ExecuteSQL:
SQLSTATE = S1000
Native Error = 7
Error Message: FEHLER:  fehlender Eintrag in FROM-Klausel fr Tabelle
tmp_table



3. drop "tmp_table" 

SQL-Statement: 
DROP TABLE tmp_table;

doesn't work:

ODBC-Error - SQLExecDirect/ExecuteSQL:
SQLSTATE = S1000
Native Error = 7
Error Message: FEHLER:  Tabelle tmp_table existiert nicht

Responses

pgsql-bugs by date

Next:From: Martijn van OosterhoutDate: 2006-05-16 08:47:12
Subject: Re: [BUGS] BUG #2429: Explain does not report object's schema
Previous:From: Ruslan A DautkhanovDate: 2006-05-16 06:19:51
Subject: Re: postgresql-[any version] from FreeBSD ports - startup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group