I was able to reproduce the problem complained of here
http://archives.postgresql.org/pgsql-bugs/2007-11/msg00322.php
with this function:
create or replace function foo() returns int as $$
declare r int;
begin
drop table if exists temptable cascade;
create temp table temptable as select * from generate_series(1,4) f1;
create temp view vv as select * from temptable;
-- perform 2+2;
for r in select * from vv loop
raise notice '%', r;
end loop;
return 0;
end$$ language plpgsql;
regression=# select foo();
NOTICE: table "temptable" does not exist, skipping
CONTEXT: SQL statement "drop table if exists temptable cascade"
PL/pgSQL function "foo" line 3 at SQL statement
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
foo
-----
0
(1 row)
regression=# select foo();
NOTICE: drop cascades to rule _RETURN on view vv
CONTEXT: SQL statement "drop table if exists temptable cascade"
PL/pgSQL function "foo" line 3 at SQL statement
NOTICE: drop cascades to view vv
CONTEXT: SQL statement "drop table if exists temptable cascade"
PL/pgSQL function "foo" line 3 at SQL statement
ERROR: could not open relation 1663/121218/145930: No such file or directory
CONTEXT: PL/pgSQL function "foo" line 7 at FOR over SELECT rows
The problem goes away if there's any SQL action between the CREATE VIEW
and the FOR command, eg if you uncomment the PERFORM shown above.
What is happening is that the last step of CREATE VIEW, namely
DefineViewRules(), isn't visible at the time we try to re-validate
the cached plan for the FOR command, because no CommandCounterIncrement
has happened between. So the plan gets regenerated as a simple seqscan
of the view relation, which of course fails for lack of any underlying
storage.
This is not CREATE VIEW's fault, since no utility command expects that
it should do a final CommandCounterIncrement (henceforth CCI)
internally; CCI calls are supposed to be done between commands by system
control logic when needed. Moreover inserting a CCI at the end of
DefineView would only fix this particular manifestation, and not other
cases of DDL immediately before re-use of a plan.
One fairly simple answer is to insert a CCI call at the start of
RevalidateCachedPlan. I dislike that solution, at least by itself,
on two grounds:
* A patch of that form would approximately double the number of CCI
calls involved in executing a plpgsql function; which quite aside from
any performance cost would halve the distance to the
2^32-commands-per-transaction horizon. We've already heard from people
who ran into that limit, so I don't want to bring it closer.
* This would result in executing CCI calls even during stable/immutable
PL functions. I'm not sure that would have any bad semantic side-effects,
but I'm not convinced it wouldn't, either. And it also gives back
whatever command count limit savings we bought when we fixed things
so that stable/immutable functions don't call CCI.
I've also thought about rearranging the current conventions for where to
call CCI. This particular form of the problem would go away if SPI
command execution did CCI after, instead of before, each non-read-only
command. Or perhaps safer, before each such command and after the last
one. I'm a bit worried though about whether that leaves any code paths
in which we're still missing a needed CCI.
An idea we could use in combination with either of the above is to make
command ID assignment "lazy" in a similar sense to what we did for XID
assignment recently; that is, fix things so that CCI is a no-op if
no database change actually happened since the last one. This would
greatly reduce the command-limit disadvantages of having a scheme that
executes "unnecessary" CCI's.
Comments, better ideas?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
