Thread: Function caches wrong OID of temporary table?

Function caches wrong OID of temporary table?

From
Phil Endecott
Date:
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.

I can supply a better test case if that would help.

Regards,

--Phil.



Re: Function caches wrong OID of temporary table?

From
Oliver Elphick
Date:
On Wed, 2004-09-08 at 12:37, 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:

...
> 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.

That is correct.  You need to EXECUTE the command instead, so that it is
planned afresh each time it is used.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Put on the whole armor of God, that ye may be able to
      stand against the wiles of the devil."
                                        Ephesians 6:11


Re: Function caches wrong OID of temporary table?

From
John Sidney-Woollett
Date:

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