0001-Add-EXPLAIN-option-GENERIC_PLAN.patch

From 85991f35f0de6e4e0a0b5843373e2ba3d5976c85 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Tue, 25 Oct 2022 11:01:53 +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 | 28 +++++++++++++++++++++++++++
 src/test/regress/sql/explain.sql      | 16 +++++++++++++++
 9 files changed, 115 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
+      <likeral>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..53bb024813 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -517,3 +517,31 @@ select explain_filter('explain (verbose) select * from int8_tbl i8');
  Query Identifier: N
 (3 rows)
 
+-- Test EXPLAIN (GENERIC_PLAN)
+EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Bitmap Heap Scan on tenk1
+   Recheck Cond: (thousand = $1)
+   ->  Bitmap Index Scan on tenk1_thous_tenthous
+         Index Cond: (thousand = $1)
+(4 rows)
+
+-- should fail
+EXPLAIN (COSTS OFF, GENERIC_PLAN, ANALYZE) SELECT unique1 FROM tenk1 WHERE thousand = $1;
+ERROR:  EXPLAIN ANALYZE cannot be used with GENERIC_PLAN
+-- should also work in PL/pgSQL
+DO
+$$DECLARE
+    t text;
+BEGIN
+    FOR t IN
+        EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1
+    LOOP
+        RAISE NOTICE '%', t;
+    END LOOP;
+END;$$;
+NOTICE:  Bitmap Heap Scan on tenk1
+NOTICE:    Recheck Cond: (thousand = $1)
+NOTICE:    ->  Bitmap Index Scan on tenk1_thous_tenthous
+NOTICE:          Index Cond: (thousand = $1)
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index ae3f7a308d..c2438efdcc 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -128,3 +128,19 @@ 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)
+EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1;
+-- should fail
+EXPLAIN (COSTS OFF, GENERIC_PLAN, ANALYZE) SELECT unique1 FROM tenk1 WHERE thousand = $1;
+-- should also work in PL/pgSQL
+DO
+$$DECLARE
+    t text;
+BEGIN
+    FOR t IN
+        EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1
+    LOOP
+        RAISE NOTICE '%', t;
+    END LOOP;
+END;$$;
-- 
2.37.3