Phil Endecott wrote:
> Dear PostgreSQL experts,
>
> I have encountered a problem with temporary tables inside plpgsql
> functions. I suspect that this is a known issue; if someone could
> confirm and suggest a workaround I'd be grateful.
>
> My function creates a couple of temporary tables, uses them, and drops
> them before returning:
>
>
> create temporary table s as select 123 as id;
> create temporary table t ( id integer );
> ....
> insert into t (select id from s);
> ....
> drop table s;
> drop table t;
> return;
>
>
> When I run this the first time it works as expected. When I run it a
> second time I get this message:
>
> ERROR: relation with OID 590209 does not exist
> CONTEXT: PL/pgSQL function "f" line 18 at SQL statement
>
> (Line 18 is the insert-select statement.)
>
> I imagine that it has cached that one of the tables is object 590209,
> but has not noticed that the table has been dropped and recreated before
> the second invokation of the function.
Your analysis is correct - Using execute is the work workaround.
While I'm all for function caching, this *feature* in the scenario above
is verging on a bug as far as I am concerned. I think that this item
should move from a "nice to have / to do" item to the bug list.
Other database systems that I have used are able to detect when a
procedure/function need recompiling and do it on the fly. If postgres
implemented this it would have another benefit. You could create objects
that reference other objects that don't yet exist (or are not yet
created) for example when applying a schema build script.
The first time the object is "used/called" it is compiled and all the
dependencies are resolved then (this is what Oracle is able to do).
Not forcing SQL developers to use DB-specific workarounds would be a
benefit too! :)
John Sidney-Woollett