Subject: Normalization of utility queries in pg_stat_statements

From: Michael Paquier <[email-masked]>
Date: Wed, 8 Feb 2023 12:05:24 +0900

Hi all,
(Adding Bertrand in CC.)

$subject is a follow-up of the automation of query jumbling for
utilities and DDLs, and attached is a set of patches that apply
normalization to DDL queries across the board, for all utilities.

This relies on tracking the location of A_Const nodes while removing
from the query jumbling computation the values attached to the node,
as as utility queries can show be stored as normalized in
pg_stat_statements with some $N parameters.  The main case behind
doing that is of course monitoring, where we have seen some user
instances willing to get more information but see pg_stat_statements
as a bottleneck because the query ID of utility queries are based on
the computation of their string, and is value-sensitive.  That's the
case mentioned by Bertrand Drouvot for CALL and SET where workloads
full of these easily bloat pg_stat_statements, where we concluded
about more automation in this area (so here it is):
https://www.postgresql.org/message-id/36e5bffe-e989-194f-85c8-06e7bc88e6f7%40amazon.com

For example, this makes possible the following grouping:
- CALL func(1,2); CALL func(1,3); => CALL func($1,$2)
- EXPLAIN SELECT 1; EXPLAIN SELECT 1; => EXPLAIN SELECT $1;
- CREATE MATERIALIZED VIEW aam AS SELECT 1; becomes "CREATE
MATERIALIZED VIEW aam AS SELECT $1".

Query jumbling for DDLs and utilities happens now automatically, still
are not represented correctly in pg_stat_statements (one bit of
documentation I missed previously refers to the fact that these depend
on their query strings, which is not the case yet).

By the way, while looking at all that, I have really underestimated
the use of Const nodes in utilities, as some queries can finish with
the same query ID even if different values are stored in a query,
still don't show up as normalized in pg_stat_statements, so the
current state of HEAD is not good, though you would need to use the
same object name to a conflict for most of them.  So that's my mistake
here with 3db72eb.  If folks think that we'd better have a revert of
this automated query jumbling facility based on this argument, that
would be fine for me, as well.  The main case I have noticed in this
area is EXPLAIN, by the way.  Note that it is actually easy to move to
the ~15 approach of having a query ID depending on the Const node
values for DDLs, by having a custom implementation in
queryjumblefuncs.c for Const nodes, where we apply the constant value
and don't store a location for normalization if a query has a utility
once this information is stored in a JumbleState.

This rule influences various DDLs, as well, once it gets applied
across the board, and it's been some work to identify all of them, but
I think that I have caught them all as the regression database offers
all the possible patterns:
- CREATE VIEW, CTAS, CREATE MATERIALIZED VIEW which have Const nodes
depending on their attached queries, for various clauses.
- ALTER TABLE/INDEX/FOREIGN with DEFAULT, SET components.
- CREATE TABLE with partition bounds.
- BEGIN and ABORT, with transaction commands getting grouped
together.

The attached patch set includes as a set of regression tests for
pg_stat_statements for *all* the utility queries that have either
Const or A_Const nodes, so as one can see the effect that all this
stuff has.  This is based on a diff of the contents of
pg_stat_statements on the regression database once all these
normalization rules are applied.

Compilation of a Const can also be made depending on the type node.
However, all that makes no sense if applying the same normalization
rules to all the queries across the board, because all the queries
would follow the same rules.  That's the critical bit IMO.  From what
I get, the bloat of pg_stat_statements for all utilities is something
that would be helpful for all such queries, still different things
could be done on a per-node basis.  Perhaps this is too aggressive as
it is and people don't like it, though, so feedback is welcome.  I'd
like to think that maximizing grouping is nice though, because it
leads to no actual loss of information on the workload pattern for the
queries involved, AFAIU.  This sentence may be overoptimistic.

So, attached is a patch set, that does the following:
- 0001 is a refactoring of the regression tests of
pg_stat_statements by splitting a bit the tests.  I bumped into that
while getting confused at how the tests are now when it comes to the
handling of utilities and track_planning, where these tests silently
rely on other parts of the same file with different GUC settings.
This refactoring is useful on its own, IMO, and the tests show the
same output as previously.
- 0002 is the addition of tests in pg_stat_statements for all the DDL
and utility patterns that make use of Const and A_Const nodes.  Even
if query jumbling of utilities is done through their text string or
their nodes, this is also useful.
- 0003 is the code of the feature, that switches pg_stat_statements to
properly normalize utility queries, with a modification to A_Const so
as normalization can be applied to it.  With the generation of the
code for query jumbling being automated based on the node definitions,
this is straight-forward as a code change, but the changes are
basically impossible to track without all the patterns tracked by
0002.

Thoughts and comments are welcome.  0001 and 0002 are useful on their
own to keep track of utilities that use Const and A_Const after going
through the query jumbling, even if an approach based on query string
or the automated query jumbling for utilities is used (the query
string approach a bit its value).  I'll add that to the next commit
fest.

Thanks,
--
Michael
From d2fe1efd9397bfdcf2e088022946ec0c3489973d Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
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

From de7d876c91ce6b80d903d93b1eb6b6c71476a62c Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 8 Feb 2023 10:34:38 +0900
Subject: [PATCH 2/3] Add more test for utility queries in pg_stat_statements

This adds coverage for utility statements that are directly impacted by
the normalization of their internal queries, showing the different in
older versions of Postgres and what's on HEAD now that all the utility
nodes are jumbled through their nodes.

This also provides coverage when more normalization is applied to
A_Const and pg_stat_statements, that should check all the DDL patterns
with nodes related to utilities that make use of constants.
---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../pg_stat_statements/expected/cursors.out   |  70 ++++
 .../expected/pg_stat_statements.out           |   1 +
 .../pg_stat_statements/expected/utility.out   | 396 ++++++++++++++++--
 contrib/pg_stat_statements/meson.build        |   1 +
 contrib/pg_stat_statements/sql/cursors.sql    |  30 ++
 .../sql/pg_stat_statements.sql                |   1 +
 contrib/pg_stat_statements/sql/utility.sql    | 207 ++++++++-
 8 files changed, 656 insertions(+), 52 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/cursors.out
 create mode 100644 contrib/pg_stat_statements/sql/cursors.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 78dc4c1d07..6a4d134c3a 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 utility planning cleanup oldextversions
+REGRESS = pg_stat_statements cursors 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/cursors.out b/contrib/pg_stat_statements/expected/cursors.out
new file mode 100644
index 0000000000..b31a4c77bb
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/cursors.out
@@ -0,0 +1,70 @@
+--
+-- Cursors
+--
+-- 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)
+
+-- DECLARE
+-- SELECT is normalized.
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1;
+CLOSE cursor_stats_1;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+CLOSE cursor_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                        query                         | calls | rows 
+------------------------------------------------------+-------+------
+ CLOSE cursor_stats_1                                 |     2 |    0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 |     2 |    0
+ SELECT pg_stat_statements_reset()                    |     1 |    1
+(3 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- FETCH
+BEGIN;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3;
+FETCH 1 IN cursor_stats_1;
+ ?column? 
+----------
+        2
+(1 row)
+
+FETCH 1 IN cursor_stats_2;
+ ?column? 
+----------
+        3
+(1 row)
+
+CLOSE cursor_stats_1;
+CLOSE cursor_stats_2;
+COMMIT;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                        query                         | calls | rows 
+------------------------------------------------------+-------+------
+ BEGIN                                                |     1 |    0
+ CLOSE cursor_stats_1                                 |     1 |    0
+ CLOSE cursor_stats_2                                 |     1 |    0
+ COMMIT                                               |     1 |    0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2 |     1 |    0
+ DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3 |     1 |    0
+ FETCH 1 IN cursor_stats_1                            |     1 |    1
+ FETCH 1 IN cursor_stats_2                            |     1 |    1
+ SELECT pg_stat_statements_reset()                    |     1 |    1
+(9 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 09ceb6dd2b..c7b6035268 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -462,6 +462,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
  SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
 (6 rows)
 
+DROP FUNCTION PLUS_ONE(INTEGER);
 --
 -- queries with locking clauses
 --
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 8d3bb7bf6d..700a37a42e 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -9,47 +9,361 @@ SELECT pg_stat_statements_reset();
  
 (1 row)
 
+-- Use unaligned format for the whole file, easing diff generation on test
+-- additions.
+\pset format unaligned
 SELECT 1;
- ?column? 
-----------
-        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
+-- Tables, indexes, triggers
+CREATE TEMP TABLE tab_stats (a int, b char(20));
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0;
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a';
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b;
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0);
+DROP TABLE tab_stats \;
+DROP TABLE IF EXISTS tab_stats \;
 -- 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);
+DROP TABLE IF EXISTS tab_stats \;
+Drop Table If Exists tab_stats \;
 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
+NOTICE:  table "tab_stats" does not exist, skipping
+NOTICE:  table "tab_stats" does not exist, skipping
+NOTICE:  table "tab_stats" does not exist, skipping
+query|calls|rows
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0)|1|0
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b|1|0
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a'|1|0
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0|1|0
+CREATE TEMP TABLE tab_stats (a int, b char(20))|1|0
+DROP TABLE IF EXISTS tab_stats|3|0
+DROP TABLE tab_stats|1|0
+SELECT $1|1|1
+SELECT pg_stat_statements_reset()|1|1
 (9 rows)
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
 
+(1 row)
+-- Partitions
+CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a);
+CREATE TABLE pt_stats1 (a int, b int);
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100);
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200);
+CREATE INDEX pt_stats_index ON ONLY pt_stats (a);
+CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a);
+ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index;
+DROP TABLE pt_stats;
+-- Views
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b;
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2;
+DROP VIEW view_stats;
+-- Foreign tables
+CREATE FOREIGN DATA WRAPPER wrapper_stats;
+CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats;
+CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats;
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1;
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0);
+DROP FOREIGN TABLE foreign_stats;
+DROP SERVER server_stats;
+DROP FOREIGN DATA WRAPPER wrapper_stats;
+-- Functions
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+  RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
+DROP FUNCTION func_stats;
+-- Rules
+CREATE TABLE tab_rule_stats (a int, b int);
+CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int);
+CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
+  INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2);
+DROP RULE rules_stats ON tab_rule_stats;
+DROP TABLE tab_rule_stats, tab_rule_stats_2;
+-- Types
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2));
+DROP TYPE stats_type;
+-- Triggers
+CREATE TABLE trigger_tab_stats (a int, b int);
+CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
+  AS $$ BEGIN return OLD; end; $$;
+CREATE TRIGGER trigger_tab_stats
+    AFTER UPDATE ON trigger_tab_stats
+    FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+    EXECUTE FUNCTION trigger_func_stats();
+DROP TABLE trigger_tab_stats;
+-- Policies
+CREATE TABLE tab_policy_stats (a int, b int);
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5);
+DROP TABLE tab_policy_stats;
+-- Statistics
+CREATE TABLE tab_expr_stats (a int, b int);
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
+DROP TABLE tab_expr_stats;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1|1|0
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0)|1|0
+ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index|1|0
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100)|1|0
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2|1|0
+CREATE FOREIGN DATA WRAPPER wrapper_stats|1|0
+CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats|1|0
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+  RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL|1|0
+CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
+  AS $$ BEGIN return OLD; end; $$|1|0
+CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a)|1|0
+CREATE INDEX pt_stats_index ON ONLY pt_stats (a)|1|0
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5)|1|0
+CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
+  INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2)|1|0
+CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats|1|0
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats|1|0
+CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a)|1|0
+CREATE TABLE pt_stats1 (a int, b int)|1|0
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200)|1|0
+CREATE TABLE tab_expr_stats (a int, b int)|1|0
+CREATE TABLE tab_policy_stats (a int, b int)|1|0
+CREATE TABLE tab_rule_stats (a int, b int)|1|0
+CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int)|1|0
+CREATE TABLE trigger_tab_stats (a int, b int)|1|0
+CREATE TRIGGER trigger_tab_stats
+    AFTER UPDATE ON trigger_tab_stats
+    FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+    EXECUTE FUNCTION trigger_func_stats()|1|0
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2))|1|0
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b|1|0
+DROP FOREIGN DATA WRAPPER wrapper_stats|1|0
+DROP FOREIGN TABLE foreign_stats|1|0
+DROP FUNCTION func_stats|1|0
+DROP RULE rules_stats ON tab_rule_stats|1|0
+DROP SERVER server_stats|1|0
+DROP TABLE pt_stats|1|0
+DROP TABLE tab_expr_stats|1|0
+DROP TABLE tab_policy_stats|1|0
+DROP TABLE tab_rule_stats, tab_rule_stats_2|1|0
+DROP TABLE trigger_tab_stats|1|0
+DROP TYPE stats_type|1|0
+DROP VIEW view_stats|1|0
+SELECT pg_stat_statements_reset()|1|1
+(39 rows)
+-- Transaction statements
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+BEGIN;
+ABORT;
+BEGIN;
+ROLLBACK;
+-- WORK
+BEGIN WORK;
+COMMIT WORK;
+BEGIN WORK;
+ABORT WORK;
+-- TRANSACTION
+BEGIN TRANSACTION;
+COMMIT TRANSACTION;
+BEGIN TRANSACTION;
+ABORT TRANSACTION;
+-- More isolation levels
+BEGIN TRANSACTION DEFERRABLE;
+COMMIT TRANSACTION AND NO CHAIN;
+BEGIN ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+ABORT|4|0
+BEGIN|6|0
+BEGIN ISOLATION LEVEL SERIALIZABLE|2|0
+BEGIN TRANSACTION DEFERRABLE|1|0
+COMMIT WORK|5|0
+SELECT pg_stat_statements_reset()|1|1
+(6 rows)
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+-- EXPLAIN statements
+-- A Query is used, normalized by the query jumbling.
+EXPLAIN (costs off) SELECT 1;
+QUERY PLAN
+Result
+(1 row)
+EXPLAIN (costs off) SELECT 2;
+QUERY PLAN
+Result
+(1 row)
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3;
+QUERY PLAN
+Function Scan on generate_series tab
+  Filter: (a = 3)
+(2 rows)
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
+QUERY PLAN
+Function Scan on generate_series tab
+  Filter: (a = 7)
+(2 rows)
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+EXPLAIN (costs off) SELECT 1|2|0
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3|2|0
+SELECT pg_stat_statements_reset()|1|1
+(3 rows)
+-- CALL
+CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + j)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+CALL sum_one(3);
+CALL sum_one(199);
+CALL sum_two(1,1);
+CALL sum_two(1,2);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+CALL sum_one(199)|1|0
+CALL sum_one(3)|1|0
+CALL sum_two(1,1)|1|0
+CALL sum_two(1,2)|1|0
+SELECT pg_stat_statements_reset()|1|1
+(5 rows)
+-- COPY
+CREATE TABLE copy_stats (a int, b int);
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+-- Some queries with A_Const nodes.
+COPY (SELECT 1) TO STDOUT;
+1
+COPY (SELECT 2) TO STDOUT;
+2
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT;
+1	1
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT;
+2	2
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT;
+1	2
+2	3
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
+1	4
+2	5
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
+1	4
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT|1|1
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT|1|1
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT|1|1
+COPY (SELECT 1) TO STDOUT|1|1
+COPY (SELECT 2) TO STDOUT|1|1
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT|1|2
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT|1|2
+SELECT pg_stat_statements_reset()|1|1
+(8 rows)
+DROP TABLE copy_stats;
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+-- CREATE TABLE AS
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE TABLE ctas_stats_1 AS SELECT 1 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_1 AS SELECT 2 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP TABLE ctas_stats_2;
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
+DROP TABLE ctas_stats_2;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+CREATE TABLE ctas_stats_1 AS SELECT 1 AS a|2|2
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|2|4
+DROP TABLE ctas_stats_1|2|0
+DROP TABLE ctas_stats_2|2|0
+SELECT pg_stat_statements_reset()|1|1
+(5 rows)
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+-- CREATE MATERIALIZED VIEW
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP MATERIALIZED VIEW matview_stats_1;
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP MATERIALIZED VIEW matview_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|2|2
+DROP MATERIALIZED VIEW matview_stats_1|2|0
+SELECT pg_stat_statements_reset()|1|1
+(3 rows)
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+-- CREATE VIEW
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP VIEW view_stats_1;
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP VIEW view_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+query|calls|rows
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|1|0
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3|1|0
+DROP VIEW view_stats_1|2|0
+SELECT pg_stat_statements_reset()|1|1
+(4 rows)
+SELECT pg_stat_statements_reset();
+pg_stat_statements_reset
+
+(1 row)
+-- Domains
+CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0);
+ALTER DOMAIN domain_stats SET DEFAULT '3';
+ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
+DROP DOMAIN domain_stats;
 -- SET statements.
 SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
+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';
@@ -59,14 +373,12 @@ 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
+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 64a6c0a58d..21bf5ee2e9 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -41,6 +41,7 @@ tests += {
   'regress': {
     'sql': [
       'pg_stat_statements',
+      'cursors',
       'utility',
       'planning',
       'cleanup',
diff --git a/contrib/pg_stat_statements/sql/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql
new file mode 100644
index 0000000000..60894e675b
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/cursors.sql
@@ -0,0 +1,30 @@
+--
+-- Cursors
+--
+
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+-- DECLARE
+-- SELECT is normalized.
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1;
+CLOSE cursor_stats_1;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+CLOSE cursor_stats_1;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- FETCH
+BEGIN;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3;
+FETCH 1 IN cursor_stats_1;
+FETCH 1 IN cursor_stats_2;
+CLOSE cursor_stats_1;
+CLOSE cursor_stats_2;
+COMMIT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 168bb4b46a..d715ec120a 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -222,6 +222,7 @@ SELECT PLUS_ONE(3);
 SELECT PLUS_ONE(1);
 
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+DROP FUNCTION PLUS_ONE(INTEGER);
 
 --
 -- queries with locking clauses
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
index 163f0a3069..f763a8d455 100644
--- a/contrib/pg_stat_statements/sql/utility.sql
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -5,21 +5,210 @@
 -- These tests require track_utility to be enabled.
 SET pg_stat_statements.track_utility = TRUE;
 SELECT pg_stat_statements_reset();
+-- Use unaligned format for the whole file, easing diff generation on test
+-- additions.
+\pset format unaligned
 
 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);
+
+-- Tables, indexes, triggers
+CREATE TEMP TABLE tab_stats (a int, b char(20));
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0;
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a';
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b;
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0);
+DROP TABLE tab_stats \;
+DROP TABLE IF EXISTS tab_stats \;
 -- 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);
+DROP TABLE IF EXISTS tab_stats \;
+Drop Table If Exists tab_stats \;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Partitions
+CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a);
+CREATE TABLE pt_stats1 (a int, b int);
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100);
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200);
+CREATE INDEX pt_stats_index ON ONLY pt_stats (a);
+CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a);
+ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index;
+DROP TABLE pt_stats;
+
+-- Views
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b;
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2;
+DROP VIEW view_stats;
+
+-- Foreign tables
+CREATE FOREIGN DATA WRAPPER wrapper_stats;
+CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats;
+CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats;
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1;
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0);
+DROP FOREIGN TABLE foreign_stats;
+DROP SERVER server_stats;
+DROP FOREIGN DATA WRAPPER wrapper_stats;
+
+-- Functions
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+  RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
+DROP FUNCTION func_stats;
+
+-- Rules
+CREATE TABLE tab_rule_stats (a int, b int);
+CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int);
+CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
+  INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2);
+DROP RULE rules_stats ON tab_rule_stats;
+DROP TABLE tab_rule_stats, tab_rule_stats_2;
+
+-- Types
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2));
+DROP TYPE stats_type;
+
+-- Triggers
+CREATE TABLE trigger_tab_stats (a int, b int);
+CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
+  AS $$ BEGIN return OLD; end; $$;
+CREATE TRIGGER trigger_tab_stats
+    AFTER UPDATE ON trigger_tab_stats
+    FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+    EXECUTE FUNCTION trigger_func_stats();
+DROP TABLE trigger_tab_stats;
+
+-- Policies
+CREATE TABLE tab_policy_stats (a int, b int);
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5);
+DROP TABLE tab_policy_stats;
+
+-- Statistics
+CREATE TABLE tab_expr_stats (a int, b int);
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
+DROP TABLE tab_expr_stats;
 
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
+-- Transaction statements
+SELECT pg_stat_statements_reset();
+BEGIN;
+ABORT;
+BEGIN;
+ROLLBACK;
+-- WORK
+BEGIN WORK;
+COMMIT WORK;
+BEGIN WORK;
+ABORT WORK;
+-- TRANSACTION
+BEGIN TRANSACTION;
+COMMIT TRANSACTION;
+BEGIN TRANSACTION;
+ABORT TRANSACTION;
+-- More isolation levels
+BEGIN TRANSACTION DEFERRABLE;
+COMMIT TRANSACTION AND NO CHAIN;
+BEGIN ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- EXPLAIN statements
+-- A Query is used, normalized by the query jumbling.
+EXPLAIN (costs off) SELECT 1;
+EXPLAIN (costs off) SELECT 2;
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3;
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CALL
+CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + j)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+SELECT pg_stat_statements_reset();
+CALL sum_one(3);
+CALL sum_one(199);
+CALL sum_two(1,1);
+CALL sum_two(1,2);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- COPY
+CREATE TABLE copy_stats (a int, b int);
+SELECT pg_stat_statements_reset();
+-- Some queries with A_Const nodes.
+COPY (SELECT 1) TO STDOUT;
+COPY (SELECT 2) TO STDOUT;
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT;
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT;
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT;
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+DROP TABLE copy_stats;
+SELECT pg_stat_statements_reset();
+
+-- CREATE TABLE AS
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE TABLE ctas_stats_1 AS SELECT 1 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_1 AS SELECT 2 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP TABLE ctas_stats_2;
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
+DROP TABLE ctas_stats_2;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- CREATE MATERIALIZED VIEW
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP MATERIALIZED VIEW matview_stats_1;
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP MATERIALIZED VIEW matview_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- CREATE VIEW
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP VIEW view_stats_1;
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP VIEW view_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Domains
+CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0);
+ALTER DOMAIN domain_stats SET DEFAULT '3';
+ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
+DROP DOMAIN domain_stats;
+
 -- SET statements.
 SELECT pg_stat_statements_reset();
 -- These use two different strings, still they count as one entry.
-- 
2.39.1

From 0156ccb547e6deffc0b2d68ca0377ea71b2e98e7 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 8 Feb 2023 11:00:01 +0900
Subject: [PATCH 3/3] Apply normalization to A_Const and utilities in
 pg_stat_statements

Its value is now ignored and location is stored, so as it is possible to
apply query normalization across more query types:
- SET
- CALL
- COPY TO with queries
- View, matviews and CTAS
- EXPLAIN
- Triggers
- Rules
- Statistics
---
 src/include/nodes/parsenodes.h                |  8 +-
 src/include/nodes/primnodes.h                 |  9 +-
 src/backend/nodes/queryjumblefuncs.c          | 23 +----
 doc/src/sgml/pgstatstatements.sgml            |  7 +-
 .../pg_stat_statements/expected/cursors.out   | 32 +++----
 .../expected/pg_stat_statements.out           |  2 +-
 .../pg_stat_statements/expected/planning.out  |  4 +-
 .../pg_stat_statements/expected/utility.out   | 90 +++++++++----------
 .../pg_stat_statements/pg_stat_statements.c   |  4 +-
 9 files changed, 79 insertions(+), 100 deletions(-)

diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 855da99ec0..d87340d4ac 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3216,14 +3216,18 @@ typedef struct InlineCodeBlock
  * list contains copies of the expressions for all output arguments, in the
  * order of the procedure's declared arguments.  (outargs is never evaluated,
  * but is useful to the caller as a reference for what to assign to.)
+ * The transformed call state is not relevant in the query jumbling, only the
+ * function call is.
  * ----------------------
  */
 typedef struct CallStmt
 {
 	NodeTag		type;
 	FuncCall   *funccall;		/* from the parser */
-	FuncExpr   *funcexpr;		/* transformed call, with only input args */
-	List	   *outargs;		/* transformed output-argument expressions */
+	/* transformed call, with only input args */
+	FuncExpr   *funcexpr pg_node_attr(query_jumble_ignore);
+	/* transformed output-argument expressions */
+	List	   *outargs pg_node_attr(query_jumble_ignore);
 } CallStmt;
 
 typedef struct CallContext
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6d740be5c0..0da482d542 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -128,8 +128,10 @@ typedef struct TableFunc
  * CREATE MATERIALIZED VIEW
  *
  * For CREATE MATERIALIZED VIEW, viewQuery is the parsed-but-not-rewritten
- * SELECT Query for the view; otherwise it's NULL.  (Although it's actually
- * Query*, we declare it as Node* to avoid a forward reference.)
+ * SELECT Query for the view; otherwise it's NULL.  This is irrelevant in
+ * the query jumbling as CreateTableAsStmt already includes a reference to
+ * its own Query, so ignore it.  (Although it's actually Query*, we declare
+ * it as Node* to avoid a forward reference.)
  */
 typedef struct IntoClause
 {
@@ -141,7 +143,8 @@ typedef struct IntoClause
 	List	   *options;		/* options from WITH clause */
 	OnCommitAction onCommit;	/* what do we do at COMMIT? */
 	char	   *tableSpaceName; /* table space to use, or NULL */
-	Node	   *viewQuery;		/* materialized view's SELECT query */
+	/* materialized view's SELECT query */
+	Node	   *viewQuery pg_node_attr(query_jumble_ignore);
 	bool		skipData;		/* true for WITH NO DATA */
 } IntoClause;
 
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index d7fd72d70f..0f08f4c75e 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -323,29 +323,8 @@ _jumbleA_Const(JumbleState *jstate, Node *node)
 	if (!expr->isnull)
 	{
 		JUMBLE_FIELD(val.node.type);
-		switch (nodeTag(&expr->val))
-		{
-			case T_Integer:
-				JUMBLE_FIELD(val.ival.ival);
-				break;
-			case T_Float:
-				JUMBLE_STRING(val.fval.fval);
-				break;
-			case T_Boolean:
-				JUMBLE_FIELD(val.boolval.boolval);
-				break;
-			case T_String:
-				JUMBLE_STRING(val.sval.sval);
-				break;
-			case T_BitString:
-				JUMBLE_STRING(val.bsval.bsval);
-				break;
-			default:
-				elog(ERROR, "unrecognized node type: %d",
-					 (int) nodeTag(&expr->val));
-				break;
-		}
 	}
+	JUMBLE_LOCATION(location);
 }
 
 static void
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index efc36da602..cee9376916 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -489,11 +489,12 @@
    Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
    <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>) are combined into a single
    <structname>pg_stat_statements</structname> entry whenever they have identical query
-   structures according to an internal hash calculation.  Typically, two
+   structures according to an internal hash calculation.  The same rule
+   applies to utility commands (that is, all other commands), and are normalized
+   when they have an identical hash calculation. Typically, two
    queries will be considered the same for this purpose if they are
    semantically equivalent except for the values of literal constants
-   appearing in the query.  Utility commands (that is, all other commands)
-   are compared strictly on the basis of their textual query strings, however.
+   appearing in the query.
   </para>
 
   <note>
diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_stat_statements/expected/cursors.out
index b31a4c77bb..fd2bef87bd 100644
--- a/contrib/pg_stat_statements/expected/cursors.out
+++ b/contrib/pg_stat_statements/expected/cursors.out
@@ -16,11 +16,11 @@ CLOSE cursor_stats_1;
 DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
 CLOSE cursor_stats_1;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                        query                         | calls | rows 
-------------------------------------------------------+-------+------
- CLOSE cursor_stats_1                                 |     2 |    0
- DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 |     2 |    0
- SELECT pg_stat_statements_reset()                    |     1 |    1
+                         query                         | calls | rows 
+-------------------------------------------------------+-------+------
+ CLOSE cursor_stats_1                                  |     2 |    0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 |     2 |    0
+ SELECT pg_stat_statements_reset()                     |     1 |    1
 (3 rows)
 
 SELECT pg_stat_statements_reset();
@@ -49,17 +49,17 @@ CLOSE cursor_stats_1;
 CLOSE cursor_stats_2;
 COMMIT;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                        query                         | calls | rows 
-------------------------------------------------------+-------+------
- BEGIN                                                |     1 |    0
- CLOSE cursor_stats_1                                 |     1 |    0
- CLOSE cursor_stats_2                                 |     1 |    0
- COMMIT                                               |     1 |    0
- DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2 |     1 |    0
- DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3 |     1 |    0
- FETCH 1 IN cursor_stats_1                            |     1 |    1
- FETCH 1 IN cursor_stats_2                            |     1 |    1
- SELECT pg_stat_statements_reset()                    |     1 |    1
+                         query                         | calls | rows 
+-------------------------------------------------------+-------+------
+ BEGIN                                                 |     1 |    0
+ CLOSE cursor_stats_1                                  |     1 |    0
+ CLOSE cursor_stats_2                                  |     1 |    0
+ COMMIT                                                |     1 |    0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 |     1 |    0
+ DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1 |     1 |    0
+ FETCH 1 IN cursor_stats_1                             |     1 |    1
+ FETCH 1 IN cursor_stats_2                             |     1 |    1
+ SELECT pg_stat_statements_reset()                     |     1 |    1
 (9 rows)
 
 SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index c7b6035268..f134805709 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -311,7 +311,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
  wal_records > $2 as wal_records_generated,               +|       |      |                     |                       | 
  wal_records >= rows as wal_records_ge_rows               +|       |      |                     |                       | 
  FROM pg_stat_statements ORDER BY query COLLATE "C"        |       |      |                     |                       | 
- SET pg_stat_statements.track_utility = FALSE              |     1 |    0 | f                   | f                     | t
+ SET pg_stat_statements.track_utility = $1                 |     1 |    0 | f                   | f                     | t
  UPDATE pgss_test SET b = $1 WHERE a > $2                  |     1 |    3 | t                   | t                     | t
 (7 rows)
 
diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out
index 216e46ea2f..33b0550ba6 100644
--- a/contrib/pg_stat_statements/expected/planning.out
+++ b/contrib/pg_stat_statements/expected/planning.out
@@ -57,13 +57,13 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
  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
+ CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) 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 generate_series($1, $2) 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)
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 700a37a42e..7d65753e66 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -32,11 +32,11 @@ NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
 query|calls|rows
-ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0)|1|0
-ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b|1|0
-ALTER TABLE tab_stats ALTER COLUMN b set default 'a'|1|0
-CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0|1|0
-CREATE TEMP TABLE tab_stats (a int, b char(20))|1|0
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> $1)|1|0
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING $1 || b|1|0
+ALTER TABLE tab_stats ALTER COLUMN b set default $1|1|0
+CREATE INDEX index_stats ON tab_stats(b, (b || $1), (b || $2)) WHERE a > $3|1|0
+CREATE TEMP TABLE tab_stats (a int, b char($1))|1|0
 DROP TABLE IF EXISTS tab_stats|3|0
 DROP TABLE tab_stats|1|0
 SELECT $1|1|1
@@ -101,27 +101,27 @@ CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
 DROP TABLE tab_expr_stats;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1|1|0
-ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0)|1|0
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT $1|1|0
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> $1)|1|0
 ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index|1|0
-ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100)|1|0
-ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2|1|0
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM ($1) TO ($2)|1|0
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT $1|1|0
 CREATE FOREIGN DATA WRAPPER wrapper_stats|1|0
 CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats|1|0
-CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+CREATE FUNCTION func_stats(a text DEFAULT $1, b text DEFAULT lower($2))
   RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL|1|0
 CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
   AS $$ BEGIN return OLD; end; $$|1|0
 CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a)|1|0
 CREATE INDEX pt_stats_index ON ONLY pt_stats (a)|1|0
-CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5)|1|0
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = $1) WITH CHECK (b < $2)|1|0
 CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
-  INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2)|1|0
+  INSERT INTO tab_rule_stats_2 VALUES(new.*, $1, $2)|1|0
 CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats|1|0
-CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats|1|0
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, ($1*a), ($2*b) FROM tab_expr_stats|1|0
 CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a)|1|0
 CREATE TABLE pt_stats1 (a int, b int)|1|0
-CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200)|1|0
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM ($1) TO ($2)|1|0
 CREATE TABLE tab_expr_stats (a int, b int)|1|0
 CREATE TABLE tab_policy_stats (a int, b int)|1|0
 CREATE TABLE tab_rule_stats (a int, b int)|1|0
@@ -129,10 +129,10 @@ CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int)|1|0
 CREATE TABLE trigger_tab_stats (a int, b int)|1|0
 CREATE TRIGGER trigger_tab_stats
     AFTER UPDATE ON trigger_tab_stats
-    FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+    FOR EACH ROW WHEN (OLD.a < $1 AND OLD.b < $2 AND $3)
     EXECUTE FUNCTION trigger_func_stats()|1|0
-CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2))|1|0
-CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b|1|0
+CREATE TYPE stats_type as (f1 numeric($1, $2), f2 numeric($3, $4))|1|0
+CREATE VIEW view_stats AS SELECT $1::int AS a, $2::int AS b|1|0
 DROP FOREIGN DATA WRAPPER wrapper_stats|1|0
 DROP FOREIGN TABLE foreign_stats|1|0
 DROP FUNCTION func_stats|1|0
@@ -177,8 +177,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
 ABORT|4|0
 BEGIN|6|0
-BEGIN ISOLATION LEVEL SERIALIZABLE|2|0
-BEGIN TRANSACTION DEFERRABLE|1|0
+BEGIN ISOLATION LEVEL $1|2|0
+BEGIN TRANSACTION $1|1|0
 COMMIT WORK|5|0
 SELECT pg_stat_statements_reset()|1|1
 (6 rows)
@@ -208,8 +208,8 @@ Function Scan on generate_series tab
 (2 rows)
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-EXPLAIN (costs off) SELECT 1|2|0
-EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3|2|0
+EXPLAIN (costs off) SELECT $1|2|0
+EXPLAIN (costs off) SELECT a FROM generate_series($1,$2) AS tab(a) WHERE a = $3|2|0
 SELECT pg_stat_statements_reset()|1|1
 (3 rows)
 -- CALL
@@ -235,12 +235,10 @@ CALL sum_two(1,1);
 CALL sum_two(1,2);
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-CALL sum_one(199)|1|0
-CALL sum_one(3)|1|0
-CALL sum_two(1,1)|1|0
-CALL sum_two(1,2)|1|0
+CALL sum_one($1)|2|0
+CALL sum_two($1,$2)|2|0
 SELECT pg_stat_statements_reset()|1|1
-(5 rows)
+(3 rows)
 -- COPY
 CREATE TABLE copy_stats (a int, b int);
 SELECT pg_stat_statements_reset();
@@ -266,15 +264,12 @@ COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
 1	4
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT|1|1
-COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT|1|1
-COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT|1|1
-COPY (SELECT 1) TO STDOUT|1|1
-COPY (SELECT 2) TO STDOUT|1|1
-COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT|1|2
-COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT|1|2
+COPY (DELETE FROM copy_stats WHERE a = $1 RETURNING *) TO STDOUT|1|1
+COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) TO STDOUT|2|2
+COPY (SELECT $1) TO STDOUT|2|2
+COPY (UPDATE copy_stats SET b = b + $1 RETURNING *) TO STDOUT|2|4
 SELECT pg_stat_statements_reset()|1|1
-(8 rows)
+(5 rows)
 DROP TABLE copy_stats;
 SELECT pg_stat_statements_reset();
 pg_stat_statements_reset
@@ -296,10 +291,10 @@ CREATE TABLE ctas_stats_2 AS
 DROP TABLE ctas_stats_2;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-CREATE TABLE ctas_stats_1 AS SELECT 1 AS a|2|2
+CREATE TABLE ctas_stats_1 AS SELECT $1 AS a|2|2
 CREATE TABLE ctas_stats_2 AS
-  SELECT a AS col1, 2::int AS col2
-    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|2|4
+  SELECT a AS col1, $1::int AS col2
+    FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5|2|4
 DROP TABLE ctas_stats_1|2|0
 DROP TABLE ctas_stats_2|2|0
 SELECT pg_stat_statements_reset()|1|1
@@ -321,8 +316,8 @@ DROP MATERIALIZED VIEW matview_stats_1;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
 CREATE MATERIALIZED VIEW matview_stats_1 AS
-  SELECT a AS col1, 2::int AS col2
-    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|2|2
+  SELECT a AS col1, $1::int AS col2
+    FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5|2|2
 DROP MATERIALIZED VIEW matview_stats_1|2|0
 SELECT pg_stat_statements_reset()|1|1
 (3 rows)
@@ -342,14 +337,11 @@ DROP VIEW view_stats_1;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
 CREATE VIEW view_stats_1 AS 
-  SELECT a AS col1, 2::int AS col2
-    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|1|0
-CREATE VIEW view_stats_1 AS 
-  SELECT a AS col1, 4::int AS col2
-    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3|1|0
+  SELECT a AS col1, $1::int AS col2
+    FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5|2|0
 DROP VIEW view_stats_1|2|0
 SELECT pg_stat_statements_reset()|1|1
-(4 rows)
+(3 rows)
 SELECT pg_stat_statements_reset();
 pg_stat_statements_reset
 
@@ -377,8 +369,6 @@ 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)
+SET enable_seqscan = $1|2|0
+SET work_mem = $1|3|0
+(5 rows)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ad1fe44496..5285c3f7fa 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -836,8 +836,10 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
 	if (query->utilityStmt)
 	{
 		if (pgss_track_utility && !PGSS_HANDLED_UTILITY(query->utilityStmt))
+		{
 			query->queryId = UINT64CONST(0);
-		return;
+			return;
+		}
 	}
 
 	/*
-- 
2.39.1

---

From: Michael Paquier <[email-masked]>
Date: Thu, 16 Feb 2023 09:34:51 +0900

> string approach a bit its value).  I'll add that to the next commit

While wondering about this stuff about the last few days and
discussing with bertrand, I have changed my mind on the point that
there is no need to be that aggressive yet with the normalization of
the A_Const nodes, because the query string normalization of
pg_stat_statements is not prepared yet to handle cases where a A_Const
value uses a non-quoted value with whitespaces.  The two cases where I
saw an impact is on the commands that can define an isolation level:
SET TRANSACTION and BEGIN.

For example, applying normalization to A_Const nodes does the
following as of HEAD:
1) BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE
2) SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL $1 COMMITTED

On top of that, specifying a different isolation level may cause these
commands to be grouped, which is not really cool.  All that could be
done incrementally later on, in 17~ or later depending on the
adjustments that make sense.

Attached is an updated patch set.  0003 is basically the same as v3,
that I have kept around for clarity in case one wants to see the
effect of a A_Const normalization to all the related commands, though
I am not proposing that for an upstream integration.  0002 has been
completed with a couple of commands to track all the commands with
A_Const, so as we never lose sight of what happens.  0004 is what I
think could be done for PG16, where normalization affects only Const.
At the end of the day, this reflects the following commands that use
Const nodes because they use directly queries, so the same rules as
SELECT and DMLs apply to them:
- DECLARE
- EXPLAIN
- CREATE MATERIALIZED VIEW
- CTAS, SELECT INTO

Comments and thoughts welcome.
Thanks,
--
Michael
From eb61fe10d0d7399573ebb3a911aed4114742cf25 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Tue, 7 Feb 2023 14:33:20 +0900
Subject: [PATCH v2 1/4] Refactor regression tests of pg_stat_statements

 

---
--- top level handling
---

 
---
--- utility commands
---

---
--- top level handling
---

From b91faf704700134e7b9b0ee1559d4d57a563e642 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Thu, 16 Feb 2023 09:05:48 +0900
Subject: [PATCH v2 2/4] Add more test for utility queries in
 pg_stat_statements

This also provides coverage when more normalization is applied to
A_Const and pg_stat_statements, that should check all the DDL patterns
with nodes related to utilities that make use of constants.
---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../pg_stat_statements/expected/cursors.out   |  70 +++
 .../expected/pg_stat_statements.out           |   1 +
 .../pg_stat_statements/expected/utility.out   | 413 ++++++++++++++++--
 contrib/pg_stat_statements/meson.build        |   1 +
 contrib/pg_stat_statements/sql/cursors.sql    |  30 ++
 .../sql/pg_stat_statements.sql                |   1 +
 contrib/pg_stat_statements/sql/utility.sql    | 216 ++++++++-
 8 files changed, 681 insertions(+), 53 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/cursors.out
 create mode 100644 contrib/pg_stat_statements/sql/cursors.sql

 
+DROP FUNCTION PLUS_ONE(INTEGER);
 --
 -- queries with locking clauses
 --
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 8d3bb7bf6d..031fd42be4 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -9,47 +9,365 @@ SELECT pg_stat_statements_reset();
  
 (1 row)

+(1 row)
+-- Partitions
+-- List of A_Const nodes
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE|1|0
+COMMIT WORK|6|0
 
+(1 row)
@@ -58,15 +376,24 @@ RESET work_mem;
 SET enable_seqscan = off;
+-- SET TRANSACTION ISOLATION
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN|1|0
+COMMIT|1|0
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED|1|0
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ|1|0
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|1|0
+(12 rows)

index 163f0a3069..7f9080821c 100644
@@ -5,21 +5,213 @@
 
 SELECT 1;

+-- List of A_Const nodes
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
@@ -30,5 +222,11 @@ RESET work_mem;
 SET enable_seqscan = off;
 SET enable_seqscan = on;
 RESET enable_seqscan;
+-- SET TRANSACTION ISOLATION
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;

From 7b8da9c54d21b7b0b87fb1489a18ad14c6e6a5dc Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 8 Feb 2023 11:00:01 +0900
Subject: [PATCH v2 3/4] Apply normalization to A_Const and utilities in
 pg_stat_statements

 .../pg_stat_statements/expected/utility.out   | 96 ++++++++-----------
 9 files changed, 81 insertions(+), 104 deletions(-)

index f7d7f10f7d..259e814253 100644
@@ -3221,14 +3221,18 @@ typedef struct InlineCodeBlock
 
index 1be1642d92..188ff1d249 100644

 static void
 
   <note>

 
index 031fd42be4..3ef9611d29 100644
@@ -180,9 +180,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE|1|0
+BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE|1|0
 COMMIT WORK|6|0
@@ -212,8 +212,8 @@ Function Scan on generate_series tab
@@ -239,12 +239,10 @@ CALL sum_two(1,1);
@@ -270,15 +268,12 @@ COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
@@ -300,10 +295,10 @@ CREATE TABLE ctas_stats_2 AS
@@ -325,8 +320,8 @@ DROP MATERIALIZED VIEW matview_stats_1;
@@ -346,14 +341,11 @@ DROP VIEW view_stats_1;
 
@@ -389,11 +381,7 @@ COMMIT|1|0
 RESET enable_seqscan|1|0
 RESET work_mem|1|0
 SELECT pg_stat_statements_reset()|1|1
-SET TRANSACTION ISOLATION LEVEL READ COMMITTED|1|0
-SET TRANSACTION ISOLATION LEVEL REPEATABLE READ|1|0
-SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|1|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
-(12 rows)
+SET TRANSACTION ISOLATION LEVEL $1 COMMITTED|3|0
+SET enable_seqscan = $1|2|0
+SET work_mem = $1|3|0
+(8 rows)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ad1fe44496..5285c3f7fa 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -836,8 +836,10 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
 	if (query->utilityStmt)
 	{
 		if (pgss_track_utility && !PGSS_HANDLED_UTILITY(query->utilityStmt))
+		{
 			query->queryId = UINT64CONST(0);
-		return;
+			return;
+		}
 	}
 
 	/*
-- 
2.39.1

From 901f4af4891795ce89b03eed6705e0eb1e3dd99a Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Thu, 16 Feb 2023 09:17:32 +0900
Subject: [PATCH v2 4/4] Remove normalization of A_Const nodes

Doing so leads to weird cases with commands that can define a
transaction isolation (SET TRANSACTION and BEGIN), as the normalization
is not able to copy with the full field, yet.

Applying normalization of Const nodes to DDLs changes the states of the
following commands:
- DECLARE
- EXPLAIN
- CREATE MATERIALIZED VIEW
- CTAS

At the end, this should be merged with the previous patch, but keeping
it separate shows the difference of behavior between the two approaches
in the regression tests of pg_stat_statements.
---
 src/backend/nodes/queryjumblefuncs.c          | 23 +++++-
 .../expected/pg_stat_statements.out           |  2 +-
 .../pg_stat_statements/expected/utility.out   | 82 +++++++++++--------
 3 files changed, 70 insertions(+), 37 deletions(-)

diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 0f08f4c75e..d7fd72d70f 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -323,8 +323,29 @@ _jumbleA_Const(JumbleState *jstate, Node *node)
 	if (!expr->isnull)
 	{
 		JUMBLE_FIELD(val.node.type);
+		switch (nodeTag(&expr->val))
+		{
+			case T_Integer:
+				JUMBLE_FIELD(val.ival.ival);
+				break;
+			case T_Float:
+				JUMBLE_STRING(val.fval.fval);
+				break;
+			case T_Boolean:
+				JUMBLE_FIELD(val.boolval.boolval);
+				break;
+			case T_String:
+				JUMBLE_STRING(val.sval.sval);
+				break;
+			case T_BitString:
+				JUMBLE_STRING(val.bsval.bsval);
+				break;
+			default:
+				elog(ERROR, "unrecognized node type: %d",
+					 (int) nodeTag(&expr->val));
+				break;
+		}
 	}
-	JUMBLE_LOCATION(location);
 }
 
 static void
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index f134805709..c7b6035268 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -311,7 +311,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
  wal_records > $2 as wal_records_generated,               +|       |      |                     |                       | 
  wal_records >= rows as wal_records_ge_rows               +|       |      |                     |                       | 
  FROM pg_stat_statements ORDER BY query COLLATE "C"        |       |      |                     |                       | 
- SET pg_stat_statements.track_utility = $1                 |     1 |    0 | f                   | f                     | t
+ SET pg_stat_statements.track_utility = FALSE              |     1 |    0 | f                   | f                     | t
  UPDATE pgss_test SET b = $1 WHERE a > $2                  |     1 |    3 | t                   | t                     | t
 (7 rows)
 
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 3ef9611d29..68a3e66125 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -32,11 +32,11 @@ NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
 query|calls|rows
-ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> $1)|1|0
-ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING $1 || b|1|0
-ALTER TABLE tab_stats ALTER COLUMN b set default $1|1|0
-CREATE INDEX index_stats ON tab_stats(b, (b || $1), (b || $2)) WHERE a > $3|1|0
-CREATE TEMP TABLE tab_stats (a int, b char($1))|1|0
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0)|1|0
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b|1|0
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a'|1|0
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0|1|0
+CREATE TEMP TABLE tab_stats (a int, b char(20))|1|0
 DROP TABLE IF EXISTS tab_stats|3|0
 DROP TABLE tab_stats|1|0
 SELECT $1|1|1
@@ -101,27 +101,27 @@ CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
 DROP TABLE tab_expr_stats;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT $1|1|0
-ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> $1)|1|0
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1|1|0
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0)|1|0
 ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index|1|0
-ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM ($1) TO ($2)|1|0
-ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT $1|1|0
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100)|1|0
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2|1|0
 CREATE FOREIGN DATA WRAPPER wrapper_stats|1|0
 CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats|1|0
-CREATE FUNCTION func_stats(a text DEFAULT $1, b text DEFAULT lower($2))
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
   RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL|1|0
 CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
   AS $$ BEGIN return OLD; end; $$|1|0
 CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a)|1|0
 CREATE INDEX pt_stats_index ON ONLY pt_stats (a)|1|0
-CREATE POLICY policy_stats ON tab_policy_stats USING (a = $1) WITH CHECK (b < $2)|1|0
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5)|1|0
 CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
-  INSERT INTO tab_rule_stats_2 VALUES(new.*, $1, $2)|1|0
+  INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2)|1|0
 CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats|1|0
-CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, ($1*a), ($2*b) FROM tab_expr_stats|1|0
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats|1|0
 CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a)|1|0
 CREATE TABLE pt_stats1 (a int, b int)|1|0
-CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM ($1) TO ($2)|1|0
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200)|1|0
 CREATE TABLE tab_expr_stats (a int, b int)|1|0
 CREATE TABLE tab_policy_stats (a int, b int)|1|0
 CREATE TABLE tab_rule_stats (a int, b int)|1|0
@@ -129,10 +129,10 @@ CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int)|1|0
 CREATE TABLE trigger_tab_stats (a int, b int)|1|0
 CREATE TRIGGER trigger_tab_stats
     AFTER UPDATE ON trigger_tab_stats
-    FOR EACH ROW WHEN (OLD.a < $1 AND OLD.b < $2 AND $3)
+    FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
     EXECUTE FUNCTION trigger_func_stats()|1|0
-CREATE TYPE stats_type as (f1 numeric($1, $2), f2 numeric($3, $4))|1|0
-CREATE VIEW view_stats AS SELECT $1::int AS a, $2::int AS b|1|0
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2))|1|0
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b|1|0
 DROP FOREIGN DATA WRAPPER wrapper_stats|1|0
 DROP FOREIGN TABLE foreign_stats|1|0
 DROP FUNCTION func_stats|1|0
@@ -180,9 +180,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
 ABORT|4|0
 BEGIN|6|0
-BEGIN ISOLATION LEVEL $1|2|0
-BEGIN TRANSACTION $1|1|0
-BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE|1|0
+BEGIN ISOLATION LEVEL SERIALIZABLE|2|0
+BEGIN TRANSACTION DEFERRABLE|1|0
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE|1|0
 COMMIT WORK|6|0
 SELECT pg_stat_statements_reset()|1|1
 (7 rows)
@@ -239,10 +239,12 @@ CALL sum_two(1,1);
 CALL sum_two(1,2);
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-CALL sum_one($1)|2|0
-CALL sum_two($1,$2)|2|0
+CALL sum_one(199)|1|0
+CALL sum_one(3)|1|0
+CALL sum_two(1,1)|1|0
+CALL sum_two(1,2)|1|0
 SELECT pg_stat_statements_reset()|1|1
-(3 rows)
+(5 rows)
 -- COPY
 CREATE TABLE copy_stats (a int, b int);
 SELECT pg_stat_statements_reset();
@@ -268,12 +270,15 @@ COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
 1	4
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
-COPY (DELETE FROM copy_stats WHERE a = $1 RETURNING *) TO STDOUT|1|1
-COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) TO STDOUT|2|2
-COPY (SELECT $1) TO STDOUT|2|2
-COPY (UPDATE copy_stats SET b = b + $1 RETURNING *) TO STDOUT|2|4
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT|1|1
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT|1|1
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT|1|1
+COPY (SELECT 1) TO STDOUT|1|1
+COPY (SELECT 2) TO STDOUT|1|1
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT|1|2
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT|1|2
 SELECT pg_stat_statements_reset()|1|1
-(5 rows)
+(8 rows)
 DROP TABLE copy_stats;
 SELECT pg_stat_statements_reset();
 pg_stat_statements_reset
@@ -341,11 +346,14 @@ DROP VIEW view_stats_1;
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 query|calls|rows
 CREATE VIEW view_stats_1 AS 
-  SELECT a AS col1, $1::int AS col2
-    FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5|2|0
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|1|0
+CREATE VIEW view_stats_1 AS 
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3|1|0
 DROP VIEW view_stats_1|2|0
 SELECT pg_stat_statements_reset()|1|1
-(3 rows)
+(4 rows)
 SELECT pg_stat_statements_reset();
 pg_stat_statements_reset
 
@@ -381,7 +389,11 @@ COMMIT|1|0
 RESET enable_seqscan|1|0
 RESET work_mem|1|0
 SELECT pg_stat_statements_reset()|1|1
-SET TRANSACTION ISOLATION LEVEL $1 COMMITTED|3|0
-SET enable_seqscan = $1|2|0
-SET work_mem = $1|3|0
-(8 rows)
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED|1|0
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ|1|0
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|1|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
+(12 rows)
-- 
2.39.1

---

From: "Drouvot, Bertrand" <[email-masked]>
Date: Thu, 16 Feb 2023 10:55:32 +0100

Hi,

> adjustments that make sense.

Thanks for those patches!

Yeah, agree about the proposed approach.

> 0002 has been

0001:

I like the idea of splitting the existing tests in dedicated files.

What do you think about removing:

"
SET pg_stat_statements.track_utility = FALSE;
SET pg_stat_statements.track_planning = TRUE;
"

In the new pg_stat_statements.sql? That way pg_stat_statements.sql would always behave
with default values for those (currently we are setting both of them as non default).

Then, with the default values in place, if we feel that some tests are missing we could add them in
utility.sql or planning.sql accordingly.

0002:

Produces:
v2-0002-Add-more-test-for-utility-queries-in-pg_stat_stat.patch:834: trailing whitespace.
CREATE VIEW view_stats_1 AS
v2-0002-Add-more-test-for-utility-queries-in-pg_stat_stat.patch:838: trailing whitespace.
CREATE VIEW view_stats_1 AS
warning: 2 lines add whitespace errors.

What about adding things like "SET SESSION CHARACTERISTICS AS TRANSACTION..." too?

0003 and 0004:
Thanks for keeping 0003 that's useful to see the impact of A_Const normalization.

Looking at the diff they produce, I also do think that 0004 is what
could be done for PG16.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com