Postgres Pro
Downloads
Home   >   mailing lists

Thread: basic temp table question

basic temp table question

From
"L. Fletcher"
Date:
Hello,
 
The first time I run a query against this function (using Npgsql):
 
declare
       r_cursor1 cursor for
 
       SELECT * from tmp_table;
 
begin
 
       CREATE TEMPORARY TABLE tmp_table
        (
              testcol integer
        ) ON COMMIT DROP;
 
       INSERT INTO tmp_table
       SELECT 0;
 
       open r_cursor1;
 
       return 0;
end;
 
I get this error:
 
ERROR: XX000: relation 1090457025 is still open
 
All subsequent times I get this error:
 
ERROR: 42P01: relation with OID 1090457025 does not exist
 
What am I doing wrong?
 
(Sorry if this is a basic question.)
 
Thanks in advance...

Re: basic temp table question

From
Tom Lane
Date:
"L. Fletcher" <lucasf@vagabond-software.com> writes:
> I get this error:
> ERROR: XX000: relation 1090457025 is still open

This is an 8.0 bug fixed in 8.0.1.

> All subsequent times I get this error:
> ERROR: 42P01: relation with OID 1090457025 does not exist=20

This is because plpgsql caches plans and therefore can't cope with
tmp_table not being the identical same table from run to run of the
function.  Sooner or later we will probably fix that, but in the
meantime consider using an ON COMMIT DELETE ROWS temp table, created
only once per connection, instead.

            regards, tom lane