Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 3C9B2476A5B for ; Mon, 8 Jul 2002 18:27:14 -0400 (EDT) Mailbox-Line: From pgman@candle.pha.pa.us Mon Jul 8 18:27:14 2002 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 C0C2E476D0A for ; Mon, 8 Jul 2002 14:40:45 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g68IedD14328; Mon, 8 Jul 2002 14:40:39 -0400 (EDT) From: Bruce Momjian Message-Id: <200207081840.g68IedD14328@candle.pha.pa.us> Subject: Re: In-Reply-To: <000201c2265f$793f85f0$1400a8c0@jac> To: jack Date: Mon, 8 Jul 2002 14:40:39 -0400 (EDT) Cc: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Spam-Status: No, hits=-3.4 required=5.0 tests=IN_REP_TO version=2.30 X-Spam-Level: X-Archive-Number: 200207/102 X-Sequence-Number: 8481 jack wrote: > Hi, > Regarding temp tables in the same connection session. If there is SQL > procedure creating temp tables, process temp tables and drop all the temp > tables finally. This SQL procedure can't run twice in the same session. > Because all the temp tables are referred to the first physical temp tables. > I posted this question couple months ago and the final reply is that in the > future version, all the sql procedures, which use temp tables, will be > forced to re-compile though it's been used in the session. > > Now, my question is, if it has been done, do I still need to drop all the > temp tables before I re-run the same SQL procedure in the same session? Actually, we recomment using EXECUTE for any temp table access from pl/pgsql stored procedures. We even have an FAQ item now:

4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?

PL/PgSQL caches function contents, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time. -- 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