Обсуждение: Temporary tables inside functions problem

I have a function (func2) which creates 2 temporary tables when it
starts and drops them before it returns.
Another function (func1) calls func2.

 From psql:
Calling func2 directly works fine.
Calling func1 which in turn calls func2 works fine.

 From ColdFusion MX:
If I call func2 directly from within ColdFusion it works fine.
If I call func1 which in turn calls func2 from within ColdFusion, I get
the following error:

ERROR:  pg_class_aclcheck: relation 8392689 not found
WARNING:  Error occurred while executing PL/pgSQL function func2

It seems that the error occurs not when creating the temporary tables,
but rather when attempting to insert into them.

I am sure there is a logical explanation, although I can find it.  Any
idea what is the problem and how I can fix it?

Thanks,
Avi
--
Avi Schwartz
avi@CFFtechnologies.com

I figured out the answer last night after sending the email but I have
to wonder whether it would make more sense for plpgsql to treat
temporary tables differently then other object due to their temporary
nature and keep any code referencing these tables dynamic.

Another related question.   To do something like the following static
select

select into
   v_price_selected_min, v_price_selected_max
   min(conf_price), max(conf_price)
from tmp_price;

I had to use the following dynamic code:

declare r record;
...
for r in execute ''select min(conf_price) as price_selected_min,
max(conf_price) as price_selected_max from tmp_price'' loop
   v_price_selected_min := r.price_selected_min;
   v_price_selected_max := r.price_selected_max;
end loop;

Is there a better way to do that when I know that the result is a
singleton select (i.e. only one row is returned)?  Doing the whole loop
thing is way too ugly :-)

Avi

On Sunday, Jun 8, 2003, at 14:54 America/Chicago, Ian Barwick wrote:

> On Sunday 08 June 2003 01:50, Avi Schwartz wrote:
> (...)
>> It seems that the error occurs not when creating the temporary tables,
>> but rather when attempting to insert into them.
>>
>> I am sure there is a logical explanation, although I can find it.  Any
>> idea what is the problem and how I can fix it?
>
> Possibly this item from the FAQ might help:
> http://www.postgresql.org/docs/faqs/FAQ.html#4.26
>
> 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

On Sunday 08 June 2003 01:50, Avi Schwartz wrote:
(...)
> It seems that the error occurs not when creating the temporary tables,
> but rather when attempting to insert into them.
>
> I am sure there is a logical explanation, although I can find it.  Any
> idea what is the problem and how I can fix it?

Possibly this item from the FAQ might help:
http://www.postgresql.org/docs/faqs/FAQ.html#4.26

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.


Ian Barwick
barwick@gmx.net

On Sat, 7 Jun 2003, Avi Schwartz wrote:

> I have a function (func2) which creates 2 temporary tables when it
> starts and drops them before it returns.
> Another function (func1) calls func2.
>
>  From psql:
> Calling func2 directly works fine.
> Calling func1 which in turn calls func2 works fine.
>
>  From ColdFusion MX:
> If I call func2 directly from within ColdFusion it works fine.
> If I call func1 which in turn calls func2 from within ColdFusion, I get
> the following error:
>
> ERROR:  pg_class_aclcheck: relation 8392689 not found
> WARNING:  Error occurred while executing PL/pgSQL function func2

If you want to work with temporary tables from inside plpgsql or with
other tables that you're creating and dropping, you'll need to use EXECUTE
for the queries that use the table so that it won't cache the query plan.

> I have a function (func2) which creates 2 temporary tables when it
> starts and drops them before it returns.
> Another function (func1) calls func2.
>
> From psql:
> Calling func2 directly works fine.
> Calling func1 which in turn calls func2 works fine.
>
> From ColdFusion MX:
> If I call func2 directly from within ColdFusion it works fine.
> If I call func1 which in turn calls func2 from within ColdFusion, I get
> the following error:
>
> ERROR:  pg_class_aclcheck: relation 8392689 not found
> WARNING:  Error occurred while executing PL/pgSQL function func2
>
> It seems that the error occurs not when creating the temporary tables,
> but rather when attempting to insert into them.
>
> I am sure there is a logical explanation, although I can find it.  Any
> idea what is the problem and how I can fix it?

I just ran into this not that long ago.  Instead of using ON COMMIT
DROP, use ON COMMIT DELETE ROWS that way the oid for the temp table
can be reused inside of pl/pgsql's cache.  Tom pointed out that it
should be possible to hook up the new dependency system to the cache
and have the dependency delete expired items from the cache, but I
don't think anyone has the desire to add that at the moment.  -sc

--
Sean Chittenden