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
