Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 057E8476123 for ; Mon, 10 Jun 2002 16:11:53 -0400 (EDT) Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 8751447619E for ; Mon, 10 Jun 2002 15:50:54 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g5AJolL10295; Mon, 10 Jun 2002 15:50:47 -0400 (EDT) From: Bruce Momjian Message-Id: <200206101950.g5AJolL10295@candle.pha.pa.us> Subject: Re: Temporary table weirdness In-Reply-To: <23409.1020089557@sss.pgh.pa.us> To: Tom Lane Date: Mon, 10 Jun 2002 15:50:46 -0400 (EDT) Cc: Joseph Barillari , pgsql-sql X-Mailer: ELM [version 2.4ME+ PL97 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200206/118 X-Sequence-Number: 8083 FAQ updated with this, item 4.26. --------------------------------------------------------------------------- Tom Lane wrote: > Joseph Barillari 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