On Tue, 2022-12-06 at 10:17 -0800, Andres Freund wrote: > On 2022-10-29 10:35:26 +0200, Laurenz Albe wrote: > > > > Here is a patch that > > > > implements it with an EXPLAIN option named GENERIC_PLAN. > > This fails to build the docs: > > https://cirrus-ci.com/task/5609301511766016 > > [17:47:01.064] ref/explain.sgml:179: parser error : Opening and ending tag > mismatch: likeral line 179 and literal > [17:47:01.064] <likeral>ANALYZE</literal>, since a statement with > unknown parameters > [17:47:01.064] ^
*blush* Here is a fixed version. Yours, Laurenz Albe
From 8704d51f5810619be152ae68faa5743dcf26c7a9 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Fri, 28 Oct 2022 20:58:59 +0200 Subject: [PATCH] Add EXPLAIN option GENERIC_PLAN This allows EXPLAIN to generate generic plans for parameterized statements (that have parameter placeholders like $1 in the statement text). Author: Laurenz Albe Discussion: https://postgr.es/m/0a29b954b10b57f0d135fe12aa0909bd41883eb0.camel%40cybertec.at --- doc/src/sgml/ref/explain.sgml | 15 +++++++++++++++ src/backend/commands/explain.c | 9 +++++++++ src/backend/parser/analyze.c | 13 +++++++++++++ src/backend/parser/parse_coerce.c | 15 +++++++++++++++ src/backend/parser/parse_expr.c | 16 ++++++++++++++++ src/include/commands/explain.h | 1 + src/include/parser/parse_node.h | 2 ++ src/test/regress/expected/explain.out | 14 ++++++++++++++ src/test/regress/sql/explain.sql | 5 +++++ 9 files changed, 90 insertions(+) diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index d4895b9d7d..659d5c51b6 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -40,6 +40,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replac VERBOSE [ <replaceable class="parameter">boolean</replaceable> ] COSTS [ <replaceable class="parameter">boolean</replaceable> ] SETTINGS [ <replaceable class="parameter">boolean</replaceable> ] + GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ] BUFFERS [ <replaceable class="parameter">boolean</replaceable> ] WAL [ <replaceable class="parameter">boolean</replaceable> ] TIMING [ <replaceable class="parameter">boolean</replaceable> ] @@ -167,6 +168,20 @@ ROLLBACK; </listitem> </varlistentry> + <varlistentry> + <term><literal>GENERIC_PLAN</literal></term> + <listitem> + <para> + Generate a generic plan for the statement (see <xref linkend="sql-prepare"/> + for details about generic plans). The statement can contain parameter + placeholders like <literal>$1</literal> and must be a statement that can + use parameters. This option cannot be used together with + <literal>ANALYZE</literal>, since a statement with unknown parameters + cannot be executed. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>BUFFERS</literal></term> <listitem> diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index f86983c660..7b7ca3f90a 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -190,6 +190,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) es->settings = defGetBoolean(opt); + else if (strcmp(opt->defname, "generic_plan") == 0) + es->generic = defGetBoolean(opt); else if (strcmp(opt->defname, "timing") == 0) { timing_set = true; @@ -227,6 +229,13 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, parser_errposition(pstate, opt->location))); } + /* check that GENERIC_PLAN is not used with EXPLAIN ANALYZE */ + if (es->generic && es->analyze) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("EXPLAIN ANALYZE cannot be used with GENERIC_PLAN"))); + + /* check that WAL is used with EXPLAIN ANALYZE */ if (es->wal && !es->analyze) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 6688c2a865..c849765151 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -27,6 +27,7 @@ #include "access/sysattr.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "commands/defrem.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -2894,6 +2895,18 @@ static Query * transformExplainStmt(ParseState *pstate, ExplainStmt *stmt) { Query *result; + ListCell *lc; + bool generic_plan; + + foreach(lc, stmt->options) + { + DefElem *opt = (DefElem *) lfirst(lc); + + if (strcmp(opt->defname, "generic_plan") == 0) + generic_plan = defGetBoolean(opt); + /* don't "break", as we want the last value */ + } + pstate->p_generic_explain = generic_plan; /* transform contained query, allowing SELECT INTO */ stmt->query = (Node *) transformOptionalSelectInto(pstate, stmt->query); diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c index c4e958e4aa..171d8c60a8 100644 --- a/src/backend/parser/parse_coerce.c +++ b/src/backend/parser/parse_coerce.c @@ -369,6 +369,21 @@ coerce_type(ParseState *pstate, Node *node, return result; } + /* + * If we are to generate a generic plan for EXPLAIN, simply let the + * parameter be of the desired type. + */ + if (IsA(node, Param) && + pstate != NULL && pstate->p_generic_explain) + { + Param *param = (Param *) node; + + param->paramtype = targetTypeId; + param->paramtypmod = targetTypeMod; + param->location = location; + + return node; + } if (IsA(node, Param) && pstate != NULL && pstate->p_coerce_param_hook != NULL) { diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 7aaf1c673f..75a24ed9b2 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -815,6 +815,22 @@ transformParamRef(ParseState *pstate, ParamRef *pref) { Node *result; + /* for "generic_plan" EXPLAIN, supply an unknown parameter */ + if (pstate->p_generic_explain) + { + Param *param; + + param = makeNode(Param); + param->paramkind = PARAM_EXTERN; + param->paramid = pref->number; + param->paramtype = UNKNOWNOID; + param->paramtypmod = -1; + param->paramcollid = InvalidOid; + param->location = pref->location; + + return (Node *)param; + } + /* * The core parser knows nothing about Params. If a hook is supplied, * call it. If not, or if the hook returns NULL, throw a generic error. diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index 9ebde089ae..524d355961 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -46,6 +46,7 @@ typedef struct ExplainState bool timing; /* print detailed node timing */ bool summary; /* print total planning and execution timing */ bool settings; /* print modified settings */ + bool generic; /* generate a generic plan */ ExplainFormat format; /* output format */ /* state for output formatting --- not reset for each new plan tree */ int indent; /* current indentation level */ diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 962ebf65de..0095fdb347 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -202,6 +202,8 @@ struct ParseState * with FOR UPDATE/FOR SHARE */ bool p_resolve_unknowns; /* resolve unknown-type SELECT outputs as * type text */ + bool p_generic_explain; /* accept undefined parameter + * placeholders */ QueryEnvironment *p_queryEnv; /* curr env, incl refs to enclosing env */ diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index 48620edbc2..18f7ac93c4 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -517,3 +517,17 @@ select explain_filter('explain (verbose) select * from int8_tbl i8'); Query Identifier: N (3 rows) +-- Test EXPLAIN (GENERIC_PLAN) +select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); + explain_filter +--------------------------------------------------------------------------------- + Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N) + Recheck Cond: (thousand = $N) + -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N) + Index Cond: (thousand = $N) +(4 rows) + +-- should fail +select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +ERROR: EXPLAIN ANALYZE cannot be used with GENERIC_PLAN +CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index ae3f7a308d..fce031775a 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -128,3 +128,8 @@ select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1 -- Test compute_query_id set compute_query_id = on; select explain_filter('explain (verbose) select * from int8_tbl i8'); + +-- Test EXPLAIN (GENERIC_PLAN) +select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); +-- should fail +select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); -- 2.37.3
