From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joseph Barillari <jbarilla@princeton.edu>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Temporary table weirdness
Date: Mon, 10 Jun 2002 15:50:46 -0400 (EDT)
Message-ID: <200206101950.g5AJolL10295@candle.pha.pa.us> (raw)
In-Reply-To: <23409.1020089557@sss.pgh.pa.us>
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, Pennsylvania 19026
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, tgl@sss.pgh.pa.us, jbarilla@princeton.edu
Subject: Re: Temporary table weirdness
In-Reply-To: <200206101950.g5AJolL10295@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox