2010/1/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel pointed out here
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-hackers by date
|Next:||From: Greg Smith||Date: 2010-01-14 18:46:47|
|Subject: Re: Clearing global statistics|
|Previous:||From: David Fetter||Date: 2010-01-14 18:36:09|
|Subject: Re: mailing list archiver chewing patches|