enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

Started by Katsuragi Yutaover 5 years ago9 messages

btkatsuragiyu@oss.nttdata.com

over 5 years ago

1 attachment(s)

Hi,

pg_stat_statements tracks the number of rows processed
by some utility commands.
But, currently, it does not track the number of rows
processed by REFRESH MATERIALIZED VIEW.

Attached patch enables pg_stat_statements to track
processed rows by REFRESH MATERIALIZED VIEW.

Regards,
Katsuragi Yuta

Attachments:

pg_stat_statements_mt_refresh_v1.patchtext/x-diff; charset=us-ascii; name=pg_stat_statements_mt_refresh_v1.patchDownload

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0edb134f3..2a303a7f07 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -530,8 +530,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
--- and SELECT INTO
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
 --
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -543,6 +543,7 @@ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
 SELECT generate_series(1, 10) c INTO pgss_select_into;
 COPY pgss_ctas (a, b) FROM STDIN;
 CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
 BEGIN;
 DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
 FETCH NEXT pgss_cursor;
@@ -586,10 +587,11 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
  FETCH FORWARD 5 pgss_cursor                                                         |     0 |     1 |    5
  FETCH FORWARD ALL pgss_cursor                                                       |     0 |     1 |    7
  FETCH NEXT pgss_cursor                                                              |     0 |     1 |    1
+ REFRESH MATERIALIZED VIEW pgss_matv                                                 |     0 |     1 |   13
  SELECT generate_series(1, 10) c INTO pgss_select_into                               |     0 |     1 |   10
  SELECT pg_stat_statements_reset()                                                   |     0 |     1 |    1
  SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |     0 |    0
-(12 rows)
+(13 rows)
 
 --
 -- Track user activity and reset them
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1eac9edaee..1c2ac24cf6 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -1173,11 +1173,12 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		/*
 		 * Track the total number of rows retrieved or affected by
 		 * the utility statements of COPY, FETCH, CREATE TABLE AS,
-		 * CREATE MATERIALIZED VIEW and SELECT INTO.
+		 * CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW and SELECT INTO.
 		 */
 		rows = (qc && (qc->commandTag == CMDTAG_COPY ||
 					   qc->commandTag == CMDTAG_FETCH ||
-					   qc->commandTag == CMDTAG_SELECT)) ?
+					   qc->commandTag == CMDTAG_SELECT ||
+					   qc->commandTag == CMDTAG_REFRESH_MATERIALIZED_VIEW)) ?
 			qc->nprocessed : 0;
 
 		/* calc differences of buffer counters. */
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 996a24a293..e9f5bb84e3 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -252,8 +252,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
--- and SELECT INTO
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
 --
 SELECT pg_stat_statements_reset();
 
@@ -265,6 +265,7 @@ COPY pgss_ctas (a, b) FROM STDIN;
 13	copy
 \.
 CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
 BEGIN;
 DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
 FETCH NEXT pgss_cursor;
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index f80a9e96a9..cdc2e62ad4 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -136,7 +136,7 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
  */
 ObjectAddress
 ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
-				   ParamListInfo params, QueryCompletion *qc)
+				   ParamListInfo params, QueryCompletion *qc, uint64 *processed)
 {
 	Oid			matviewOid;
 	Relation	matviewRel;
@@ -147,7 +147,6 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 	Oid			relowner;
 	Oid			OIDNewHeap;
 	DestReceiver *dest;
-	uint64		processed = 0;
 	bool		concurrent;
 	LOCKMODE	lockmode;
 	char		relpersistence;
@@ -311,7 +310,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 
 	/* Generate the data, if wanted. */
 	if (!stmt->skipData)
-		processed = refresh_matview_datafill(dest, dataQuery, queryString);
+		*processed = refresh_matview_datafill(dest, dataQuery, queryString);
 
 	/* Make the matview match the newly generated data. */
 	if (concurrent)
@@ -343,7 +342,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 		 */
 		pgstat_count_truncate(matviewRel);
 		if (!stmt->skipData)
-			pgstat_count_heap_insert(matviewRel, processed);
+			pgstat_count_heap_insert(matviewRel, *processed);
 	}
 
 	table_close(matviewRel, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 9713a7ac41..19e964ea5c 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1638,24 +1638,27 @@ ProcessUtilitySlow(ParseState *pstate,
 				break;
 
 			case T_RefreshMatViewStmt:
-
-				/*
-				 * REFRESH CONCURRENTLY executes some DDL commands internally.
-				 * Inhibit DDL command collection here to avoid those commands
-				 * from showing up in the deparsed command queue.  The refresh
-				 * command itself is queued, which is enough.
-				 */
-				EventTriggerInhibitCommandCollection();
-				PG_TRY();
 				{
-					address = ExecRefreshMatView((RefreshMatViewStmt *) parsetree,
-												 queryString, params, qc);
-				}
-				PG_FINALLY();
-				{
-					EventTriggerUndoInhibitCommandCollection();
+					uint64	processed = 0;
+					/*
+					 * REFRESH CONCURRENTLY executes some DDL commands internally.
+					 * Inhibit DDL command collection here to avoid those commands
+					 * from showing up in the deparsed command queue.  The refresh
+					 * command itself is queued, which is enough.
+					 */
+					EventTriggerInhibitCommandCollection();
+					PG_TRY();
+					{
+						address = ExecRefreshMatView((RefreshMatViewStmt *) parsetree,
+													 queryString, params, qc, &processed);
+					}
+					PG_FINALLY();
+					{
+						EventTriggerUndoInhibitCommandCollection();
+					}
+					PG_END_TRY();
+					SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
 				}
-				PG_END_TRY();
 				break;
 
 			case T_CreateTrigStmt:
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index 3ea4f5c80b..9407883261 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -24,7 +24,7 @@
 extern void SetMatViewPopulatedState(Relation relation, bool newstate);
 
 extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
-										ParamListInfo params, QueryCompletion *qc);
+										ParamListInfo params, QueryCompletion *qc, uint64 *processed);
 
 extern DestReceiver *CreateTransientRelDestReceiver(Oid oid);

legrand legrand

legrand_legrand@hotmail.com

over 5 years ago

In reply to: Katsuragi Yuta (#1)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

Hi,

isn't this already fixed in pg14
/messages/by-id/E1k0mzG-0002Vn-2W@gemulon.postgresql.org
?

Regards
PAscal

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Fujii Masao

masao.fujii@oss.nttdata.com

over 5 years ago

In reply to: legrand legrand (#2)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

On 2020/09/25 19:04, legrand legrand wrote:

Hi,

isn't this already fixed in pg14
/messages/by-id/E1k0mzG-0002Vn-2W@gemulon.postgresql.org
?

IIUC that commit handled CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW
and FETCH commands, but not REFRESH MATERIALIZED VIEW. Katsuragi-san's patch is
for REFRESH MATERIALIZED VIEW.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

legrand legrand

legrand_legrand@hotmail.com

over 5 years ago

In reply to: Fujii Masao (#3)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

oups, sorry
so +1 for this fix

Regards
PAscal

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Yuki Seino

seinoyu@oss.nttdata.com

about 5 years ago

In reply to: legrand legrand (#4)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

+1.
I checked the patch and there were no problems.
I hope this fix will be reflected.

The new status of this patch is: Ready for Committer

Fujii Masao

masao.fujii@oss.nttdata.com

about 5 years ago

In reply to: Yuki Seino (#5)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

On 2020/11/02 14:02, Yuki Seino wrote:

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

+1.
I checked the patch and there were no problems.

+		PG_END_TRY();
+		SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);

Isn't it better to call SetQueryCompletion() in ExecRefreshMatView()
instead of ProcessUtilitySlow() (e.g., ExecCreateTableAs() does)?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Seino Yuki

seinoyu@oss.nttdata.com

about 5 years ago

In reply to: Fujii Masao (#6)

1 attachment(s)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

2020-11-02 20:01 に Fujii Masao さんは書きました:

On 2020/11/02 14:02, Yuki Seino wrote:

The following review has been posted through the commitfest
application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

+1.
I checked the patch and there were no problems.
+		PG_END_TRY();
+		SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
Isn't it better to call SetQueryCompletion() in ExecRefreshMatView()
instead of ProcessUtilitySlow() (e.g., ExecCreateTableAs() does)?

Regards,

Sorry. I missed it.
I've incorporated your point into this patch.
So the changes to "matview.h" and "utility.c" have been canceld.

We also confirmed that the new patch passed the regression test.

Regards,

Attachments:

pg_stat_statements_mt_refresh_v2.patchtext/x-diff; name=pg_stat_statements_mt_refresh_v2.patchDownload

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0edb134f3..2a303a7f07 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -530,8 +530,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
--- and SELECT INTO
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
 --
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -543,6 +543,7 @@ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
 SELECT generate_series(1, 10) c INTO pgss_select_into;
 COPY pgss_ctas (a, b) FROM STDIN;
 CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
 BEGIN;
 DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
 FETCH NEXT pgss_cursor;
@@ -586,10 +587,11 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
  FETCH FORWARD 5 pgss_cursor                                                         |     0 |     1 |    5
  FETCH FORWARD ALL pgss_cursor                                                       |     0 |     1 |    7
  FETCH NEXT pgss_cursor                                                              |     0 |     1 |    1
+ REFRESH MATERIALIZED VIEW pgss_matv                                                 |     0 |     1 |   13
  SELECT generate_series(1, 10) c INTO pgss_select_into                               |     0 |     1 |   10
  SELECT pg_stat_statements_reset()                                                   |     0 |     1 |    1
  SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |     0 |    0
-(12 rows)
+(13 rows)
 
 --
 -- Track user activity and reset them
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1eac9edaee..1c2ac24cf6 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -1173,11 +1173,12 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		/*
 		 * Track the total number of rows retrieved or affected by
 		 * the utility statements of COPY, FETCH, CREATE TABLE AS,
-		 * CREATE MATERIALIZED VIEW and SELECT INTO.
+		 * CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW and SELECT INTO.
 		 */
 		rows = (qc && (qc->commandTag == CMDTAG_COPY ||
 					   qc->commandTag == CMDTAG_FETCH ||
-					   qc->commandTag == CMDTAG_SELECT)) ?
+					   qc->commandTag == CMDTAG_SELECT ||
+					   qc->commandTag == CMDTAG_REFRESH_MATERIALIZED_VIEW)) ?
 			qc->nprocessed : 0;
 
 		/* calc differences of buffer counters. */
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 996a24a293..e9f5bb84e3 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -252,8 +252,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
--- and SELECT INTO
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
 --
 SELECT pg_stat_statements_reset();
 
@@ -265,6 +265,7 @@ COPY pgss_ctas (a, b) FROM STDIN;
 13	copy
 \.
 CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
 BEGIN;
 DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
 FETCH NEXT pgss_cursor;
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index f80a9e96a9..8af8bd6aa8 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -356,6 +356,9 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 
 	ObjectAddressSet(address, RelationRelationId, matviewOid);
 
+	/* save the rowcount if we're given a qc to fill */
+	SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
+
 	return address;
 }

Fujii Masao

masao.fujii@oss.nttdata.com

about 5 years ago

In reply to: Seino Yuki (#7)

1 attachment(s)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

On 2020/11/05 23:54, Seino Yuki wrote:

2020-11-02 20:01 に Fujii Masao さんは書きました:
On 2020/11/02 14:02, Yuki Seino wrote:

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

+1.
I checked the patch and there were no problems.
+        PG_END_TRY();
+        SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
Isn't it better to call SetQueryCompletion() in ExecRefreshMatView()
instead of ProcessUtilitySlow() (e.g., ExecCreateTableAs() does)?

Regards,
Sorry. I missed it.
I've incorporated your point into this patch.
So the changes to "matview.h" and "utility.c" have been canceld.

We also confirmed that the new patch passed the regression test.

Thanks for updating the patch!

+	/* save the rowcount if we're given a qc to fill */
+	SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);

I added the check "if (qc)" into the above. I also added the following
comments about that we don't display the rowcount in the command
completion tag output though we save it in qc. There is the discussion
related to this topic, at [1]/messages/by-id/aadbfba9-e4bb-9531-6b3a-d13c31c8f4fe@oss.nttdata.com. Thought?

+	 * Save the rowcount so that pg_stat_statements can track the total number
+	 * of rows processed by REFRESH MATERIALIZED VIEW command. Note that we
+	 * still don't display the rowcount in the command completion tag output,
+	 * i.e., the display_rowcount flag of CMDTAG_REFRESH_MATERIALIZED_VIEW
+	 * command tag is left false in cmdtaglist.h. Otherwise, the change of
+	 * completion tag output might break applications using it.

Attached is the updated version of the patch.
Barring no objection, I will commit that.

Regards,

[1]: /messages/by-id/aadbfba9-e4bb-9531-6b3a-d13c31c8f4fe@oss.nttdata.com
/messages/by-id/aadbfba9-e4bb-9531-6b3a-d13c31c8f4fe@oss.nttdata.com

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Attachments:

pg_stat_statements_mt_refresh_v3.patchtext/plain; charset=UTF-8; name=pg_stat_statements_mt_refresh_v3.patch; x-mac-creator=0; x-mac-type=0Download

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0edb134f3..2a303a7f07 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -530,8 +530,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
--- and SELECT INTO
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
 --
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -543,6 +543,7 @@ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
 SELECT generate_series(1, 10) c INTO pgss_select_into;
 COPY pgss_ctas (a, b) FROM STDIN;
 CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
 BEGIN;
 DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
 FETCH NEXT pgss_cursor;
@@ -586,10 +587,11 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
  FETCH FORWARD 5 pgss_cursor                                                         |     0 |     1 |    5
  FETCH FORWARD ALL pgss_cursor                                                       |     0 |     1 |    7
  FETCH NEXT pgss_cursor                                                              |     0 |     1 |    1
+ REFRESH MATERIALIZED VIEW pgss_matv                                                 |     0 |     1 |   13
  SELECT generate_series(1, 10) c INTO pgss_select_into                               |     0 |     1 |   10
  SELECT pg_stat_statements_reset()                                                   |     0 |     1 |    1
  SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |     0 |    0
-(12 rows)
+(13 rows)
 
 --
 -- Track user activity and reset them
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1eac9edaee..dd963c4644 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -1171,13 +1171,14 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		INSTR_TIME_SUBTRACT(duration, start);
 
 		/*
-		 * Track the total number of rows retrieved or affected by
-		 * the utility statements of COPY, FETCH, CREATE TABLE AS,
-		 * CREATE MATERIALIZED VIEW and SELECT INTO.
+		 * Track the total number of rows retrieved or affected by the utility
+		 * statements of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED
+		 * VIEW, REFRESH MATERIALIZED VIEW and SELECT INTO.
 		 */
 		rows = (qc && (qc->commandTag == CMDTAG_COPY ||
 					   qc->commandTag == CMDTAG_FETCH ||
-					   qc->commandTag == CMDTAG_SELECT)) ?
+					   qc->commandTag == CMDTAG_SELECT ||
+					   qc->commandTag == CMDTAG_REFRESH_MATERIALIZED_VIEW)) ?
 			qc->nprocessed : 0;
 
 		/* calc differences of buffer counters. */
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 996a24a293..e9f5bb84e3 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -252,8 +252,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
--- and SELECT INTO
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
 --
 SELECT pg_stat_statements_reset();
 
@@ -265,6 +265,7 @@ COPY pgss_ctas (a, b) FROM STDIN;
 13	copy
 \.
 CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
 BEGIN;
 DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
 FETCH NEXT pgss_cursor;
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index f80a9e96a9..cfc63915f3 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -356,6 +356,17 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 
 	ObjectAddressSet(address, RelationRelationId, matviewOid);
 
+	/*
+	 * Save the rowcount so that pg_stat_statements can track the total number
+	 * of rows processed by REFRESH MATERIALIZED VIEW command. Note that we
+	 * still don't display the rowcount in the command completion tag output,
+	 * i.e., the display_rowcount flag of CMDTAG_REFRESH_MATERIALIZED_VIEW
+	 * command tag is left false in cmdtaglist.h. Otherwise, the change of
+	 * completion tag output might break applications using it.
+	 */
+	if (qc)
+		SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
+
 	return address;
 }

Fujii Masao

masao.fujii@oss.nttdata.com

about 5 years ago

In reply to: Fujii Masao (#8)

Re: enable pg_stat_statements to track rows processed by REFRESH MATERIALIZED VIEW

On 2020/11/10 17:29, Fujii Masao wrote:

On 2020/11/05 23:54, Seino Yuki wrote:
2020-11-02 20:01 に Fujii Masao さんは書きました:
On 2020/11/02 14:02, Yuki Seino wrote:

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

+1.
I checked the patch and there were no problems.
+        PG_END_TRY();
+        SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
Isn't it better to call SetQueryCompletion() in ExecRefreshMatView()
instead of ProcessUtilitySlow() (e.g., ExecCreateTableAs() does)?

Regards,
Sorry. I missed it.
I've incorporated your point into this patch.
So the changes to "matview.h" and "utility.c" have been canceld.

We also confirmed that the new patch passed the regression test.
Thanks for updating the patch!
+    /* save the rowcount if we're given a qc to fill */
+    SetQueryCompletion(qc, CMDTAG_REFRESH_MATERIALIZED_VIEW, processed);
I added the check "if (qc)" into the above. I also added the following
comments about that we don't display the rowcount in the command
completion tag output though we save it in qc. There is the discussion
related to this topic, at [1]. Thought?
+     * Save the rowcount so that pg_stat_statements can track the total number
+     * of rows processed by REFRESH MATERIALIZED VIEW command. Note that we
+     * still don't display the rowcount in the command completion tag output,
+     * i.e., the display_rowcount flag of CMDTAG_REFRESH_MATERIALIZED_VIEW
+     * command tag is left false in cmdtaglist.h. Otherwise, the change of
+     * completion tag output might break applications using it.
Attached is the updated version of the patch.
Barring no objection, I will commit that.

Pushed. Thanks!

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION