Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit6023b7e

Browse files
committed
pg_stat_statements: track number of rows processed by some utility commands.
This commit makes pg_stat_statements track the total numberof rows retrieved or affected by CREATE TABLE AS, SELECT INTO,CREATE MATERIALIZED VIEW and FETCH commands.Suggested-by: Pascal LegrandAuthor: Fujii MasaoReviewed-by: Asif RehmanDiscussion:https://postgr.es/m/1584293755198-0.post@n3.nabble.com
1 parentb5310e4 commit6023b7e

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, rowsFROM pg_stat_statementsORDER 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+
CREATETABLEpgss_ctasASSELECT a,'ctas' bFROM 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+
11copy
264+
12copy
265+
13copy
266+
\.
267+
CREATE MATERIALIZED VIEW pgss_matvASSELECT*FROM pgss_ctas;
268+
BEGIN;
269+
DECLARE pgss_cursor CURSOR FORSELECT*FROM pgss_matv;
270+
FETCH NEXT pgss_cursor;
271+
FETCH FORWARD5 pgss_cursor;
272+
FETCH FORWARD ALL pgss_cursor;
273+
COMMIT;
274+
275+
SELECT query, plans, calls, rowsFROM pg_stat_statementsORDER 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+
DROPTABLE pgss_ctas;
342+
DROPTABLE pgss_select_into;
316343

317344
--
318345
-- [re]plan counting

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp