To: Joseph Barillari <jbarilla@princeton.edu>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Temporary table weirdness 
In-reply-to: <m3elgzni0f.fsf@washer.barillari.org> 
References: <m3elgzni0f.fsf@washer.barillari.org>
Comments: In-reply-to Joseph Barillari <jbarilla@princeton.edu>
	message dated "Mon, 29 Apr 2002 02:20:00 -0400"
Date: Mon, 29 Apr 2002 10:12:37 -0400
Message-ID: <23409.1020089557@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Joseph Barillari <jbarilla@princeton.edu> writes:
> I have a function that creates and destroys temporary tables in the
> course of its work, and have run into trouble if the function
> is executed more than once during a database session. A small
> proof-of-concept is attached below:

> cal=3D> create or replace function frob() returns integer as 'begin create =
> temporary table foo(bar INT); insert into foo (bar) values (1); drop table =
> foo; return 1; end;' language 'plpgsql';

This should be in the FAQ :-(.  Since plpgsql caches query plans, it
will fall over the second time through this code, because the temp table
is no longer the same table (same OID) as it was the first time ---
but the cached plan for the INSERT still has the old OID.

There's a TODO item for plpgsql to detect changes of schema that affect
its cached plans, and drop the cache; but it's not exactly trivial to
do.

In the meantime, you need to use EXECUTE to defeat the plan caching for
every plpgsql query that touches the temp table.  Another answer is to
arrange to create the temp table only once per session, but that's
harder.

			regards, tom lane