In bug #3662
http://archives.postgresql.org/pgsql-bugs/2007-10/msg00047.php
we see that it doesn't work to do nextval('seq') on a temp sequence
in a plpgsql function except via EXECUTE, because the sequence OID gets
embedded into the cached plan, same as any other temp table. This is to
be expected in existing releases, but I notice it still doesn't work in
CVS HEAD, which is a tad annoying given the existence of the plan inval
mechanism. The reason is that plancache.c only searches the plan's
RangeTable for references to a just-invalidated relation; and the
argument of nextval() isn't mentioned in the RangeTable (it is in fact
just a Const of type regclass).
There doesn't seem to be any very nice way to fix this. There is
not any existing support mechanism (comparable to query_tree_walker)
for scanning whole plan trees, which means that searching a cached plan
for regclass Consts is going to involve a chunk of new code no matter
how we approach it. We might want to do that someday --- in particular,
if we ever try to extend the plan inval mechanism to react to
redefinitions of non-table objects, we'd likely need some such thing
anyway. I'm disinclined to try to do it for 8.3 though. The use-case
for temp sequences seems a bit narrow and there are several workarounds
(see followups to bug report), so I'm feeling this is a
fix-some-other-day kind of issue.
Comments?
regards, tom lane