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 A2FBB475966 for ; Wed, 17 Apr 2002 18:07:14 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g3HM74D09071; Wed, 17 Apr 2002 18:07:04 -0400 (EDT) From: Bruce Momjian Message-Id: <200204172207.g3HM74D09071@candle.pha.pa.us> Subject: Re: call the same pl/pgsql procedure twice in the same connection In-Reply-To: <200203252136.g2PLaEg25650@saturn.janwieck.net> To: Jan Wieck Date: Wed, 17 Apr 2002 18:07:04 -0400 (EDT) Cc: jack , pgsql-sql@postgresql.org 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: 200204/217 X-Sequence-Number: 7481 Jan, instead of doing cache invalidation to fix temporary tables, can we disable cached plans for functions that use temporary tables? --------------------------------------------------------------------------- Jan Wieck wrote: > Bruce Momjian wrote: > > jack wrote: > > > I have a function with pl/pgSQL such as... > > > CREATE FUNCTION comp_userExp (TEXT) RETURNS INTEGER AS ' > > > DECLARE > > > stUserName TEXT; > > > BEGIN > > > stUserName := upper($1); > > > > > > CREATE TEMP TABLE comuser AS > > > SELECT * FROM comt_user1 > > > WHERE userName=stUserName; > > > > > > CREATE TEMP TABLE comUser1 AS > > > SELECT a.userName FROM comt_user2 a, comuser b > > > WHERE a.userName = b.userName > > > ORDER BY b.userName; > > > > > > CREATE TEMP TABLE comUser2 AS > > > SELECT a.userName FROM comt_user3 a, comuser b > > > WHERE a.userName = b.userName > > > ORDER BY b.userName; > > > > > > DROP Table comuser,comuser1,comuser2; > > > > > > RETURN 0; > > > > > > END;' > > > LANGUAGE 'PLPGSQL'; > > > > > > This function can't run twice in the same connection session. After tracing > > > error, the reason is because 2nd and 3rd SQL refer to table comuser. It will > > > cause "can't find relation number xxxxx". I think ,after first run, the > > > procedure just use relation number to access table, while actually table was > > > dropped and create again with a different relation number on the 2nd time. > > > If I disconnect database, and re-connect again, it won't cuase any problem. > > > If don't want to disconnect and connect, is there any way to fix the > > > problem? > > > > Yes, this is coming up a lot recently, maybe an FAQ. You need to use > > EXECUTE in plpgsql so the string is reparsed every time and the proper > > oid assigned. > > This is somehow connected to the temporary view discussion, > as it needs the same detection if a query depends on > temporary objects. As soon as we have a detection mechanism > for it, I can modify PL/pgSQL not to save prepared plans for > these statements. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > -- 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