Skip to content

Commit 6023b7e

Browse files
MasaoFujiiMasaoFujii
committed
pg_stat_statements: track number of rows processed by some utility commands.
This commit makes pg_stat_statements track the total number of rows retrieved or affected by CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW and FETCH commands. Suggested-by: Pascal Legrand Author: Fujii Masao Reviewed-by: Asif Rehman Discussion: https://postgr.es/m/1584293755198-0.post@n3.nabble.com
1 parent b5310e4 commit 6023b7e

File tree

3 files changed

+102
-1
lines changed

3 files changed

+102
-1
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -528,6 +528,69 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
528528
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
529529
(9 rows)
530530

531+
--
532+
-- Track the total number of rows retrieved or affected by the utility
533+
-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
534+
-- and SELECT INTO
535+
--
536+
SELECT pg_stat_statements_reset();
537+
pg_stat_statements_reset
538+
--------------------------
539+
540+
(1 row)
541+
542+
CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
543+
SELECT generate_series(1, 10) c INTO pgss_select_into;
544+
COPY pgss_ctas (a, b) FROM STDIN;
545+
CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
546+
BEGIN;
547+
DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
548+
FETCH NEXT pgss_cursor;
549+
a | b
550+
---+------
551+
1 | ctas
552+
(1 row)
553+
554+
FETCH FORWARD 5 pgss_cursor;
555+
a | b
556+
---+------
557+
2 | ctas
558+
3 | ctas
559+
4 | ctas
560+
5 | ctas
561+
6 | ctas
562+
(5 rows)
563+
564+
FETCH FORWARD ALL pgss_cursor;
565+
a | b
566+
----+------
567+
7 | ctas
568+
8 | ctas
569+
9 | ctas
570+
10 | ctas
571+
11 | copy
572+
12 | copy
573+
13 | copy
574+
(7 rows)
575+
576+
COMMIT;
577+
SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
578+
query | plans | calls | rows
579+
-------------------------------------------------------------------------------------+-------+-------+------
580+
BEGIN | 0 | 1 | 0
581+
COMMIT | 0 | 1 | 0
582+
COPY pgss_ctas (a, b) FROM STDIN | 0 | 1 | 3
583+
CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 0 | 1 | 13
584+
CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 0 | 1 | 10
585+
DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 0 | 1 | 0
586+
FETCH FORWARD 5 pgss_cursor | 0 | 1 | 5
587+
FETCH FORWARD ALL pgss_cursor | 0 | 1 | 7
588+
FETCH NEXT pgss_cursor | 0 | 1 | 1
589+
SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10
590+
SELECT pg_stat_statements_reset() | 0 | 1 | 1
591+
SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0
592+
(12 rows)
593+
531594
--
532595
-- Track user activity and reset them
533596
--
@@ -728,6 +791,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
728791
--
729792
DROP ROLE regress_stats_user1;
730793
DROP ROLE regress_stats_user2;
794+
DROP MATERIALIZED VIEW pgss_matv;
795+
DROP TABLE pgss_ctas;
796+
DROP TABLE pgss_select_into;
731797
--
732798
-- [re]plan counting
733799
--

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1170,7 +1170,15 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
11701170
INSTR_TIME_SET_CURRENT(duration);
11711171
INSTR_TIME_SUBTRACT(duration, start);
11721172

1173-
rows = (qc && qc->commandTag == CMDTAG_COPY) ? qc->nprocessed : 0;
1173+
/*
1174+
* Track the total number of rows retrieved or affected by
1175+
* the utility statements of COPY, FETCH, CREATE TABLE AS,
1176+
* CREATE MATERIALIZED VIEW and SELECT INTO.
1177+
*/
1178+
rows = (qc && (qc->commandTag == CMDTAG_COPY ||
1179+
qc->commandTag == CMDTAG_FETCH ||
1180+
qc->commandTag == CMDTAG_SELECT)) ?
1181+
qc->nprocessed : 0;
11741182

11751183
/* calc differences of buffer counters. */
11761184
memset(&bufusage, 0, sizeof(BufferUsage));

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -250,6 +250,30 @@ DROP FUNCTION PLUS_TWO(INTEGER);
250250

251251
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
252252

253+
--
254+
-- Track the total number of rows retrieved or affected by the utility
255+
-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
256+
-- and SELECT INTO
257+
--
258+
SELECT pg_stat_statements_reset();
259+
260+
CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
261+
SELECT generate_series(1, 10) c INTO pgss_select_into;
262+
COPY pgss_ctas (a, b) FROM STDIN;
263+
11 copy
264+
12 copy
265+
13 copy
266+
\.
267+
CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
268+
BEGIN;
269+
DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
270+
FETCH NEXT pgss_cursor;
271+
FETCH FORWARD 5 pgss_cursor;
272+
FETCH FORWARD ALL pgss_cursor;
273+
COMMIT;
274+
275+
SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
276+
253277
--
254278
-- Track user activity and reset them
255279
--
@@ -313,6 +337,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
313337
--
314338
DROP ROLE regress_stats_user1;
315339
DROP ROLE regress_stats_user2;
340+
DROP MATERIALIZED VIEW pgss_matv;
341+
DROP TABLE pgss_ctas;
342+
DROP TABLE pgss_select_into;
316343

317344
--
318345
-- [re]plan counting

0 commit comments

Comments
 (0)