Re: faq 4.20: pl/pgsql temporary tables create/drop

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(at)postgresql(dot)org)
>

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