Output affected rows in EXPLAIN
I create a patch that outputs affected rows in EXPLAIN that occur by
INSERT/UPDATE/DELETE.
Despite the fact that commands in EXPLAIN ANALYZE query are executed as
usual, EXPLAIN doesn't show outputting affected rows as in these commands.
The patch fixes this problem.
Examples:
explain analyze insert into a values (1);
QUERY PLAN
------------------------------------------------------------------------------------------
Insert on a (cost=0.00..0.01 rows=0 width=0) (actual time=0.076..0.077
rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002
rows=1 loops=1)
Planning Time: 0.025 ms
Execution Time: 0.412 ms
(4 rows)
INSERT 0 1
QUERY PLAN
------------------------------------------------------------------------------------------------------
Update on a (cost=0.00..35.50 rows=0 width=0) (actual time=0.059..0.060
rows=0 loops=1)
-> Seq Scan on a (cost=0.00..35.50 rows=2550 width=10) (actual
time=0.012..0.013 rows=7 loops=1)
Planning Time: 0.142 ms
Execution Time: 0.666 ms
(4 rows)
UPDATE 7
explain analyze delete from a where n = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Delete on a (cost=0.00..41.88 rows=0 width=0) (actual time=0.147..0.147
rows=0 loops=1)
-> Seq Scan on a (cost=0.00..41.88 rows=13 width=6) (actual
time=0.120..0.123 rows=7 loops=1)
Filter: (n = 1)
Planning Time: 1.073 ms
Execution Time: 0.178 ms
(5 rows)
DELETE 7
EXPLAIN queries without ANALYZE don't affect rows, so the output number is
0.
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
Maybe there is no need to add this row when EXPLAIN has no ANALYZE. So it
is a discussion question.
Also haven't fixed regress tests yet.
Regards,
Damir Belyalov
Postgres Professional
Attachments:
0001-Output-affected-rows-in-EXPLAIN.patchtext/x-patch; charset=US-ASCII; name=0001-Output-affected-rows-in-EXPLAIN.patchDownload
From c6cbc6fa9ddf24f29bc19ff115224dd76e351db1 Mon Sep 17 00:00:00 2001
From: Damir Belyalov <d.belyalov@postgrespro.ru>
Date: Tue, 5 Sep 2023 15:04:01 +0300
Subject: [PATCH] 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
On 6 Sep 2023, at 14:49, Damir Belyalov <dam.bel07@gmail.com> wrote
The patch fixes this problem.
Given that EXPLAIN ANALYZE has worked like for a very long time, which problem
is it you have identified?
I'm also not convinced that the added "EXPLAIN" in the below plan is an
improvement in any way.
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
postgres=#
--
Daniel Gustafsson
Damir Belyalov <dam.bel07@gmail.com> writes:
I create a patch that outputs affected rows in EXPLAIN that occur by
INSERT/UPDATE/DELETE.
Despite the fact that commands in EXPLAIN ANALYZE query are executed as
usual, EXPLAIN doesn't show outputting affected rows as in these commands.
The patch fixes this problem.
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:
=# explain analyze insert into a values (1);
QUERY PLAN
------------------------------------------------------------------------------------------
Insert on a (cost=0.00..0.01 rows=0 width=0) (actual time=0.015..0.016 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.015 ms
Execution Time: 0.027 ms
(4 rows)
=# explain analyze insert into a values (1) returning *;
QUERY PLAN
------------------------------------------------------------------------------------------
Insert on a (cost=0.00..0.01 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
Planning Time: 0.031 ms
Execution Time: 0.051 ms
(4 rows)
Now admittedly, if you want to know the number of rows that went to disk,
you have to infer that from the number of rows emitted by the
ModifyTable's child plan. But that's a matter for documentation
(and I'm pretty sure it's documented someplace).
regards, tom lane
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
Attachments:
0002-Output-affected-rows-in-EXPLAIN.patchtext/x-patch; charset=US-ASCII; name=0002-Output-affected-rows-in-EXPLAIN.patchDownload
From c6cbc6fa9ddf24f29bc19ff115224dd76e351db1 Mon Sep 17 00:00:00 2001
From: Damir Belyalov <d.belyalov@postgrespro.ru>
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 <d.belyalov@postgrespro.ru>
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
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
Show quoted text
On 2023-09-07 23:57, Damir Belyalov wrote:
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
On Wed, Nov 15, 2023 at 2:17 PM <kuroda.keisuke@nttcom.co.jp> wrote:
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.
Given that several people have voiced an opinion against this patch,
I'm marking it rejected.