Function caches wrong OID of temporary table?

[Phil Endecott]

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Oliver Elphick]

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 ol**@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)