Subject: Re: Output affected rows in EXPLAIN

From: Damir Belyalov <[email-masked]>
Date: Thu, 7 Sep 2023 17:57:12 +0300

> This creates a bug, not fixes one.  It's intentional that "insert into a"
> is shown as returning zero rows, because that's what it did.  If you'd
> written "insert ... returning", you'd have gotten a different result:
>
Maybe I didn't understand you correctly, but I didn't touch the number of
affected rows in EXPLAIN output.
It's just a simple patch that adds 1 row after using commands: EXPLAIN
INSERT, EXPLAIN UPDATE, EXPLAIN DELETE.
It was done because the commands INSERT/UPDATE/DELETE return one row after
execution: "UPDATE 7" or "INSERT 0 4".
EXPLAIN (ANALYZE) INSERT/UPDATE/DELETE does the same thing as these
commands, but doesn't output this row. So I added it.

Patch is fixed. There is no row "EXPLAIN" in queries like:
postgres=# explain (analyze) select * from t;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..35.50 rows=2550 width=4) (actual
time=0.064..0.075 rows=5 loops=1)
 Planning Time: 1.639 ms
 Execution Time: 0.215 ms
(3 rows)

EXPLAIN

What is about queries EXPLAIN INSERT/UPDATE/DELETE without ANALYZE?
Now it is outputting a row with 0 affected (inserted) rows at the end:
"INSERT 0 0", "UPDATE 0". Example:
explain update a set n = 2;
                         QUERY PLAN
------------------------------------------------------------
 Update on a  (cost=0.00..35.50 rows=0 width=0)
   ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=10)
(2 rows)

UPDATE 0

Regards,
Damir Belyalov
Postgres Professional
From c6cbc6fa9ddf24f29bc19ff115224dd76e351db1 Mon Sep 17 00:00:00 2001
From: Damir Belyalov <[email-masked]>
Date: Tue, 5 Sep 2023 15:04:01 +0300
Subject: [PATCH 1/2] Output affected rows in EXPLAIN.

---
 src/backend/commands/explain.c | 10 +++++++++-
 src/backend/tcop/cmdtag.c      |  2 +-
 src/backend/tcop/pquery.c      |  8 +++++++-
 src/backend/tcop/utility.c     | 27 ++++++++++++++++++++++++++-
 src/bin/psql/common.c          |  5 +++--
 src/include/commands/explain.h |  3 ++-
 src/include/tcop/cmdtag.h      |  1 +
 src/include/tcop/cmdtaglist.h  |  3 +++
 src/interfaces/libpq/fe-exec.c |  4 +++-
 9 files changed, 55 insertions(+), 8 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8570b14f62..453e545ba5 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -162,7 +162,7 @@ static void escape_yaml(StringInfo buf, const char *str);
  */
 void
 ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
-			 ParamListInfo params, DestReceiver *dest)
+			 ParamListInfo params, DestReceiver *dest, uint64 *processed)
 {
 	ExplainState *es = NewExplainState();
 	TupOutputState *tstate;
@@ -173,6 +173,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	bool		timing_set = false;
 	bool		summary_set = false;
 
+	if (processed)
+		*processed = 0;
+
 	/* Parse options list. */
 	foreach(lc, stmt->options)
 	{
@@ -311,6 +314,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	end_tup_output(tstate);
 
 	pfree(es->str->data);
+
+	if (processed)
+		*processed = es->es_processed;
 }
 
 /*
@@ -649,6 +655,8 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 	 */
 	INSTR_TIME_SET_CURRENT(starttime);
 
+	es->es_processed += queryDesc->estate->es_processed;
+
 	ExecutorEnd(queryDesc);
 
 	FreeQueryDesc(queryDesc);
diff --git a/src/backend/tcop/cmdtag.c b/src/backend/tcop/cmdtag.c
index 4bd713a0b4..9e6fdbd8af 100644
--- a/src/backend/tcop/cmdtag.c
+++ b/src/backend/tcop/cmdtag.c
@@ -146,7 +146,7 @@ BuildQueryCompletionString(char *buff, const QueryCompletion *qc,
 	 */
 	if (command_tag_display_rowcount(tag) && !nameonly)
 	{
-		if (tag == CMDTAG_INSERT)
+		if (tag == CMDTAG_INSERT || tag == CMDTAG_EXPLAIN_INSERT)
 		{
 			*bufp++ = ' ';
 			*bufp++ = '0';
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 5565f200c3..ba0b33cc67 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -775,7 +775,13 @@ PortalRun(Portal portal, long count, bool isTopLevel, bool run_once,
 				if (qc && portal->qc.commandTag != CMDTAG_UNKNOWN)
 				{
 					CopyQueryCompletion(qc, &portal->qc);
-					qc->nprocessed = nprocessed;
+					if (portal->qc.commandTag == CMDTAG_EXPLAIN ||
+						portal->qc.commandTag == CMDTAG_EXPLAIN_INSERT ||
+						portal->qc.commandTag == CMDTAG_EXPLAIN_UPDATE ||
+						portal->qc.commandTag == CMDTAG_EXPLAIN_DELETE)
+						qc->nprocessed = portal->qc.nprocessed;
+					else
+						qc->nprocessed = nprocessed;
 				}
 
 				/* Mark portal not active */
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7f7..8975d046f9 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -867,7 +867,32 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_ExplainStmt:
-			ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest);
+			{
+				Query	   *query;
+				uint64		processed;
+				int			explainTag;
+
+				ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest, &processed);
+
+				query = castNode(Query, ((ExplainStmt *) parsetree)->query);
+				switch (query->commandType)
+				{
+					case CMD_INSERT:
+						explainTag = CMDTAG_EXPLAIN_INSERT;
+						break;
+					case CMD_UPDATE:
+						explainTag = CMDTAG_EXPLAIN_UPDATE;
+						break;
+					case CMD_DELETE:
+						explainTag = CMDTAG_EXPLAIN_DELETE;
+						break;
+					default:
+						explainTag = CMDTAG_EXPLAIN;
+						break;
+				}
+				if (qc)
+					SetQueryCompletion(qc, explainTag, processed);
+			}
 			break;
 
 		case T_AlterSystemStmt:
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index ede197bebe..a66d9127c5 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -987,8 +987,9 @@ PrintQueryResult(PGresult *result, bool last,
 			if (last || pset.show_all_results)
 			{
 				cmdstatus = PQcmdStatus(result);
-				if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
-					strncmp(cmdstatus, "UPDATE", 6) == 0 ||
+				if (strncmp(cmdstatus, "EXPLAIN", 7) == 0 ||
+					strncmp(cmdstatus, "INSERT", 6) == 0  ||
+					strncmp(cmdstatus, "UPDATE", 6) == 0  ||
 					strncmp(cmdstatus, "DELETE", 6) == 0)
 					PrintQueryStatus(result, printStatusFout);
 			}
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 3d3e632a0c..21fe5f7555 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -60,6 +60,7 @@ typedef struct ExplainState
 	bool		hide_workers;	/* set if we find an invisible Gather */
 	/* state related to the current plan node */
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
+	uint64		es_processed;	/* sum of queryDesc->estate->es_processed */
 } ExplainState;
 
 /* Hook for plugins to get control in ExplainOneQuery() */
@@ -78,7 +79,7 @@ extern PGDLLIMPORT explain_get_index_name_hook_type explain_get_index_name_hook;

 extern void ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
-						 ParamListInfo params, DestReceiver *dest);
+						 ParamListInfo params, DestReceiver *dest, uint64 *processed);
 
 extern ExplainState *NewExplainState(void);
 
diff --git a/src/include/tcop/cmdtag.h b/src/include/tcop/cmdtag.h
index 1e7514dcff..49f7ea85e7 100644
--- a/src/include/tcop/cmdtag.h
+++ b/src/include/tcop/cmdtag.h
@@ -30,6 +30,7 @@ typedef enum CommandTag
 typedef struct QueryCompletion
 {
 	CommandTag	commandTag;
+	CommandTag	explainCommandTag;
 	uint64		nprocessed;
 } QueryCompletion;
 
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index e738ac1c09..fdc570a304 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -178,6 +178,9 @@ PG_CMDTAG(CMDTAG_DROP_USER_MAPPING, "DROP USER MAPPING", true, false, false)
 PG_CMDTAG(CMDTAG_DROP_VIEW, "DROP VIEW", true, false, false)
 PG_CMDTAG(CMDTAG_EXECUTE, "EXECUTE", false, false, false)
 PG_CMDTAG(CMDTAG_EXPLAIN, "EXPLAIN", false, false, false)
+PG_CMDTAG(CMDTAG_EXPLAIN_INSERT, "INSERT", false, false, true)
+PG_CMDTAG(CMDTAG_EXPLAIN_UPDATE, "UPDATE", false, false, true)
+PG_CMDTAG(CMDTAG_EXPLAIN_DELETE, "DELETE", false, false, true)
 PG_CMDTAG(CMDTAG_FETCH, "FETCH", false, false, true)
 PG_CMDTAG(CMDTAG_GRANT, "GRANT", true, false, false)
 PG_CMDTAG(CMDTAG_GRANT_ROLE, "GRANT ROLE", false, false, false)
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index c6d80ec396..b85e9b8e04 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -3741,7 +3741,9 @@ PQcmdTuples(PGresult *res)
 	if (!res)
 		return "";
 
-	if (strncmp(res->cmdStatus, "INSERT ", 7) == 0)
+	if (strncmp(res->cmdStatus, "EXPLAIN ", 8) == 0)
+		p = res->cmdStatus + 8;
+	else if (strncmp(res->cmdStatus, "INSERT ", 7) == 0)
 	{
 		p = res->cmdStatus + 7;
 		/* INSERT: skip oid and space */
-- 
2.34.1

From eceaa19b847b18ee3346b0d2fc526e69557b71bd Mon Sep 17 00:00:00 2001
From: Damir Belyalov <[email-masked]>
Date: Thu, 7 Sep 2023 17:27:21 +0300
Subject: [PATCH 2/2] v2 Output affected rows in EXPLAIN.

---
 src/bin/psql/common.c          | 5 ++---
 src/include/tcop/cmdtag.h      | 1 -
 src/interfaces/libpq/fe-exec.c | 6 ++----
 3 files changed, 4 insertions(+), 8 deletions(-)

diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a66d9127c5..1f39dcef4b 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -983,12 +983,11 @@ PrintQueryResult(PGresult *result, bool last,
 			else
 				success = true;
 
-			/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
+			/* if it's EXPLAIN/INSERT/UPDATE/DELETE RETURNING, also print status */
 			if (last || pset.show_all_results)
 			{
 				cmdstatus = PQcmdStatus(result);
-				if (strncmp(cmdstatus, "EXPLAIN", 7) == 0 ||
-					strncmp(cmdstatus, "INSERT", 6) == 0  ||
+				if (strncmp(cmdstatus, "INSERT", 6) == 0  ||
 					strncmp(cmdstatus, "UPDATE", 6) == 0  ||
 					strncmp(cmdstatus, "DELETE", 6) == 0)
 					PrintQueryStatus(result, printStatusFout);
diff --git a/src/include/tcop/cmdtag.h b/src/include/tcop/cmdtag.h
index 49f7ea85e7..1e7514dcff 100644
--- a/src/include/tcop/cmdtag.h
+++ b/src/include/tcop/cmdtag.h
@@ -30,7 +30,6 @@ typedef enum CommandTag
 typedef struct QueryCompletion
 {
 	CommandTag	commandTag;
-	CommandTag	explainCommandTag;
 	uint64		nprocessed;
 } QueryCompletion;
 
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index b85e9b8e04..40ea2d7ac2 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -3726,7 +3726,7 @@ PQoidValue(const PGresult *res)
 
 /*
  * PQcmdTuples -
- *	If the last command was INSERT/UPDATE/DELETE/MERGE/MOVE/FETCH/COPY,
+ *	If the last command was EXPLAIN/INSERT/UPDATE/DELETE/MERGE/MOVE/FETCH/COPY,
  *	return a string containing the number of inserted/affected tuples.
  *	If not, return "".
  *
@@ -3741,9 +3741,7 @@ PQcmdTuples(PGresult *res)
 	if (!res)
 		return "";
 
-	if (strncmp(res->cmdStatus, "EXPLAIN ", 8) == 0)
-		p = res->cmdStatus + 8;
-	else if (strncmp(res->cmdStatus, "INSERT ", 7) == 0)
+	 if (strncmp(res->cmdStatus, "INSERT ", 7) == 0)
 	{
 		p = res->cmdStatus + 7;
 		/* INSERT: skip oid and space */
-- 
2.34.1

---

From: [email-masked]
Date: Wed, 15 Nov 2023 16:15:34 +0900

Hi hackers,

Indeed, I think it is a little confusing that when executing
EXPLAIN(ANALYZE), even though an update is actually occurring,
the commandtag of the update result is not returned.

However, the manual also describes the information that will be
affected when EXPLAIN (ANALYZE) is executed as important information.
https://www.postgresql.org/docs/current/sql-explain.html

Also, in most cases, users who use EXPLAIN(ANALYZE) only want
an execution plan of a statement.
If command tags are not required, this can be controlled using
the QUIET variable, but command tags other than EXPLAIN will also
be omitted, increasing the scope of the effect.
We can check the number of updated rows from execute plan,
I think there is no need to return the command tag
when EXPLAIN(ANALYZE) is executed by default.

## patch and QUIET=off(default)

postgres=# explain (analyze) insert into a values (1);
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  Insert on a  (cost=0.00..0.01 rows=0 width=0) (actual time=0.227..0.228 
rows=0 loops=1)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.013..0.015 rows=1 loops=1)
  Planning Time: 0.152 ms
  Execution Time: 0.480 ms
(4 rows)

INSERT 0 1

## patch and QUIET=on(psql work quietly)

'INSERT 0 1' is omitted both 'explain(analyze) and 'INSERT'.

postgres=# \set QUIET on
postgres=# explain (analyze) insert into a values (1);
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  Insert on a  (cost=0.00..0.01 rows=0 width=0) (actual time=0.058..0.059 
rows=0 loops=1)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.004..0.005 rows=1 loops=1)
  Planning Time: 0.059 ms
  Execution Time: 0.117 ms
(4 rows)

postgres=# insert into a values (1);
postgres=#

Best Regards,
Keisuke Kuroda
NTT COMWARE

On 2023-09-07 23:57, Damir Belyalov wrote:
> Maybe I didn't understand you correctly, but I didn't touch the number
> of affected rows in EXPLAIN output.
> It was done because the commands INSERT/UPDATE/DELETE return one row
> after execution: "UPDATE 7" or "INSERT 0 4".