Pavel pointed out here
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01233.php
that it no longer works to reference plpgsql variables in EXPLAIN
statements in plpgsql. I dug into this a bit, and the code is trying
to do it but it doesn't quite work.
The issue centers around the behavior of the ParamListInfo data
structure, which was originally intended to carry only values for a
fixed set of $1 .. $n parameters (as in PREPARE/EXECUTE for instance).
This is the structure that carries plpgsql values into a command that's
executed as a cursor. To support the recent changes in plgsql parsing,
I extended that struct to also carry parser hook functions. The idea is
that while doing parse analysis of a statement, the parser hook
functions could capture references to plpgsql variables and turn them
into Params, which would then reference the data area of the
ParamListInfo struct at runtime.
This works well enough for regular DML statements, but it falls down for
EXPLAIN which is a utility statement, because *parse analysis of utility
statements doesn't do anything*. EXPLAIN actually does the parse
analysis of its contained statement at the beginning of execution.
And that is too late, in the scenario Pavel exhibited. Why is it too
late? Because SPI_cursor_open_internal() intentionally "freezes" the
ParamListInfo struct after doing initial parsing: what it copies into
the cursor portal is just a static list of data values without the
parser hooks (see copyParamList). This is really necessary because the
execution of the portal could outlive the function that created the
cursor, so we can't safely execute its parsing hooks anymore.
So what to do about it? I can see two basic avenues towards a solution:
1. Change things so that copyParamList copies enough state into the
cursor portal so that we can still run the plpgsql parsing hooks during
cursor execution. In the worst case this would imply copying *all*
local variables and parameters of the plpgsql function into the cursor
portal, plus a lot of names, types, etc. We could perhaps optimize
things enough to only copy the values actually referenced, but it still
seems like possibly a rather nasty performance hit. And it'd affect not
only explicit cursors, but every plpgsql for-over-rows construct,
because those are cursors internally.
2. Redesign EXPLAIN so that it parses the contained query in the initial
parsing step; it wouldn't be a simple utility command anymore but a
hybrid much like DECLARE CURSOR. I think this would not be very messy.
The main objection to it is that it doesn't scale to solve the problem
for other types of utility statements. Now we don't support parameters
in other types of utility statements anyway, but it's something we'd
like to do someday probably.
(Of course there are also 3. "Sorry, we're not going to support
variables in EXPLAIN anymore" and 4. Revert all those parsing fixes
in plpgsql, but I rejected these solutions out of hand.)
I'm kind of leaning to #2, particularly given that we don't have time
to expend a great deal of work on this for 8.5. But I wonder if anyone
has any comments or alternative ideas.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
