Received: from smtp018.mail.yahoo.com (smtp018.mail.yahoo.com [216.136.174.115]) by postgresql.org (Postfix) with SMTP id 1397E475452 for ; Thu, 18 Apr 2002 14:50:37 -0400 (EDT) Received: from psc.progress.com (HELO saturn.janwieck.net) (janwieck@192.233.92.200 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Apr 2002 18:50:37 -0000 Received: (from wieck@localhost) by saturn.janwieck.net (8.11.2/8.11.2) id g3IInRM14849; Thu, 18 Apr 2002 14:49:27 -0400 From: Jan Wieck Message-Id: <200204181849.g3IInRM14849@saturn.janwieck.net> Subject: Re: call the same pl/pgsql procedure twice in the same connection In-Reply-To: <200204181722.g3IHMEc21830@candle.pha.pa.us> from Bruce Momjian at "Apr 18, 2002 01:22:14 pm" To: Bruce Momjian Date: Thu, 18 Apr 2002 14:49:26 -0400 (EDT) Cc: Jan Wieck , jack , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/235 X-Sequence-Number: 7499 Bruce Momjian wrote: > Jan Wieck wrote: > > Bruce Momjian wrote: > > > > > > Jan, instead of doing cache invalidation to fix temporary tables, can we > > > disable cached plans for functions that use temporary tables? > > > > I was thinking of a different approach. Enhancing the SPI > > manager to detect if a plan uses temporary objects and to > > remember the original querystring in the SPI_plan. Having > > callbacks when temp object beeing destroyed into the SPI > > manager, causing it to reparse and plan on the next call to > > SPI_execp() would do it for everything that uses SPI. > > I was merely proposing that preventing caching of functions ueing temp > tables may be easier than trying to invalidation them on temp table > destruction. It's neat to say "preventing caching of functions using ...", now tell in detail how you detect that a function "is" using a temp table? No, I don't mean how "you" can detect it, how can the PL/pgSQL parser or executor detect it. And when do you detect it? Remember that PL/pgSQL has delayed SPI preparation? Second, it doesn't really look smart to me to prevent saving of all query plans just because one of them uses a temp table. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #