Thread: Function caches wrong OID of temporary table?
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.
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
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