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

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема Re: faq 4.20: pl/pgsql temporary tables create/drop
Дата
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3412A7610@Herge.rcsinc.local
обсуждение исходный текст
Ответ на faq 4.20: pl/pgsql temporary tables create/drop  ("Merlin Moncure" <merlin.moncure@rcsonline.com>)
Ответы Re: faq 4.20: pl/pgsql temporary tables create/drop  (Roman Neuhauser <neuhauser@chello.cz>)
Re: faq 4.20: pl/pgsql temporary tables create/drop  (Bruce Momjian <pgman@candle.pha.pa.us>)
Список pgsql-docs
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




В списке pgsql-docs по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: faq 4.20: pl/pgsql temporary tables create/drop
Следующее
От: Roman Neuhauser
Дата:
Сообщение: Re: faq 4.20: pl/pgsql temporary tables create/drop