X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5A7158BA27B
	for <pgsql-docs-postgresql.org@localhost.postgresql.org>;
	Tue, 15 Feb 2005 04:36:22 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 38139-05
	for <pgsql-docs-postgresql.org@localhost.postgresql.org>;
	Tue, 15 Feb 2005 04:36:12 +0000 (GMT)
Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126])
	by svr1.postgresql.org (Postfix) with ESMTP id DBAE88BA2CA
	for <pgsql-docs@postgresql.org>; Tue, 15 Feb 2005 04:36:11 +0000 (GMT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id j1F4aCj21193;
	Mon, 14 Feb 2005 23:36:12 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200502150436.j1F4aCj21193@candle.pha.pa.us>
Subject: Re: faq 4.20: pl/pgsql temporary tables create/drop
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A7610@Herge.rcsinc.local>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Date: Mon, 14 Feb 2005 23:36:12 -0500 (EST)
Cc: pgsql-docs@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL121 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200502/41
X-Sequence-Number: 2874


Yes, you are right, the question was inaccurate.  Here is the updated
text:

    <H4><A name="4.20">4.20</A>) Why do I get "missing oid" errors when
    accessing temporary tables in PL/PgSQL functions?</H4>

---------------------------------------------------------------------------

Merlin Moncure wrote:
> Bruce Momijan wrote:
> > Merlin Moncure wrote:
> > > The PostgreSQL FAQ currently suggests using dynamic SQL as a
> workaround
> > > for the table OID caching problem of temp tables in pg/pgsql
> functions.
> > > While this is ok, it fails to suggest that besides the initial
> > > create/drop statements, every statement that touches the table must
> also
> > > be dynamic.
> > Uh, the FAQ reads:
> > 
> >     <H4><A name="4.20">4.20</A>) Why can't I reliably create/drop
> >     temporary tables in PL/PgSQL functions?</H4>
> > 
> >     <P>PL/PgSQL caches function scripts, 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
> >     <SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL.
> This
> >     will cause the query to be reparsed every time.</P>
> > 
> > What should be changed?  I see it saying "function accesses a
> temporary
> > table".  The word "access" suggests all access, not just create/drop.
> 
> You are 100% correct.  But something still doesn't feel right.
> 
> Namely, the answer answers the question, "why can't I reliably access
> temporary tables in pg/pgsql functions?"  Note that a temporary table
> not created in a pg/pgsql function will still have this behavior.  So,
> really, it is the question that is misleading, not the answer.  
> 
> One possible re-phrasing would be:
> "Why do temporary tables in PL/PgSQL functions give me "missing oid"
> errors?"
> 
> I think this matches the existing answer much better.  Given further
> consideration, my previous suggestions regarding using exception handing
> to manage temporary table construction, etc. would be more appropriate
> in the proper documentation than in a FAQ.
> 
> I will say that for most cases of usage of temporary tables for storage
> from within pg/pgsql functions, using dynamic sql is probably not the
> optimial solution unless dynamic sql is more generally preferred.
> 
> Merlin
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073