0001-Refactor-regression-tests-of-pg_stat_statements.patch

From d2fe1efd9397bfdcf2e088022946ec0c3489973d Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 7 Feb 2023 14:33:20 +0900
Subject: [PATCH 1/3] Refactor regression tests of pg_stat_statements

pg_stat_statements.sql acts as the main file for all the core tests of
the module, but things have become a bit hairy over the years as some of
the sub-scenarios tested rely on assumptions that may have been set in a
completely different block, like a GUC setup or a different relation.

This commit refactors the tests of pg_stat_statements a bit, by moving a
few test cases out of pg_stat_statements.sql into their own file, as of:
- Planning-related tests in planning.sql.
- Utilities in utility.sql.

Test scenarios and their results remain the same as the originals.
---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../pg_stat_statements/expected/cleanup.out   |   1 +
 .../expected/pg_stat_statements.out           | 284 ++----------------
 .../pg_stat_statements/expected/planning.out  | 195 ++++++++++++
 .../pg_stat_statements/expected/utility.out   |  72 +++++
 contrib/pg_stat_statements/meson.build        |   3 +
 contrib/pg_stat_statements/sql/cleanup.sql    |   1 +
 .../sql/pg_stat_statements.sql                | 118 +-------
 contrib/pg_stat_statements/sql/planning.sql   |  78 +++++
 contrib/pg_stat_statements/sql/utility.sql    |  34 +++
 10 files changed, 417 insertions(+), 371 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/cleanup.out
 create mode 100644 contrib/pg_stat_statements/expected/planning.out
 create mode 100644 contrib/pg_stat_statements/expected/utility.out
 create mode 100644 contrib/pg_stat_statements/sql/cleanup.sql
 create mode 100644 contrib/pg_stat_statements/sql/planning.sql
 create mode 100644 contrib/pg_stat_statements/sql/utility.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index edc40c8bbf..78dc4c1d07 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -17,7 +17,7 @@ PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
 LDFLAGS_SL += $(filter -lm, $(LIBS))
 
 REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
-REGRESS = pg_stat_statements oldextversions
+REGRESS = pg_stat_statements utility planning cleanup oldextversions
 # Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
 # which typical installcheck users do not have (e.g. buildfarm clients).
 NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/cleanup.out b/contrib/pg_stat_statements/expected/cleanup.out
new file mode 100644
index 0000000000..36bec35c40
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/cleanup.out
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 8c0b2235e8..09ceb6dd2b 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -550,131 +550,10 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 (12 rows)
 
 DROP TABLE pgss_a, pgss_b CASCADE;
---
--- utility commands
---
-SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT 1;
- ?column? 
-----------
-        1
-(1 row)
-
-CREATE INDEX test_b ON test(b);
-DROP TABLE test \;
-DROP TABLE IF EXISTS test \;
-DROP FUNCTION PLUS_ONE(INTEGER);
-NOTICE:  table "test" does not exist, skipping
--- This DROP query uses two different strings, still they count as one entry.
-DROP TABLE IF EXISTS test \;
-Drop Table If Exists test \;
-DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
-NOTICE:  table "test" does not exist, skipping
-NOTICE:  table "test" does not exist, skipping
-NOTICE:  function plus_one(pg_catalog.int4) does not exist, skipping
-DROP FUNCTION PLUS_TWO(INTEGER);
--- This SET query uses two different strings, still they count as one entry.
-SET work_mem = '1MB';
-Set work_mem = '1MB';
-SET work_mem = '2MB';
-RESET work_mem;
-SET enable_seqscan = off;
-SET enable_seqscan = on;
-RESET enable_seqscan;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                    query                                     | calls | rows 
-------------------------------------------------------------------------------+-------+------
- CREATE INDEX test_b ON test(b)                                               |     1 |    0
- DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER)                                    |     1 |    0
- DROP FUNCTION PLUS_ONE(INTEGER)                                              |     1 |    0
- DROP FUNCTION PLUS_TWO(INTEGER)                                              |     1 |    0
- DROP TABLE IF EXISTS test                                                    |     3 |    0
- DROP TABLE test                                                              |     1 |    0
- RESET enable_seqscan                                                         |     1 |    0
- RESET work_mem                                                               |     1 |    0
- SELECT $1                                                                    |     1 |    1
- SELECT pg_stat_statements_reset()                                            |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
- SET enable_seqscan = off                                                     |     1 |    0
- SET enable_seqscan = on                                                      |     1 |    0
- SET work_mem = '1MB'                                                         |     2 |    0
- SET work_mem = '2MB'                                                         |     1 |    0
-(15 rows)
-
---
--- Track the total number of rows retrieved or affected by the utility
--- 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 
---------------------------
- 
-(1 row)
-
-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;
- a |  b   
----+------
- 1 | ctas
-(1 row)
-
-FETCH FORWARD 5 pgss_cursor;
- a |  b   
----+------
- 2 | ctas
- 3 | ctas
- 4 | ctas
- 5 | ctas
- 6 | ctas
-(5 rows)
-
-FETCH FORWARD ALL pgss_cursor;
- a  |  b   
-----+------
-  7 | ctas
-  8 | ctas
-  9 | ctas
- 10 | ctas
- 11 | copy
- 12 | copy
- 13 | copy
-(7 rows)
-
-COMMIT;
-SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                        query                                        | plans | calls | rows 
--------------------------------------------------------------------------------------+-------+-------+------
- BEGIN                                                                               |     0 |     1 |    0
- COMMIT                                                                              |     0 |     1 |    0
- COPY pgss_ctas (a, b) FROM STDIN                                                    |     0 |     1 |    3
- CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas                       |     0 |     1 |   13
- CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a          |     0 |     1 |   10
- DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv                              |     0 |     1 |    0
- 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
-(13 rows)
-
 --
 -- Track user activity and reset them
 --
+SET pg_stat_statements.track_utility = TRUE;
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
 --------------------------
@@ -872,84 +751,6 @@ 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
---
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-CREATE TABLE test ();
-PREPARE prep1 AS SELECT COUNT(*) FROM test;
-EXECUTE prep1;
- count 
--------
-     0
-(1 row)
-
-EXECUTE prep1;
- count 
--------
-     0
-(1 row)
-
-EXECUTE prep1;
- count 
--------
-     0
-(1 row)
-
-ALTER TABLE test ADD COLUMN x int;
-EXECUTE prep1;
- count 
--------
-     0
-(1 row)
-
-SELECT 42;
- ?column? 
-----------
-       42
-(1 row)
-
-SELECT 42;
- ?column? 
-----------
-       42
-(1 row)
-
-SELECT 42;
- ?column? 
-----------
-       42
-(1 row)
-
-SELECT query, plans, calls, rows FROM pg_stat_statements
-  WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
-                          query                           | plans | calls | rows 
-----------------------------------------------------------+-------+-------+------
- ALTER TABLE test ADD COLUMN x int                        |     0 |     1 |    0
- CREATE TABLE test ()                                     |     0 |     1 |    0
- SELECT $1                                                |     3 |     3 |    3
- SELECT pg_stat_statements_reset()                        |     0 |     1 |    1
- SELECT query, plans, calls, rows FROM pg_stat_statements+|     1 |     0 |    0
-   WHERE query NOT LIKE $1 ORDER BY query COLLATE "C"     |       |       | 
-(5 rows)
-
--- for the prepared statement we expect at least one replan, but cache
--- invalidations could force more
-SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
-  WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
-                   query                    | plans_ok | calls | rows 
---------------------------------------------+----------+-------+------
- PREPARE prep1 AS SELECT COUNT(*) FROM test | t        |     4 |    4
-(1 row)
-
 --
 -- access to pg_stat_statements_info view
 --
@@ -965,67 +766,6 @@ SELECT dealloc FROM pg_stat_statements_info;
        0
 (1 row)
 
---
--- top level handling
---
-SET pg_stat_statements.track = 'top';
-DELETE FROM test;
-DO $$
-BEGIN
-    DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-         query         | toplevel | plans | calls 
------------------------+----------+-------+-------
- DELETE FROM test      | t        |     1 |     1
- DO $$                +| t        |     0 |     1
- BEGIN                +|          |       | 
-     DELETE FROM test;+|          |       | 
- END;                 +|          |       | 
- $$ LANGUAGE plpgsql   |          |       | 
-(2 rows)
-
-SET pg_stat_statements.track = 'all';
-DELETE FROM test;
-DO $$
-BEGIN
-    DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-         query         | toplevel | plans | calls 
------------------------+----------+-------+-------
- DELETE FROM test      | f        |     1 |     1
- DELETE FROM test      | t        |     2 |     2
- DO $$                +| t        |     0 |     2
- BEGIN                +|          |       | 
-     DELETE FROM test;+|          |       | 
- END;                 +|          |       | 
- $$ LANGUAGE plpgsql   |          |       | 
-(3 rows)
-
--- FROM [ONLY]
-CREATE TABLE tbl_inh(id integer);
-CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
-INSERT INTO tbl_inh_1 SELECT 1;
-SELECT * FROM tbl_inh;
- id 
-----
-  1
-(1 row)
-
-SELECT * FROM ONLY tbl_inh;
- id 
-----
-(0 rows)
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
- count 
--------
-     2
-(1 row)
-
 -- WITH TIES
 CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10);
 SELECT *
@@ -1156,4 +896,24 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
      2
 (1 row)
 
-DROP EXTENSION pg_stat_statements;
+-- FROM [ONLY]
+CREATE TABLE tbl_inh(id integer);
+CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
+INSERT INTO tbl_inh_1 SELECT 1;
+SELECT * FROM tbl_inh;
+ id 
+----
+  1
+(1 row)
+
+SELECT * FROM ONLY tbl_inh;
+ id 
+----
+(0 rows)
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
+ count 
+-------
+     2
+(1 row)
+
diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out
new file mode 100644
index 0000000000..216e46ea2f
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/planning.out
@@ -0,0 +1,195 @@
+--
+-- Information related to planning
+--
+-- These tests require track_planning to be enabled.
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
+--
+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;
+ a |  b   
+---+------
+ 1 | ctas
+(1 row)
+
+FETCH FORWARD 5 pgss_cursor;
+ a |  b   
+---+------
+ 2 | ctas
+ 3 | ctas
+ 4 | ctas
+ 5 | ctas
+ 6 | ctas
+(5 rows)
+
+FETCH FORWARD ALL pgss_cursor;
+ a  |  b   
+----+------
+  7 | ctas
+  8 | ctas
+  9 | ctas
+ 10 | ctas
+ 11 | copy
+ 12 | copy
+ 13 | copy
+(7 rows)
+
+COMMIT;
+SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                        query                                        | plans | calls | rows 
+-------------------------------------------------------------------------------------+-------+-------+------
+ BEGIN                                                                               |     0 |     1 |    0
+ COMMIT                                                                              |     0 |     1 |    0
+ COPY pgss_ctas (a, b) FROM STDIN                                                    |     0 |     1 |    3
+ CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas                       |     0 |     1 |   13
+ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a          |     0 |     1 |   10
+ DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv                              |     0 |     1 |    0
+ 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
+(13 rows)
+
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
+--
+-- [re]plan counting
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+CREATE TABLE test ();
+PREPARE prep1 AS SELECT COUNT(*) FROM test;
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+ALTER TABLE test ADD COLUMN x int;
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+SELECT 42;
+ ?column? 
+----------
+       42
+(1 row)
+
+SELECT 42;
+ ?column? 
+----------
+       42
+(1 row)
+
+SELECT 42;
+ ?column? 
+----------
+       42
+(1 row)
+
+SELECT query, plans, calls, rows FROM pg_stat_statements
+  WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+                          query                           | plans | calls | rows 
+----------------------------------------------------------+-------+-------+------
+ ALTER TABLE test ADD COLUMN x int                        |     0 |     1 |    0
+ CREATE TABLE test ()                                     |     0 |     1 |    0
+ SELECT $1                                                |     3 |     3 |    3
+ SELECT pg_stat_statements_reset()                        |     0 |     1 |    1
+ SELECT query, plans, calls, rows FROM pg_stat_statements+|     1 |     0 |    0
+   WHERE query NOT LIKE $1 ORDER BY query COLLATE "C"     |       |       | 
+(5 rows)
+
+-- for the prepared statement we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
+  WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+                   query                    | plans_ok | calls | rows 
+--------------------------------------------+----------+-------+------
+ PREPARE prep1 AS SELECT COUNT(*) FROM test | t        |     4 |    4
+(1 row)
+
+--
+-- top level handling
+--
+SET pg_stat_statements.track = 'top';
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+DELETE FROM test;
+DO $$
+BEGIN
+    DELETE FROM test;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+         query         | toplevel | plans | calls 
+-----------------------+----------+-------+-------
+ DELETE FROM test      | t        |     1 |     1
+ DO $$                +| t        |     0 |     1
+ BEGIN                +|          |       | 
+     DELETE FROM test;+|          |       | 
+ END;                 +|          |       | 
+ $$ LANGUAGE plpgsql   |          |       | 
+(2 rows)
+
+SET pg_stat_statements.track = 'all';
+DELETE FROM test;
+DO $$
+BEGIN
+    DELETE FROM test;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+         query         | toplevel | plans | calls 
+-----------------------+----------+-------+-------
+ DELETE FROM test      | f        |     1 |     1
+ DELETE FROM test      | t        |     2 |     2
+ DO $$                +| t        |     0 |     2
+ BEGIN                +|          |       | 
+     DELETE FROM test;+|          |       | 
+ END;                 +|          |       | 
+ $$ LANGUAGE plpgsql   |          |       | 
+(3 rows)
+
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
new file mode 100644
index 0000000000..8d3bb7bf6d
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -0,0 +1,72 @@
+--
+-- Utility commands
+--
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+CREATE TEMP TABLE test (a int, b char(20));
+CREATE INDEX test_b ON test(b);
+DROP TABLE test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION PLUS_ONE(INTEGER);
+NOTICE:  table "test" does not exist, skipping
+-- This DROP query uses two different strings, still they count as one entry.
+DROP TABLE IF EXISTS test \;
+Drop Table If Exists test \;
+DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
+NOTICE:  table "test" does not exist, skipping
+NOTICE:  table "test" does not exist, skipping
+NOTICE:  function plus_one(pg_catalog.int4) does not exist, skipping
+DROP FUNCTION PLUS_TWO(INTEGER);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                   query                    | calls | rows 
+--------------------------------------------+-------+------
+ CREATE INDEX test_b ON test(b)             |     1 |    0
+ CREATE TEMP TABLE test (a int, b char(20)) |     1 |    0
+ DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER)  |     1 |    0
+ DROP FUNCTION PLUS_ONE(INTEGER)            |     1 |    0
+ DROP FUNCTION PLUS_TWO(INTEGER)            |     1 |    0
+ DROP TABLE IF EXISTS test                  |     3 |    0
+ DROP TABLE test                            |     1 |    0
+ SELECT $1                                  |     1 |    1
+ SELECT pg_stat_statements_reset()          |     1 |    1
+(9 rows)
+
+-- SET statements.
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+               query               | calls | rows 
+-----------------------------------+-------+------
+ RESET enable_seqscan              |     1 |    0
+ RESET work_mem                    |     1 |    0
+ SELECT pg_stat_statements_reset() |     1 |    1
+ SET enable_seqscan = off          |     1 |    0
+ SET enable_seqscan = on           |     1 |    0
+ SET work_mem = '1MB'              |     2 |    0
+ SET work_mem = '2MB'              |     1 |    0
+(7 rows)
+
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 508b53b4a2..64a6c0a58d 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -41,6 +41,9 @@ tests += {
   'regress': {
     'sql': [
       'pg_stat_statements',
+      'utility',
+      'planning',
+      'cleanup',
       'oldextversions',
     ],
     'regress_args': ['--temp-config', files('pg_stat_statements.conf')],
diff --git a/contrib/pg_stat_statements/sql/cleanup.sql b/contrib/pg_stat_statements/sql/cleanup.sql
new file mode 100644
index 0000000000..36bec35c40
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/cleanup.sql
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index cebde7392b..168bb4b46a 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -254,61 +254,10 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 DROP TABLE pgss_a, pgss_b CASCADE;
 
---
--- utility commands
---
-SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
-
-SELECT 1;
-CREATE INDEX test_b ON test(b);
-DROP TABLE test \;
-DROP TABLE IF EXISTS test \;
-DROP FUNCTION PLUS_ONE(INTEGER);
--- This DROP query uses two different strings, still they count as one entry.
-DROP TABLE IF EXISTS test \;
-Drop Table If Exists test \;
-DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
-DROP FUNCTION PLUS_TWO(INTEGER);
--- This SET query uses two different strings, still they count as one entry.
-SET work_mem = '1MB';
-Set work_mem = '1MB';
-SET work_mem = '2MB';
-RESET work_mem;
-SET enable_seqscan = off;
-SET enable_seqscan = on;
-RESET enable_seqscan;
-
-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,
--- REFRESH 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;
-REFRESH MATERIALIZED VIEW pgss_matv;
-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
 --
+SET pg_stat_statements.track_utility = TRUE;
 SELECT pg_stat_statements_reset();
 CREATE ROLE regress_stats_user1;
 CREATE ROLE regress_stats_user2;
@@ -369,30 +318,6 @@ 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
---
-SELECT pg_stat_statements_reset();
-CREATE TABLE test ();
-PREPARE prep1 AS SELECT COUNT(*) FROM test;
-EXECUTE prep1;
-EXECUTE prep1;
-EXECUTE prep1;
-ALTER TABLE test ADD COLUMN x int;
-EXECUTE prep1;
-SELECT 42;
-SELECT 42;
-SELECT 42;
-SELECT query, plans, calls, rows FROM pg_stat_statements
-  WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
--- for the prepared statement we expect at least one replan, but cache
--- invalidations could force more
-SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
-  WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
 
 --
 -- access to pg_stat_statements_info view
@@ -400,37 +325,6 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta
 SELECT pg_stat_statements_reset();
 SELECT dealloc FROM pg_stat_statements_info;
 
---
--- top level handling
---
-SET pg_stat_statements.track = 'top';
-DELETE FROM test;
-DO $$
-BEGIN
-    DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-
-SET pg_stat_statements.track = 'all';
-DELETE FROM test;
-DO $$
-BEGIN
-    DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-
--- FROM [ONLY]
-CREATE TABLE tbl_inh(id integer);
-CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
-INSERT INTO tbl_inh_1 SELECT 1;
-
-SELECT * FROM tbl_inh;
-SELECT * FROM ONLY tbl_inh;
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
-
 -- WITH TIES
 CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10);
 SELECT *
@@ -473,4 +367,12 @@ SELECT (
 
 SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
 
-DROP EXTENSION pg_stat_statements;
+-- FROM [ONLY]
+CREATE TABLE tbl_inh(id integer);
+CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
+INSERT INTO tbl_inh_1 SELECT 1;
+
+SELECT * FROM tbl_inh;
+SELECT * FROM ONLY tbl_inh;
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql
new file mode 100644
index 0000000000..93fa7eb45d
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/planning.sql
@@ -0,0 +1,78 @@
+--
+-- Information related to planning
+--
+
+-- These tests require track_planning to be enabled.
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
+--
+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;
+REFRESH MATERIALIZED VIEW pgss_matv;
+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";
+
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
+
+--
+-- [re]plan counting
+--
+SELECT pg_stat_statements_reset();
+CREATE TABLE test ();
+PREPARE prep1 AS SELECT COUNT(*) FROM test;
+EXECUTE prep1;
+EXECUTE prep1;
+EXECUTE prep1;
+ALTER TABLE test ADD COLUMN x int;
+EXECUTE prep1;
+SELECT 42;
+SELECT 42;
+SELECT 42;
+SELECT query, plans, calls, rows FROM pg_stat_statements
+  WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+-- for the prepared statement we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
+  WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+
+--
+-- top level handling
+--
+SET pg_stat_statements.track = 'top';
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+DELETE FROM test;
+DO $$
+BEGIN
+    DELETE FROM test;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+
+SET pg_stat_statements.track = 'all';
+DELETE FROM test;
+DO $$
+BEGIN
+    DELETE FROM test;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
new file mode 100644
index 0000000000..163f0a3069
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -0,0 +1,34 @@
+--
+-- Utility commands
+--
+
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+SELECT 1;
+CREATE TEMP TABLE test (a int, b char(20));
+CREATE INDEX test_b ON test(b);
+DROP TABLE test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION PLUS_ONE(INTEGER);
+-- This DROP query uses two different strings, still they count as one entry.
+DROP TABLE IF EXISTS test \;
+Drop Table If Exists test \;
+DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- SET statements.
+SELECT pg_stat_statements_reset();
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- 
2.39.1