Re: Temporary table weirdness

FAQ updated with this, item 4.26.

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

Tom Lane wrote:
> Joseph Barillari <jbarilla(at)princeton(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026