Skip site navigation (1) Skip section navigation (2)

Re: EXPLAIN, utility statement parameters, and recent plpgsql changes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: EXPLAIN, utility statement parameters, and recent plpgsql changes
Date: 2010-01-14 19:56:20
Message-ID: 28031.1263498980@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I wrote:
> 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.

I've been looking some more at this.  The analogy to DECLARE CURSOR
isn't as good as I thought: we can't use a transformed representation
similar to DECLARE CURSOR's (namely, a Query with some extra stuff in
its utilityStmt field) because EXPLAIN can take non-SELECT queries,
which could be rewritten into multiple Query trees by the action of
rules.  So it seems the transformed representation would have to be
an ExplainStmt with a list of Queries underneath it.

The reason for the rule that utility statements aren't affected by parse
analysis is that parse analysis of regular queries takes locks on the
referenced tables, and we must keep hold of those locks to be sure that
the transformed tree still reflects database reality.  At the time we
made that rule it seemed too messy to consider doing anything similar
for utility statements.  However, now the locking considerations have
been centralized in plancache.c, which knows about re-taking locks on
a possibly stale cached plan.  So the price of doing parse analysis of
EXPLAIN's target statement during the normal parse analysis phase is
just going to be some adjustments in plancache.c so that it knows to
look underneath an ExplainStmt for queries representing additional locks
to re-take.  This is a little bit ugly, but not really any worse than
what it knows already about the representation of parsed queries.

So I conclude that the "it doesn't scale" argument isn't as strong
as it seemed.  In principle, to support parameters in other utility
statements, we'll have the same type of changes to make:
  * transform the expressions that might reference parameters during
    the normal parse analysis phase
  * teach plancache.c about finding lock dependencies in these
    expressions
That seems fairly reasonable.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Markus WannerDate: 2010-01-14 20:08:19
Subject: Re: Testing with concurrent sessions
Previous:From: Tom LaneDate: 2010-01-14 19:39:21
Subject: Re: EXPLAIN, utility statement parameters, and recent plpgsql changes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group