Well, here we are! Yet another thread about some piece of information that's omitted from EXPLAIN and can't easily be added because there are a zillion things we want to add to EXPLAIN and it's not OK to bury the user[1]! I've long been of the opinion that the right way to fix this problem is to extend the syntax with some sort of extensible options syntax[2]. The current "EXPLAIN [ANALYZE] [VERBOSE] <query>" syntax does not scale to large numbers of options - it requires that the options occur in a fixed order, and that the option names all be keywords. Having gotten throughly fed up with having this conversation for the ump-teenth time, I wrote a patch to introduce just such a syntax. See attached.
What I did is borrowed the generic options stuff that Peter Eisentraut introduced for FOREIGN DATA WRAPPER et. al, so you can write: EXPLAIN (option_name1 "option_value1", option_name2 "option_value2") query e.g. EXPLAIN (ANALYZE "on") query As written, this patch doesn't introduce any actual new functionality, but I think it's pretty easy to see how we could build on the syntax to add things like different types of output formats, different types of instrumentation, etc. A few other random notes: - This currently lacks documentation. If we have any consensus that this is a reasonable approach, I'll add some. - I noticed that we currently accept as a top-level SQL command an arbitrarily parenthesized SELECT statement, like ((SELECT 3)). But you can't put parentheses around any other type of statement. Even more oddly, we also accept things like (SELECT 3) ORDER BY 1, which to me makes no sense at all. But that's neither here nor there as far as this patch is concerned, except that it required some minor grammar hackery and a long comment explaining the hackery. Thoughts? ...Robert [1] http://archives.postgresql.org/message-id/4a16a8af.2080...@anarazel.de [2] http://archives.postgresql.org/message-id/603c8f070904151758w6af25641xac831b4cb71c4...@mail.gmail.com
*** a/src/backend/nodes/makefuncs.c --- b/src/backend/nodes/makefuncs.c *************** *** 17,24 **** --- 17,26 ---- #include "catalog/pg_type.h" #include "nodes/makefuncs.h" + #include "utils/builtins.h" #include "utils/lsyscache.h" + static bool parseBooleanGenericOption(DefElem *opt); /* * makeA_Expr - *************** *** 385,387 **** makeDefElemExtended(char *namespace, char *name, Node *arg, --- 387,435 ---- return res; } + + /* + * makeExplain - + * build an ExplainStmt node by parsing the generic options list + */ + ExplainStmt * + makeExplain(List *options, Node *query) + { + ExplainStmt *n = makeNode(ExplainStmt); + ListCell *lc; + + foreach (lc, options) + { + DefElem *opt = lfirst(lc); + if (!strcmp(opt->defname, "analyze")) + n->analyze = parseBooleanGenericOption(opt); + else if (!strcmp(opt->defname, "verbose")) + n->verbose = parseBooleanGenericOption(opt); + else + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_PARAMETER), + errmsg("unknown EXPLAIN option: %s", opt->defname))); + } + + n->query = query; + return n; + } + + /* + * parseBooleanGenericOption - + * Interpret a generic option as a boolean. Currently, a generic_option_arg + * can only be a string, so we don't need to worry about anything else. + */ + static bool + parseBooleanGenericOption(DefElem *opt) + { + bool res; + + Assert(IsA(opt->arg, String)); + if (!parse_bool(strVal(opt->arg), &res)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("parameter \"%s\" requires a Boolean value", + opt->defname))); + return res; + } *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 6436,6452 **** opt_name_list: * * QUERY: * EXPLAIN [ANALYZE] [VERBOSE] query * *****************************************************************************/ ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt { ! ExplainStmt *n = makeNode(ExplainStmt); n->analyze = $2; n->verbose = $3; - n->query = $4; $$ = (Node *)n; } ; ExplainableStmt: --- 6436,6456 ---- * * QUERY: * EXPLAIN [ANALYZE] [VERBOSE] query + * EXPLAIN ( generic options ) query * *****************************************************************************/ ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt { ! ExplainStmt *n = makeExplain(NIL, (Node *) $4); n->analyze = $2; n->verbose = $3; $$ = (Node *)n; } + | EXPLAIN '(' generic_option_list ')' ExplainableStmt + { + $$ = (Node *) makeExplain((List *) $3, (Node *) $5); + } ; ExplainableStmt: *************** *** 6459,6467 **** ExplainableStmt: | ExecuteStmt /* by default all are $$=$1 */ ; opt_analyze: analyze_keyword { $$ = TRUE; } ! | /* EMPTY */ { $$ = FALSE; } ; /***************************************************************************** --- 6463,6484 ---- | ExecuteStmt /* by default all are $$=$1 */ ; + /* + * The precedence declaration for the opt_analyze EMPTY case, below, is + * necessary to prevent a shift/reduce conflict in the second production for + * ExplainStmt, above. Otherwise, when the parser encounters "EXPLAIN (", it + * can't tell whether the "(" is the beginning of a SelectStmt or the beginning + * of the options list. The precedence declaration below forces the latter + * interpretation. + * + * It might seem that we could get away with simply changing the definition of + * ExplainableStmt to use select_without_parens rather than SelectStmt, but + * that does not work, because select_without_parens produces expressions such + * as "(SELECT NULL) ORDER BY 1" that we interpret as legal queries. + */ opt_analyze: analyze_keyword { $$ = TRUE; } ! | /* EMPTY */ %prec UMINUS { $$ = FALSE; } ; /***************************************************************************** *** a/src/include/nodes/makefuncs.h --- b/src/include/nodes/makefuncs.h *************** *** 69,72 **** extern DefElem *makeDefElem(char *name, Node *arg); --- 69,74 ---- extern DefElem *makeDefElemExtended(char *namespace, char *name, Node *arg, DefElemAction defaction); + extern ExplainStmt *makeExplain(List *options, Node *query); + #endif /* MAKEFUNC_H */
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers