Re: 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?

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