Обсуждение: BUG #2440: TEMP TABLES in Postgres 8.1.3

The following bug has been logged online:

Bug reference:      2440
Logged by:          Peter Exner
Email address:      exner@his.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 für 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

"Peter Exner" <exner@his.de> writes:
> 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;

That's not standard SQL.  You can fix it to work under 8.1 the same as
it did before by adding "FROM tmp_table", or if you are stubborn you can
turn add_missing_from on ...

            regards, tom lane