Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 6023b7e

Browse filesBrowse 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
Copy full SHA for 6023b7e

File tree

Expand file treeCollapse file tree

3 files changed

+102
-1
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+102
-1
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/expected/pg_stat_statements.out
+66Lines 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

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/pg_stat_statements.c
+9-1Lines 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

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/sql/pg_stat_statements.sql
+27Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.