Re: Temporary table weirdness

FAQ updated with this, item 4.26.

---------------------------------------------------------------------------

Tom Lane wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026