From 6023b7ea717ca04cf1bd53709d9c862db07eaefb Mon Sep 17 00:00:00 2001 From: Fujii Masao Date: Wed, 29 Jul 2020 23:21:55 +0900 Subject: 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 --- .../pg_stat_statements/sql/pg_stat_statements.sql | 27 ++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'contrib/pg_stat_statements/sql') diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index 6ed8e380280..996a24a293c 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -250,6 +250,30 @@ DROP FUNCTION PLUS_TWO(INTEGER); 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 +-- +SELECT pg_stat_statements_reset(); + +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; +11 copy +12 copy +13 copy +\. +CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas; +BEGIN; +DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv; +FETCH NEXT pgss_cursor; +FETCH FORWARD 5 pgss_cursor; +FETCH FORWARD ALL pgss_cursor; +COMMIT; + +SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- -- Track user activity and reset them -- @@ -313,6 +337,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- DROP ROLE regress_stats_user1; DROP ROLE regress_stats_user2; +DROP MATERIALIZED VIEW pgss_matv; +DROP TABLE pgss_ctas; +DROP TABLE pgss_select_into; -- -- [re]plan counting -- cgit v1.2.3