> 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 protected]>
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 protected]>
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
