Subject: Re: Normalization of utility queries in pg_stat_statements

From: Michael Paquier <[email-masked]>
Date: Mon, 20 Feb 2023 11:32:23 +0900

On Fri, Feb 17, 2023 at 09:36:27AM +0100, Drouvot, Bertrand wrote:
> On 2/17/23 3:35 AM, Michael Paquier wrote:
>> 0001 looks quite committable at this stage, and that's independent on
>> the rest.  At the end this patch creates four new test files that are
>> extended in the next patches: utility, planning, track and cleanup.
> 
> Thanks! LGTM.

Thanks.  I have applied the set of regression tests in 0001 and 0002.
Note that I have changed the order of the attributes when querying
pg_stat_statements, to make easier to follow the diffs generated by
the normalization.  The unaligned mode would be another option, but
it makes not much sense as long as there are no more than two fields
with variable lengths.  Some extra notes about that:
- Should the test for the validation WAL generation metrics be moved
out?  I am not sure that it makes much sense to separate it as it has
a short purpose.
- Same issue with user activity, which creates a few roles and makes
sure that their activity is tracked?  We don't look at the userid in
this case, which does not make much sense to me.
- Same issue with locking clauses, worth a file of their own?

The main file is still named pg_stat_statements.sql, perhaps it should
be renamed to something more generic, like general.sql?  Or perhaps we
could just split the main file with a select.sql (with locking
clauses) and a dml.sql?

>> I am wondering if others have an opinion to share about that, but,
>> yes, 0004 seems enough to begin with.  We could always study more
>> normalization areas in future releases, taking it slowly.
> 
> Agree.

These last ones are staying around for a few more weeks, until the
middle of the next CF, I guess.  After all this is done, the final
changes are very short, showing the effects of the normalization, as
of:
 6 files changed, 45 insertions(+), 35 deletions(-)
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Mon, 20 Feb 2023 11:34:59 +0900

>  6 files changed, 45 insertions(+), 35 deletions(-)

With the patches..
--
Michael
From da6b7a7c5199c4db6ea8906df6c0e256559735f1 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 8 Feb 2023 11:00:01 +0900
Subject: [PATCH v4 3/4] 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   |  14 +-
 .../expected/level_tracking.out               |  20 +-
 .../pg_stat_statements/expected/utility.out   | 174 ++++++++----------
 .../pg_stat_statements/pg_stat_statements.c   |   4 +-
 8 files changed, 117 insertions(+), 142 deletions(-)

diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f7d7f10f7d..259e814253 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3221,14 +3221,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 1be1642d92..188ff1d249 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 5d0dc196f9..46375ea905 100644
--- a/contrib/pg_stat_statements/expected/cursors.out
+++ b/contrib/pg_stat_statements/expected/cursors.out
@@ -16,10 +16,10 @@ CLOSE cursor_stats_1;
 DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
 CLOSE cursor_stats_1;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                        query                         
--------+------+------------------------------------------------------
+ calls | rows |                         query                         
+-------+------+-------------------------------------------------------
      2 |    0 | CLOSE cursor_stats_1
-     2 |    0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1
+     2 |    0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1
      1 |    1 | SELECT pg_stat_statements_reset()
 (3 rows)
 
@@ -49,14 +49,14 @@ CLOSE cursor_stats_1;
 CLOSE cursor_stats_2;
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                        query                         
--------+------+------------------------------------------------------
+ calls | rows |                         query                         
+-------+------+-------------------------------------------------------
      1 |    0 | 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 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1
+     1 |    0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1
      1 |    1 | FETCH 1 IN cursor_stats_1
      1 |    1 | FETCH 1 IN cursor_stats_2
      1 |    1 | SELECT pg_stat_statements_reset()
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index c824ebdac5..615be0775f 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -49,22 +49,22 @@ BEGIN
 END; $$;
 SELECT toplevel, calls, query FROM pg_stat_statements
   ORDER BY query COLLATE "C", toplevel;
- toplevel | calls |                query                 
-----------+-------+--------------------------------------
+ toplevel | calls |               query               
+----------+-------+-----------------------------------
  f        |     1 | DELETE FROM stats_track_tab
  t        |     1 | DELETE FROM stats_track_tab
- t        |     1 | DO $$                               +
-          |       | BEGIN                               +
-          |       |   DELETE FROM stats_track_tab;      +
+ t        |     1 | DO $$                            +
+          |       | BEGIN                            +
+          |       |   DELETE FROM stats_track_tab;   +
           |       | END; $$
- t        |     1 | DO LANGUAGE plpgsql $$              +
-          |       | BEGIN                               +
-          |       |   -- this is a SELECT               +
-          |       |   PERFORM 'hello world'::TEXT;      +
+ t        |     1 | DO LANGUAGE plpgsql $$           +
+          |       | BEGIN                            +
+          |       |   -- this is a SELECT            +
+          |       |   PERFORM 'hello world'::TEXT;   +
           |       | END; $$
  f        |     1 | SELECT $1::TEXT
  t        |     1 | SELECT pg_stat_statements_reset()
- t        |     1 | SET pg_stat_statements.track = 'all'
+ t        |     1 | SET pg_stat_statements.track = $1
 (7 rows)
 
 -- PL/pgSQL function - top-level tracking.
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index dbb8f661c0..f50de29440 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -24,13 +24,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
- calls | rows |                                        query                                         
--------+------+--------------------------------------------------------------------------------------
-     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))
+ calls | rows |                                    query                                    
+-------+------+-----------------------------------------------------------------------------
+     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))
      3 |    0 | DROP TABLE IF EXISTS tab_stats
      1 |    0 | DROP TABLE tab_stats
      1 |    1 | SELECT pg_stat_statements_reset()
@@ -96,40 +96,40 @@ 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                        query                                        
--------+------+-------------------------------------------------------------------------------------
-     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)
+ calls | rows |                                       query                                       
+-------+------+-----------------------------------------------------------------------------------
+     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'))+
+     1 |    0 | 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             +
+     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 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 | 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 | 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)                           +
+     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)                         +
        |      |     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
@@ -179,14 +179,14 @@ COMMIT;
 BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE;
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                query                                
--------+------+---------------------------------------------------------------------
+ calls | rows |                         query                          
+-------+------+--------------------------------------------------------
      4 |    0 | ABORT
      6 |    0 | BEGIN
-     2 |    0 | BEGIN ISOLATION LEVEL SERIALIZABLE
-     1 |    0 | BEGIN TRANSACTION DEFERRABLE
-     1 |    0 | BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE
-     1 |    0 | BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE
+     2 |    0 | BEGIN ISOLATION LEVEL $1
+     1 |    0 | BEGIN TRANSACTION $1
+     1 |    0 | BEGIN TRANSACTION $1 DEFERRABLE, $2 ONLY, $3 WRITE, $4
+     1 |    0 | BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE
      7 |    0 | COMMIT WORK
      1 |    1 | SELECT pg_stat_statements_reset()
 (8 rows)
@@ -226,10 +226,10 @@ EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
 (2 rows)
 
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                     query                                     
--------+------+-------------------------------------------------------------------------------
-     2 |    0 | EXPLAIN (costs off) SELECT 1
-     2 |    0 | EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3
+ calls | rows |                                      query                                      
+-------+------+---------------------------------------------------------------------------------
+     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
      1 |    1 | SELECT pg_stat_statements_reset()
 (3 rows)
 
@@ -259,12 +259,10 @@ CALL sum_two(1,2);
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
  calls | rows |               query               
 -------+------+-----------------------------------
-     1 |    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)
+     2 |    0 | CALL sum_one($1)
+     2 |    0 | CALL sum_two($1,$2)
      1 |    1 | SELECT pg_stat_statements_reset()
-(5 rows)
+(3 rows)
 
 -- COPY
 CREATE TABLE copy_stats (a int, b int);
@@ -292,17 +290,14 @@ COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
 COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
 1	4
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                               query                               
--------+------+-------------------------------------------------------------------
-     1 |    1 | 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 |    2 | COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT
-     1 |    2 | COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT
+ calls | rows |                                query                                
+-------+------+---------------------------------------------------------------------
+     1 |    1 | COPY (DELETE FROM copy_stats WHERE a = $1 RETURNING *) TO STDOUT
+     2 |    2 | COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) TO STDOUT
+     2 |    2 | COPY (SELECT $1) TO STDOUT
+     2 |    4 | COPY (UPDATE copy_stats SET b = b + $1 RETURNING *) TO STDOUT
      1 |    1 | SELECT pg_stat_statements_reset()
-(8 rows)
+(5 rows)
 
 DROP TABLE copy_stats;
 SELECT pg_stat_statements_reset();
@@ -326,12 +321,12 @@ CREATE TABLE ctas_stats_2 AS
     FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
 DROP TABLE ctas_stats_2;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                              query                              
--------+------+-----------------------------------------------------------------
-     2 |    2 | CREATE TABLE ctas_stats_1 AS SELECT 1 AS a
-     2 |    4 | 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
+ calls | rows |                               query                                
+-------+------+--------------------------------------------------------------------
+     2 |    2 | CREATE TABLE ctas_stats_1 AS SELECT $1 AS a
+     2 |    4 | CREATE TABLE ctas_stats_2 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 | DROP TABLE ctas_stats_1
      2 |    0 | DROP TABLE ctas_stats_2
      1 |    1 | SELECT pg_stat_statements_reset()
@@ -354,11 +349,11 @@ CREATE MATERIALIZED VIEW matview_stats_1 AS
     FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
 DROP MATERIALIZED VIEW matview_stats_1;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                              query                              
--------+------+-----------------------------------------------------------------
-     2 |    2 | 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
+ calls | rows |                               query                                
+-------+------+--------------------------------------------------------------------
+     2 |    2 | CREATE MATERIALIZED VIEW matview_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 | DROP MATERIALIZED VIEW matview_stats_1
      1 |    1 | SELECT pg_stat_statements_reset()
 (3 rows)
@@ -379,17 +374,14 @@ CREATE VIEW view_stats_1 AS
     FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
 DROP VIEW view_stats_1;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                              query                              
--------+------+-----------------------------------------------------------------
-     1 |    0 | 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
+ calls | rows |                               query                                
+-------+------+--------------------------------------------------------------------
+     2 |    0 | 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 | DROP VIEW view_stats_1
      1 |    1 | SELECT pg_stat_statements_reset()
-(4 rows)
+(3 rows)
 
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -403,11 +395,11 @@ ALTER DOMAIN domain_stats SET DEFAULT '3';
 ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
 DROP DOMAIN domain_stats;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                   query                                    
--------+------+----------------------------------------------------------------------------
-     1 |    0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1)
-     1 |    0 | ALTER DOMAIN domain_stats SET DEFAULT '3'
-     1 |    0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0)
+ calls | rows |                                    query                                    
+-------+------+-----------------------------------------------------------------------------
+     1 |    0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > $1)
+     1 |    0 | ALTER DOMAIN domain_stats SET DEFAULT $1
+     1 |    0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > $1)
      1 |    0 | DROP DOMAIN domain_stats
      1 |    1 | SELECT pg_stat_statements_reset()
 (5 rows)
@@ -441,8 +433,8 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT;
 RESET SESSION AUTHORIZATION;
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                      query                      
--------+------+-------------------------------------------------
+ calls | rows |                    query                     
+-------+------+----------------------------------------------
      2 |    0 | BEGIN
      2 |    0 | COMMIT
      2 |    0 | RESET SESSION AUTHORIZATION
@@ -451,14 +443,10 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
      1 |    1 | SELECT pg_stat_statements_reset()
      1 |    0 | SET LOCAL SESSION AUTHORIZATION DEFAULT
      1 |    0 | SET SESSION SESSION AUTHORIZATION DEFAULT
-     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 | SET enable_seqscan = off
-     1 |    0 | SET enable_seqscan = on
-     2 |    0 | SET work_mem = '1MB'
-     1 |    0 | SET work_mem = '2MB'
-(15 rows)
+     3 |    0 | SET TRANSACTION ISOLATION LEVEL $1 COMMITTED
+     2 |    0 | SET enable_seqscan = $1
+     3 |    0 | SET work_mem = $1
+(11 rows)
 
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -508,19 +496,19 @@ FETCH FORWARD ALL pgss_cursor;
 
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                   query                                    
--------+------+----------------------------------------------------------------------------
+ calls | rows |                                  query                                  
+-------+------+-------------------------------------------------------------------------
      1 |    0 | BEGIN
      1 |    0 | COMMIT
      1 |    3 | COPY pgss_ctas (a, b) FROM STDIN
      1 |   13 | CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas
-     1 |   10 | CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a
+     1 |   10 | CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) a
      1 |    0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv
      1 |    5 | FETCH FORWARD 5 pgss_cursor
      1 |    7 | FETCH FORWARD ALL pgss_cursor
      1 |    1 | FETCH NEXT pgss_cursor
      1 |   13 | REFRESH MATERIALIZED VIEW pgss_matv
-     1 |   10 | SELECT generate_series(1, 10) c INTO pgss_select_into
+     1 |   10 | SELECT generate_series($1, $2) c INTO pgss_select_into
      1 |    1 | SELECT pg_stat_statements_reset()
 (12 rows)
 
@@ -548,9 +536,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
      1 |    0 | RESET enable_seqscan
      1 |    0 | RESET work_mem
      1 |    1 | SELECT pg_stat_statements_reset()
-     1 |    0 | SET enable_seqscan = off
-     1 |    0 | SET enable_seqscan = on
-     2 |    0 | SET work_mem = '1MB'
-     1 |    0 | SET work_mem = '2MB'
-(7 rows)
+     2 |    0 | SET enable_seqscan = $1
+     3 |    0 | SET work_mem = $1
+(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.2

From dfe2b9d15788fe04c9205738ba66201c0730fe90 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Thu, 16 Feb 2023 09:17:32 +0900
Subject: [PATCH v4 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/level_tracking.out               |  20 +--
 .../pg_stat_statements/expected/utility.out   | 136 ++++++++++--------
 3 files changed, 107 insertions(+), 72 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/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 615be0775f..c824ebdac5 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -49,22 +49,22 @@ BEGIN
 END; $$;
 SELECT toplevel, calls, query FROM pg_stat_statements
   ORDER BY query COLLATE "C", toplevel;
- toplevel | calls |               query               
-----------+-------+-----------------------------------
+ toplevel | calls |                query                 
+----------+-------+--------------------------------------
  f        |     1 | DELETE FROM stats_track_tab
  t        |     1 | DELETE FROM stats_track_tab
- t        |     1 | DO $$                            +
-          |       | BEGIN                            +
-          |       |   DELETE FROM stats_track_tab;   +
+ t        |     1 | DO $$                               +
+          |       | BEGIN                               +
+          |       |   DELETE FROM stats_track_tab;      +
           |       | END; $$
- t        |     1 | DO LANGUAGE plpgsql $$           +
-          |       | BEGIN                            +
-          |       |   -- this is a SELECT            +
-          |       |   PERFORM 'hello world'::TEXT;   +
+ t        |     1 | DO LANGUAGE plpgsql $$              +
+          |       | BEGIN                               +
+          |       |   -- this is a SELECT               +
+          |       |   PERFORM 'hello world'::TEXT;      +
           |       | END; $$
  f        |     1 | SELECT $1::TEXT
  t        |     1 | SELECT pg_stat_statements_reset()
- t        |     1 | SET pg_stat_statements.track = $1
+ t        |     1 | SET pg_stat_statements.track = 'all'
 (7 rows)
 
 -- PL/pgSQL function - top-level tracking.
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index f50de29440..826ada4eaa 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -24,13 +24,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
 NOTICE:  table "tab_stats" does not exist, skipping
- calls | rows |                                    query                                    
--------+------+-----------------------------------------------------------------------------
-     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))
+ calls | rows |                                        query                                         
+-------+------+--------------------------------------------------------------------------------------
+     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))
      3 |    0 | DROP TABLE IF EXISTS tab_stats
      1 |    0 | DROP TABLE tab_stats
      1 |    1 | SELECT pg_stat_statements_reset()
@@ -96,40 +96,40 @@ 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                       query                                       
--------+------+-----------------------------------------------------------------------------------
-     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)
+ calls | rows |                                        query                                        
+-------+------+-------------------------------------------------------------------------------------
+     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))          +
+     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           +
+     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 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 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, ($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 | 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)                         +
+     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($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
@@ -179,14 +179,14 @@ COMMIT;
 BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE;
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                         query                          
--------+------+--------------------------------------------------------
+ calls | rows |                                query                                
+-------+------+---------------------------------------------------------------------
      4 |    0 | ABORT
      6 |    0 | BEGIN
-     2 |    0 | BEGIN ISOLATION LEVEL $1
-     1 |    0 | BEGIN TRANSACTION $1
-     1 |    0 | BEGIN TRANSACTION $1 DEFERRABLE, $2 ONLY, $3 WRITE, $4
-     1 |    0 | BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE
+     2 |    0 | BEGIN ISOLATION LEVEL SERIALIZABLE
+     1 |    0 | BEGIN TRANSACTION DEFERRABLE
+     1 |    0 | BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE
+     1 |    0 | BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE
      7 |    0 | COMMIT WORK
      1 |    1 | SELECT pg_stat_statements_reset()
 (8 rows)
@@ -259,10 +259,12 @@ CALL sum_two(1,2);
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
  calls | rows |               query               
 -------+------+-----------------------------------
-     2 |    0 | CALL sum_one($1)
-     2 |    0 | CALL sum_two($1,$2)
+     1 |    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 |    1 | SELECT pg_stat_statements_reset()
-(3 rows)
+(5 rows)
 
 -- COPY
 CREATE TABLE copy_stats (a int, b int);
@@ -290,14 +292,17 @@ COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
 COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
 1	4
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                query                                
--------+------+---------------------------------------------------------------------
-     1 |    1 | COPY (DELETE FROM copy_stats WHERE a = $1 RETURNING *) TO STDOUT
-     2 |    2 | COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) TO STDOUT
-     2 |    2 | COPY (SELECT $1) TO STDOUT
-     2 |    4 | COPY (UPDATE copy_stats SET b = b + $1 RETURNING *) TO STDOUT
+ calls | rows |                               query                               
+-------+------+-------------------------------------------------------------------
+     1 |    1 | 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 |    2 | 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 |    1 | SELECT pg_stat_statements_reset()
-(5 rows)
+(8 rows)
 
 DROP TABLE copy_stats;
 SELECT pg_stat_statements_reset();
@@ -374,14 +379,17 @@ CREATE VIEW view_stats_1 AS
     FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
 DROP VIEW view_stats_1;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                               query                                
--------+------+--------------------------------------------------------------------
-     2 |    0 | 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
+ calls | rows |                              query                              
+-------+------+-----------------------------------------------------------------
+     1 |    0 | 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
      2 |    0 | DROP VIEW view_stats_1
      1 |    1 | SELECT pg_stat_statements_reset()
-(3 rows)
+(4 rows)
 
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -395,11 +403,11 @@ ALTER DOMAIN domain_stats SET DEFAULT '3';
 ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
 DROP DOMAIN domain_stats;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                                    query                                    
--------+------+-----------------------------------------------------------------------------
-     1 |    0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > $1)
-     1 |    0 | ALTER DOMAIN domain_stats SET DEFAULT $1
-     1 |    0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > $1)
+ calls | rows |                                   query                                    
+-------+------+----------------------------------------------------------------------------
+     1 |    0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1)
+     1 |    0 | ALTER DOMAIN domain_stats SET DEFAULT '3'
+     1 |    0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0)
      1 |    0 | DROP DOMAIN domain_stats
      1 |    1 | SELECT pg_stat_statements_reset()
 (5 rows)
@@ -433,8 +441,8 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT;
 RESET SESSION AUTHORIZATION;
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                    query                     
--------+------+----------------------------------------------
+ calls | rows |                      query                      
+-------+------+-------------------------------------------------
      2 |    0 | BEGIN
      2 |    0 | COMMIT
      2 |    0 | RESET SESSION AUTHORIZATION
@@ -443,10 +451,14 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
      1 |    1 | SELECT pg_stat_statements_reset()
      1 |    0 | SET LOCAL SESSION AUTHORIZATION DEFAULT
      1 |    0 | SET SESSION SESSION AUTHORIZATION DEFAULT
-     3 |    0 | SET TRANSACTION ISOLATION LEVEL $1 COMMITTED
-     2 |    0 | SET enable_seqscan = $1
-     3 |    0 | SET work_mem = $1
-(11 rows)
+     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 | SET enable_seqscan = off
+     1 |    0 | SET enable_seqscan = on
+     2 |    0 | SET work_mem = '1MB'
+     1 |    0 | SET work_mem = '2MB'
+(15 rows)
 
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
@@ -536,7 +548,9 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
      1 |    0 | RESET enable_seqscan
      1 |    0 | RESET work_mem
      1 |    1 | SELECT pg_stat_statements_reset()
-     2 |    0 | SET enable_seqscan = $1
-     3 |    0 | SET work_mem = $1
-(5 rows)
+     1 |    0 | SET enable_seqscan = off
+     1 |    0 | SET enable_seqscan = on
+     2 |    0 | SET work_mem = '1MB'
+     1 |    0 | SET work_mem = '2MB'
+(7 rows)
 
-- 
2.39.2

---

From: Michael Paquier <[email-masked]>
Date: Wed, 1 Mar 2023 13:47:49 +0900

> With the patches..

Attached is an updated patch set, where I have done more refactoring
work for the regression tests of pg_stat_statements, splitting
pg_stat_statments.sql into the following files:
- user_activity.sql for the role-level resets.
- wal.sql for the WAL generation tracking.
- dml.sql for insert/update/delete/merge and row counts.
- The main file is renamed to select.sql, as it now only covers SELECT
patterns.

There is no change in the code coverage or the patterns tested.  And
with that, I am rather comfortable with the shape of the regression
tests moving forward.

0002 and 0003 are equivalent to the previous 0003 and 0004 in v4, that
switch pg_stat_statements to apply the normalization to utilities that
use Const nodes.
--
Michael
From 23476ee28a8e5bb82859369a8f54e9cd3c45fc32 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 1 Mar 2023 13:34:11 +0900
Subject: [PATCH v5 1/3] Split more regression tests of pg_stat_statements

This commit expands more the refactoring of the regression tests of
pg_stat_statements, with tests moved out of pg_stat_statements.sql to
separate files, as of:
- select.sql is mainly the former pg_stat_statements.sql, renamed.
- dml.sql for INSERT/UPDATE/DELETE and MERGE
- user_activity, to test role-level checks and stat resets.
- wal, to check the WAL generation.
---
 contrib/pg_stat_statements/Makefile           |   4 +-
 contrib/pg_stat_statements/expected/dml.out   | 148 ++++
 .../expected/pg_stat_statements.out           | 768 ------------------
 .../pg_stat_statements/expected/select.out    | 411 ++++++++++
 .../expected/user_activity.out                | 199 +++++
 contrib/pg_stat_statements/expected/wal.out   |  35 +
 contrib/pg_stat_statements/meson.build        |   5 +-
 contrib/pg_stat_statements/sql/dml.sql        |  78 ++
 .../sql/pg_stat_statements.sql                | 300 -------
 contrib/pg_stat_statements/sql/select.sql     | 146 ++++
 .../pg_stat_statements/sql/user_activity.sql  |  65 ++
 contrib/pg_stat_statements/sql/wal.sql        |  22 +
 12 files changed, 1110 insertions(+), 1071 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/dml.out
 delete mode 100644 contrib/pg_stat_statements/expected/pg_stat_statements.out
 create mode 100644 contrib/pg_stat_statements/expected/select.out
 create mode 100644 contrib/pg_stat_statements/expected/user_activity.out
 create mode 100644 contrib/pg_stat_statements/expected/wal.out
 create mode 100644 contrib/pg_stat_statements/sql/dml.sql
 delete mode 100644 contrib/pg_stat_statements/sql/pg_stat_statements.sql
 create mode 100644 contrib/pg_stat_statements/sql/select.sql
 create mode 100644 contrib/pg_stat_statements/sql/user_activity.sql
 create mode 100644 contrib/pg_stat_statements/sql/wal.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 69fbc6a858..5578a9dd4e 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -17,8 +17,8 @@ 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 cursors utility level_tracking planning \
-	cleanup oldextversions
+REGRESS = select dml cursors utility level_tracking planning \
+	user_activity wal 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/dml.out b/contrib/pg_stat_statements/expected/dml.out
new file mode 100644
index 0000000000..803d993e85
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/dml.out
@@ -0,0 +1,148 @@
+--
+-- DMLs on test table
+--
+SET pg_stat_statements.track_utility = FALSE;
+-- utility "create table" should not be shown
+CREATE TEMP TABLE pgss_dml_tab (a int, b char(20));
+INSERT INTO pgss_dml_tab VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_dml_tab SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_dml_tab WHERE a > 9;
+-- explicit transaction
+BEGIN;
+UPDATE pgss_dml_tab SET b = '111' WHERE a = 1 ;
+COMMIT;
+BEGIN \;
+UPDATE pgss_dml_tab SET b = '222' WHERE a = 2 \;
+COMMIT ;
+UPDATE pgss_dml_tab SET b = '333' WHERE a = 3 \;
+UPDATE pgss_dml_tab SET b = '444' WHERE a = 4 ;
+BEGIN \;
+UPDATE pgss_dml_tab SET b = '555' WHERE a = 5 \;
+UPDATE pgss_dml_tab SET b = '666' WHERE a = 6 \;
+COMMIT ;
+-- many INSERT values
+INSERT INTO pgss_dml_tab (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
+-- SELECT with constants
+SELECT * FROM pgss_dml_tab WHERE a > 5 ORDER BY a ;
+ a |          b           
+---+----------------------
+ 6 | 666                 
+ 7 | aaa                 
+ 8 | bbb                 
+ 9 | bbb                 
+(4 rows)
+
+SELECT *
+  FROM pgss_dml_tab
+  WHERE a > 9
+  ORDER BY a ;
+ a | b 
+---+---
+(0 rows)
+
+-- these two need to be done on a different table
+-- SELECT without constants
+SELECT * FROM pgss_dml_tab ORDER BY a;
+ a |          b           
+---+----------------------
+ 1 | a                   
+ 1 | 111                 
+ 2 | b                   
+ 2 | 222                 
+ 3 | c                   
+ 3 | 333                 
+ 4 | 444                 
+ 5 | 555                 
+ 6 | 666                 
+ 7 | aaa                 
+ 8 | bbb                 
+ 9 | bbb                 
+(12 rows)
+
+-- SELECT with IN clause
+SELECT * FROM pgss_dml_tab WHERE a IN (1, 2, 3, 4, 5);
+ a |          b           
+---+----------------------
+ 1 | 111                 
+ 2 | 222                 
+ 3 | 333                 
+ 4 | 444                 
+ 5 | 555                 
+ 1 | a                   
+ 2 | b                   
+ 3 | c                   
+(8 rows)
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | rows |                                query                                
+-------+------+---------------------------------------------------------------------
+     1 |    1 | DELETE FROM pgss_dml_tab WHERE a > $1
+     1 |    3 | INSERT INTO pgss_dml_tab (a, b) VALUES ($1, $2), ($3, $4), ($5, $6)
+     1 |   10 | INSERT INTO pgss_dml_tab VALUES(generate_series($1, $2), $3)
+     1 |   12 | SELECT * FROM pgss_dml_tab ORDER BY a
+     2 |    4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
+     1 |    8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5)
+     1 |    1 | SELECT pg_stat_statements_reset()
+     1 |    0 | SET pg_stat_statements.track_utility = FALSE
+     6 |    6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
+     1 |    3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2
+(10 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- MERGE
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT VALUES (0, NULL);	-- same as above
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN DELETE;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN DO NOTHING;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN NOT MATCHED THEN DO NOTHING;
+DROP TABLE pgss_dml_tab;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | rows |                                          query                                          
+-------+------+-----------------------------------------------------------------------------------------
+     1 |    6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
+       |      |  WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text
+     1 |    6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
+       |      |  WHEN MATCHED THEN DELETE
+     1 |    0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
+       |      |  WHEN MATCHED THEN DO NOTHING
+     1 |    6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
+       |      |  WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text
+     1 |    6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
+       |      |  WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text
+     1 |    0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
+       |      |  WHEN NOT MATCHED THEN DO NOTHING
+     1 |    0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)               +
+       |      |  WHEN NOT MATCHED THEN INSERT (a) VALUES ($1)
+     2 |    0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)               +
+       |      |  WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2)
+     1 |    0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)               +
+       |      |  WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2)
+     1 |    1 | SELECT pg_stat_statements_reset()
+(10 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
deleted file mode 100644
index 2c5bed841a..0000000000
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ /dev/null
@@ -1,768 +0,0 @@
-CREATE EXTENSION pg_stat_statements;
---
--- simple and compound statements
---
-SET pg_stat_statements.track_utility = FALSE;
-SET pg_stat_statements.track_planning = TRUE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT 1 AS "int";
- int 
------
-   1
-(1 row)
-
-SELECT 'hello'
-  -- multiline
-  AS "text";
- text  
--------
- hello
-(1 row)
-
-SELECT 'world' AS "text";
- text  
--------
- world
-(1 row)
-
--- transaction
-BEGIN;
-SELECT 1 AS "int";
- int 
------
-   1
-(1 row)
-
-SELECT 'hello' AS "text";
- text  
--------
- hello
-(1 row)
-
-COMMIT;
--- compound transaction
-BEGIN \;
-SELECT 2.0 AS "float" \;
-SELECT 'world' AS "text" \;
-COMMIT;
- float 
--------
-   2.0
-(1 row)
-
- text  
--------
- world
-(1 row)
-
--- compound with empty statements and spurious leading spacing
-\;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
- ?column? 
-----------
-        6
-(1 row)
-
- ?column? 
-----------
-   !
-(1 row)
-
- ?column? 
-----------
-        5
-(1 row)
-
--- non ;-terminated statements
-SELECT 1 + 1 + 1 AS "add" \gset
-SELECT :add + 1 + 1 AS "add" \;
-SELECT :add + 1 + 1 AS "add" \gset
- add 
------
-   5
-(1 row)
-
--- set operator
-SELECT 1 AS i UNION SELECT 2 ORDER BY i;
- i 
----
- 1
- 2
-(2 rows)
-
--- ? operator
-select '{"a":1, "b":2}'::jsonb ? 'b';
- ?column? 
-----------
- t
-(1 row)
-
--- cte
-WITH t(f) AS (
-  VALUES (1.0), (2.0)
-)
-  SELECT f FROM t ORDER BY f;
-  f  
------
- 1.0
- 2.0
-(2 rows)
-
--- prepared statement with parameter
-PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
-EXECUTE pgss_test(1);
- ?column? | ?column? 
-----------+----------
-        1 | test
-(1 row)
-
-DEALLOCATE pgss_test;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                    query                                     | calls | rows 
-------------------------------------------------------------------------------+-------+------
- PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3                            |     1 |    1
- SELECT $1                                                                   +|     4 |    4
-   -- multiline                                                              +|       | 
-   AS "text"                                                                  |       | 
- SELECT $1 + $2                                                               |     2 |    2
- SELECT $1 + $2 + $3 AS "add"                                                 |     3 |    3
- SELECT $1 AS "float"                                                         |     1 |    1
- SELECT $1 AS "int"                                                           |     2 |    2
- SELECT $1 AS i UNION SELECT $2 ORDER BY i                                    |     1 |    2
- SELECT $1 || $2                                                              |     1 |    1
- SELECT pg_stat_statements_reset()                                            |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
- WITH t(f) AS (                                                              +|     1 |    2
-   VALUES ($1), ($2)                                                         +|       | 
- )                                                                           +|       | 
-   SELECT f FROM t ORDER BY f                                                 |       | 
- select $1::jsonb ? $2                                                        |     1 |    1
-(12 rows)
-
---
--- CRUD: INSERT SELECT UPDATE DELETE on test table
---
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
--- utility "create table" should not be shown
-CREATE TEMP TABLE test (a int, b char(20));
-INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
-UPDATE test SET b = 'bbb' WHERE a > 7;
-DELETE FROM test WHERE a > 9;
--- explicit transaction
-BEGIN;
-UPDATE test SET b = '111' WHERE a = 1 ;
-COMMIT;
-BEGIN \;
-UPDATE test SET b = '222' WHERE a = 2 \;
-COMMIT ;
-UPDATE test SET b = '333' WHERE a = 3 \;
-UPDATE test SET b = '444' WHERE a = 4 ;
-BEGIN \;
-UPDATE test SET b = '555' WHERE a = 5 \;
-UPDATE test SET b = '666' WHERE a = 6 \;
-COMMIT ;
--- many INSERT values
-INSERT INTO test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
--- SELECT with constants
-SELECT * FROM test WHERE a > 5 ORDER BY a ;
- a |          b           
----+----------------------
- 6 | 666                 
- 7 | aaa                 
- 8 | bbb                 
- 9 | bbb                 
-(4 rows)
-
-SELECT *
-  FROM test
-  WHERE a > 9
-  ORDER BY a ;
- a | b 
----+---
-(0 rows)
-
--- SELECT without constants
-SELECT * FROM test ORDER BY a;
- a |          b           
----+----------------------
- 1 | a                   
- 1 | 111                 
- 2 | b                   
- 2 | 222                 
- 3 | c                   
- 3 | 333                 
- 4 | 444                 
- 5 | 555                 
- 6 | 666                 
- 7 | aaa                 
- 8 | bbb                 
- 9 | bbb                 
-(12 rows)
-
--- SELECT with IN clause
-SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
- a |          b           
----+----------------------
- 1 | 111                 
- 2 | 222                 
- 3 | 333                 
- 4 | 444                 
- 5 | 555                 
- 1 | a                   
- 2 | b                   
- 3 | c                   
-(8 rows)
-
--- MERGE
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = test.b || st.a::text;
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT VALUES (0, NULL);	-- same as above
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN DELETE;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN DO NOTHING;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN NOT MATCHED THEN DO NOTHING;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                    query                                     | calls | rows 
-------------------------------------------------------------------------------+-------+------
- DELETE FROM test WHERE a > $1                                                |     1 |    1
- INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6)                  |     1 |    3
- INSERT INTO test VALUES(generate_series($1, $2), $3)                         |     1 |   10
- MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1)             +|     1 |    6
-  WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = test.b || st.a::text |       | 
- MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1)             +|     1 |    6
-  WHEN MATCHED THEN DELETE                                                    |       | 
- MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1)             +|     1 |    0
-  WHEN MATCHED THEN DO NOTHING                                                |       | 
- MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1)             +|     1 |    6
-  WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text                         |       | 
- MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1)             +|     1 |    6
-  WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text                       |       | 
- MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1)             +|     1 |    0
-  WHEN NOT MATCHED THEN DO NOTHING                                            |       | 
- MERGE INTO test USING test st ON (st.a = test.a)                            +|     1 |    0
-  WHEN NOT MATCHED THEN INSERT (a) VALUES ($1)                                |       | 
- MERGE INTO test USING test st ON (st.a = test.a)                            +|     2 |    0
-  WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2)                         |       | 
- MERGE INTO test USING test st ON (st.a = test.a)                            +|     1 |    0
-  WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2)                         |       | 
- SELECT * FROM test ORDER BY a                                                |     1 |   12
- SELECT * FROM test WHERE a > $1 ORDER BY a                                   |     2 |    4
- SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5)                           |     1 |    8
- SELECT pg_stat_statements_reset()                                            |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
- UPDATE test SET b = $1 WHERE a = $2                                          |     6 |    6
- UPDATE test SET b = $1 WHERE a > $2                                          |     1 |    3
-(19 rows)
-
---
--- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
---
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
--- utility "create table" should not be shown
-CREATE TABLE pgss_test (a int, b char(20));
-INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
-UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
-DELETE FROM pgss_test WHERE a > 9;
--- DROP test table
-DROP TABLE pgss_test;
--- Check WAL is generated for the above statements
-SELECT query, calls, rows,
-wal_bytes > 0 as wal_bytes_generated,
-wal_records > 0 as wal_records_generated,
-wal_records >= rows as wal_records_ge_rows
-FROM pg_stat_statements ORDER BY query COLLATE "C";
-                           query                           | calls | rows | wal_bytes_generated | wal_records_generated | wal_records_ge_rows 
------------------------------------------------------------+-------+------+---------------------+-----------------------+---------------------
- DELETE FROM pgss_test WHERE a > $1                        |     1 |    1 | t                   | t                     | t
- INSERT INTO pgss_test VALUES(generate_series($1, $2), $3) |     1 |   10 | t                   | t                     | t
- SELECT pg_stat_statements_reset()                         |     1 |    1 | f                   | f                     | f
- SELECT query, calls, rows,                               +|     0 |    0 | f                   | f                     | t
- wal_bytes > $1 as wal_bytes_generated,                   +|       |      |                     |                       | 
- wal_records > $2 as wal_records_generated,               +|       |      |                     |                       | 
- wal_records >= rows as wal_records_ge_rows               +|       |      |                     |                       | 
- FROM pg_stat_statements ORDER BY query COLLATE "C"        |       |      |                     |                       | 
- UPDATE pgss_test SET b = $1 WHERE a > $2                  |     1 |    3 | t                   | t                     | t
-(5 rows)
-
---
--- queries with locking clauses
---
-CREATE TABLE pgss_a (id integer PRIMARY KEY);
-CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
--- control query
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
- id | id | a_id 
-----+----+------
-(0 rows)
-
--- test range tables
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a, pgss_b; -- matches plain "FOR UPDATE"
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a;
- id | id | a_id 
-----+----+------
-(0 rows)
-
--- test strengths
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE;
- id | id | a_id 
-----+----+------
-(0 rows)
-
--- test wait policies
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED;
- id | id | a_id 
-----+----+------
-(0 rows)
-
-SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls |                                          query                                           
--------+------------------------------------------------------------------------------------------
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE
-     2 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a
-     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED
-     0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"
-     1 | SELECT pg_stat_statements_reset()
-(12 rows)
-
-DROP TABLE pgss_a, pgss_b CASCADE;
---
--- Track user activity and reset them
---
-SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-CREATE ROLE regress_stats_user1;
-CREATE ROLE regress_stats_user2;
-SET ROLE regress_stats_user1;
-SELECT 1 AS "ONE";
- ONE 
------
-   1
-(1 row)
-
-SELECT 1+1 AS "TWO";
- TWO 
------
-   2
-(1 row)
-
-RESET ROLE;
-SET ROLE regress_stats_user2;
-SELECT 1 AS "ONE";
- ONE 
------
-   1
-(1 row)
-
-SELECT 1+1 AS "TWO";
- TWO 
------
-   2
-(1 row)
-
-RESET ROLE;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                    query                                     | calls | rows 
-------------------------------------------------------------------------------+-------+------
- CREATE ROLE regress_stats_user1                                              |     1 |    0
- CREATE ROLE regress_stats_user2                                              |     1 |    0
- RESET ROLE                                                                   |     2 |    0
- SELECT $1 AS "ONE"                                                           |     1 |    1
- SELECT $1 AS "ONE"                                                           |     1 |    1
- SELECT $1+$2 AS "TWO"                                                        |     1 |    1
- SELECT $1+$2 AS "TWO"                                                        |     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 ROLE regress_stats_user1                                                 |     1 |    0
- SET ROLE regress_stats_user2                                                 |     1 |    0
-(11 rows)
-
---
--- Don't reset anything if any of the parameter is NULL
---
-SELECT pg_stat_statements_reset(NULL);
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                    query                                     | calls | rows 
-------------------------------------------------------------------------------+-------+------
- CREATE ROLE regress_stats_user1                                              |     1 |    0
- CREATE ROLE regress_stats_user2                                              |     1 |    0
- RESET ROLE                                                                   |     2 |    0
- SELECT $1 AS "ONE"                                                           |     1 |    1
- SELECT $1 AS "ONE"                                                           |     1 |    1
- SELECT $1+$2 AS "TWO"                                                        |     1 |    1
- SELECT $1+$2 AS "TWO"                                                        |     1 |    1
- SELECT pg_stat_statements_reset($1)                                          |     1 |    1
- SELECT pg_stat_statements_reset()                                            |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |   11
- SET ROLE regress_stats_user1                                                 |     1 |    0
- SET ROLE regress_stats_user2                                                 |     1 |    0
-(12 rows)
-
---
--- remove query ('SELECT $1+$2 AS "TWO"') executed by regress_stats_user2
--- in the current_database
---
-SELECT pg_stat_statements_reset(
-	(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'),
-	(SELECT d.oid FROM pg_database As d where datname = current_database()),
-	(SELECT s.queryid FROM pg_stat_statements AS s
-				WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1));
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                      query                                       | calls | rows 
-----------------------------------------------------------------------------------+-------+------
- CREATE ROLE regress_stats_user1                                                  |     1 |    0
- CREATE ROLE regress_stats_user2                                                  |     1 |    0
- RESET ROLE                                                                       |     2 |    0
- SELECT $1 AS "ONE"                                                               |     1 |    1
- SELECT $1 AS "ONE"                                                               |     1 |    1
- SELECT $1+$2 AS "TWO"                                                            |     1 |    1
- SELECT pg_stat_statements_reset(                                                +|     1 |    1
-         (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1),                 +|       | 
-         (SELECT d.oid FROM pg_database As d where datname = current_database()),+|       | 
-         (SELECT s.queryid FROM pg_stat_statements AS s                          +|       | 
-                                 WHERE s.query = $2 LIMIT $3))                    |       | 
- SELECT pg_stat_statements_reset($1)                                              |     1 |    1
- SELECT pg_stat_statements_reset()                                                |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"     |     2 |   23
- SET ROLE regress_stats_user1                                                     |     1 |    0
- SET ROLE regress_stats_user2                                                     |     1 |    0
-(12 rows)
-
---
--- remove query ('SELECT $1 AS "ONE"') executed by two users
---
-SELECT pg_stat_statements_reset(0,0,s.queryid)
-	FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
- pg_stat_statements_reset 
---------------------------
- 
- 
-(2 rows)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                      query                                       | calls | rows 
-----------------------------------------------------------------------------------+-------+------
- CREATE ROLE regress_stats_user1                                                  |     1 |    0
- CREATE ROLE regress_stats_user2                                                  |     1 |    0
- RESET ROLE                                                                       |     2 |    0
- SELECT $1+$2 AS "TWO"                                                            |     1 |    1
- SELECT pg_stat_statements_reset(                                                +|     1 |    1
-         (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1),                 +|       | 
-         (SELECT d.oid FROM pg_database As d where datname = current_database()),+|       | 
-         (SELECT s.queryid FROM pg_stat_statements AS s                          +|       | 
-                                 WHERE s.query = $2 LIMIT $3))                    |       | 
- SELECT pg_stat_statements_reset($1)                                              |     1 |    1
- SELECT pg_stat_statements_reset($1,$2,s.queryid)                                +|     1 |    2
-         FROM pg_stat_statements AS s WHERE s.query = $3                          |       | 
- SELECT pg_stat_statements_reset()                                                |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"     |     3 |   35
- SET ROLE regress_stats_user1                                                     |     1 |    0
- SET ROLE regress_stats_user2                                                     |     1 |    0
-(11 rows)
-
---
--- remove query of a user (regress_stats_user1)
---
-SELECT pg_stat_statements_reset(r.oid)
-		FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                      query                                       | calls | rows 
-----------------------------------------------------------------------------------+-------+------
- CREATE ROLE regress_stats_user1                                                  |     1 |    0
- CREATE ROLE regress_stats_user2                                                  |     1 |    0
- RESET ROLE                                                                       |     2 |    0
- SELECT pg_stat_statements_reset(                                                +|     1 |    1
-         (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1),                 +|       | 
-         (SELECT d.oid FROM pg_database As d where datname = current_database()),+|       | 
-         (SELECT s.queryid FROM pg_stat_statements AS s                          +|       | 
-                                 WHERE s.query = $2 LIMIT $3))                    |       | 
- SELECT pg_stat_statements_reset($1)                                              |     1 |    1
- SELECT pg_stat_statements_reset($1,$2,s.queryid)                                +|     1 |    2
-         FROM pg_stat_statements AS s WHERE s.query = $3                          |       | 
- SELECT pg_stat_statements_reset()                                                |     1 |    1
- SELECT pg_stat_statements_reset(r.oid)                                          +|     1 |    1
-                 FROM pg_roles AS r WHERE r.rolname = $1                          |       | 
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"     |     4 |   46
- SET ROLE regress_stats_user2                                                     |     1 |    0
-(10 rows)
-
---
--- reset all
---
-SELECT pg_stat_statements_reset(0,0,0);
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                    query                                     | calls | rows 
-------------------------------------------------------------------------------+-------+------
- SELECT pg_stat_statements_reset(0,0,0)                                       |     1 |    1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
-(2 rows)
-
---
--- cleanup
---
-DROP ROLE regress_stats_user1;
-DROP ROLE regress_stats_user2;
---
--- access to pg_stat_statements_info view
---
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset 
---------------------------
- 
-(1 row)
-
-SELECT dealloc FROM pg_stat_statements_info;
- dealloc 
----------
-       0
-(1 row)
-
--- 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 *
-FROM limitoption
-WHERE val < 2
-ORDER BY val
-FETCH FIRST 2 ROWS WITH TIES;
- val 
------
-   0
-   0
-   0
-   0
-   0
-   0
-   0
-   0
-   0
-   0
-(10 rows)
-
-SELECT *
-FROM limitoption
-WHERE val < 2
-ORDER BY val
-FETCH FIRST 2 ROW ONLY;
- val 
------
-   0
-   0
-(2 rows)
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
- count 
--------
-     2
-(1 row)
-
--- GROUP BY [DISTINCT]
-SELECT a, b, c
-FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
-GROUP BY ROLLUP(a, b), rollup(a, c)
-ORDER BY a, b, c;
- a | b | c 
----+---+---
- 1 | 2 | 3
- 1 | 2 |  
- 1 | 2 |  
- 1 |   | 3
- 1 |   | 3
- 1 |   |  
- 1 |   |  
- 1 |   |  
- 4 |   | 6
- 4 |   | 6
- 4 |   | 6
- 4 |   |  
- 4 |   |  
- 4 |   |  
- 4 |   |  
- 4 |   |  
- 7 | 8 | 9
- 7 | 8 |  
- 7 | 8 |  
- 7 |   | 9
- 7 |   | 9
- 7 |   |  
- 7 |   |  
- 7 |   |  
-   |   |  
-(25 rows)
-
-SELECT a, b, c
-FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
-GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
-ORDER BY a, b, c;
- a | b | c 
----+---+---
- 1 | 2 | 3
- 1 | 2 |  
- 1 |   | 3
- 1 |   |  
- 4 |   | 6
- 4 |   | 6
- 4 |   |  
- 4 |   |  
- 7 | 8 | 9
- 7 | 8 |  
- 7 |   | 9
- 7 |   |  
-   |   |  
-(13 rows)
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
- count 
--------
-     2
-(1 row)
-
--- GROUPING SET agglevelsup
-SELECT (
-  SELECT (
-    SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
-  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
-) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
- grouping 
-----------
-        0
-        0
-        0
-(3 rows)
-
-SELECT (
-  SELECT (
-    SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
-  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
-) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
- grouping 
-----------
-        3
-        0
-        1
-(3 rows)
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
- count 
--------
-     2
-(1 row)
-
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
new file mode 100644
index 0000000000..a958829883
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -0,0 +1,411 @@
+CREATE EXTENSION pg_stat_statements;
+--
+-- simple and compound statements
+--
+SET pg_stat_statements.track_utility = FALSE;
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT 1 AS "int";
+ int 
+-----
+   1
+(1 row)
+
+SELECT 'hello'
+  -- multiline
+  AS "text";
+ text  
+-------
+ hello
+(1 row)
+
+SELECT 'world' AS "text";
+ text  
+-------
+ world
+(1 row)
+
+-- transaction
+BEGIN;
+SELECT 1 AS "int";
+ int 
+-----
+   1
+(1 row)
+
+SELECT 'hello' AS "text";
+ text  
+-------
+ hello
+(1 row)
+
+COMMIT;
+-- compound transaction
+BEGIN \;
+SELECT 2.0 AS "float" \;
+SELECT 'world' AS "text" \;
+COMMIT;
+ float 
+-------
+   2.0
+(1 row)
+
+ text  
+-------
+ world
+(1 row)
+
+-- compound with empty statements and spurious leading spacing
+\;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
+ ?column? 
+----------
+        6
+(1 row)
+
+ ?column? 
+----------
+   !
+(1 row)
+
+ ?column? 
+----------
+        5
+(1 row)
+
+-- non ;-terminated statements
+SELECT 1 + 1 + 1 AS "add" \gset
+SELECT :add + 1 + 1 AS "add" \;
+SELECT :add + 1 + 1 AS "add" \gset
+ add 
+-----
+   5
+(1 row)
+
+-- set operator
+SELECT 1 AS i UNION SELECT 2 ORDER BY i;
+ i 
+---
+ 1
+ 2
+(2 rows)
+
+-- ? operator
+select '{"a":1, "b":2}'::jsonb ? 'b';
+ ?column? 
+----------
+ t
+(1 row)
+
+-- cte
+WITH t(f) AS (
+  VALUES (1.0), (2.0)
+)
+  SELECT f FROM t ORDER BY f;
+  f  
+-----
+ 1.0
+ 2.0
+(2 rows)
+
+-- prepared statement with parameter
+PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
+EXECUTE pgss_test(1);
+ ?column? | ?column? 
+----------+----------
+        1 | test
+(1 row)
+
+DEALLOCATE pgss_test;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | rows |                                    query                                     
+-------+------+------------------------------------------------------------------------------
+     1 |    1 | PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3
+     4 |    4 | SELECT $1                                                                   +
+       |      |   -- multiline                                                              +
+       |      |   AS "text"
+     2 |    2 | SELECT $1 + $2
+     3 |    3 | SELECT $1 + $2 + $3 AS "add"
+     1 |    1 | SELECT $1 AS "float"
+     2 |    2 | SELECT $1 AS "int"
+     1 |    2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i
+     1 |    1 | SELECT $1 || $2
+     0 |    0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
+     1 |    1 | SELECT pg_stat_statements_reset()
+     1 |    2 | WITH t(f) AS (                                                              +
+       |      |   VALUES ($1), ($2)                                                         +
+       |      | )                                                                           +
+       |      |   SELECT f FROM t ORDER BY f
+     1 |    1 | select $1::jsonb ? $2
+(12 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+--
+-- queries with locking clauses
+--
+CREATE TABLE pgss_a (id integer PRIMARY KEY);
+CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- control query
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+-- test range tables
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a, pgss_b; -- matches plain "FOR UPDATE"
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+-- test strengths
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+-- test wait policies
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED;
+ id | id | a_id 
+----+----+------
+(0 rows)
+
+SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls |                                          query                                           
+-------+------------------------------------------------------------------------------------------
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE
+     2 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a
+     1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED
+     0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"
+     1 | SELECT pg_stat_statements_reset()
+(12 rows)
+
+DROP TABLE pgss_a, pgss_b CASCADE;
+--
+-- access to pg_stat_statements_info view
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT dealloc FROM pg_stat_statements_info;
+ dealloc 
+---------
+       0
+(1 row)
+
+-- 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 *
+FROM limitoption
+WHERE val < 2
+ORDER BY val
+FETCH FIRST 2 ROWS WITH TIES;
+ val 
+-----
+   0
+   0
+   0
+   0
+   0
+   0
+   0
+   0
+   0
+   0
+(10 rows)
+
+SELECT *
+FROM limitoption
+WHERE val < 2
+ORDER BY val
+FETCH FIRST 2 ROW ONLY;
+ val 
+-----
+   0
+   0
+(2 rows)
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
+ count 
+-------
+     2
+(1 row)
+
+-- GROUP BY [DISTINCT]
+SELECT a, b, c
+FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
+GROUP BY ROLLUP(a, b), rollup(a, c)
+ORDER BY a, b, c;
+ a | b | c 
+---+---+---
+ 1 | 2 | 3
+ 1 | 2 |  
+ 1 | 2 |  
+ 1 |   | 3
+ 1 |   | 3
+ 1 |   |  
+ 1 |   |  
+ 1 |   |  
+ 4 |   | 6
+ 4 |   | 6
+ 4 |   | 6
+ 4 |   |  
+ 4 |   |  
+ 4 |   |  
+ 4 |   |  
+ 4 |   |  
+ 7 | 8 | 9
+ 7 | 8 |  
+ 7 | 8 |  
+ 7 |   | 9
+ 7 |   | 9
+ 7 |   |  
+ 7 |   |  
+ 7 |   |  
+   |   |  
+(25 rows)
+
+SELECT a, b, c
+FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
+GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
+ORDER BY a, b, c;
+ a | b | c 
+---+---+---
+ 1 | 2 | 3
+ 1 | 2 |  
+ 1 |   | 3
+ 1 |   |  
+ 4 |   | 6
+ 4 |   | 6
+ 4 |   |  
+ 4 |   |  
+ 7 | 8 | 9
+ 7 | 8 |  
+ 7 |   | 9
+ 7 |   |  
+   |   |  
+(13 rows)
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
+ count 
+-------
+     2
+(1 row)
+
+-- GROUPING SET agglevelsup
+SELECT (
+  SELECT (
+    SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
+  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
+) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
+ grouping 
+----------
+        0
+        0
+        0
+(3 rows)
+
+SELECT (
+  SELECT (
+    SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
+  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
+) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
+ grouping 
+----------
+        3
+        0
+        1
+(3 rows)
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
+ count 
+-------
+     2
+(1 row)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
diff --git a/contrib/pg_stat_statements/expected/user_activity.out b/contrib/pg_stat_statements/expected/user_activity.out
new file mode 100644
index 0000000000..f06e5881e5
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/user_activity.out
@@ -0,0 +1,199 @@
+--
+-- Track user activity and reset them
+--
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+CREATE ROLE regress_stats_user1;
+CREATE ROLE regress_stats_user2;
+SET ROLE regress_stats_user1;
+SELECT 1 AS "ONE";
+ ONE 
+-----
+   1
+(1 row)
+
+SELECT 1+1 AS "TWO";
+ TWO 
+-----
+   2
+(1 row)
+
+RESET ROLE;
+SET ROLE regress_stats_user2;
+SELECT 1 AS "ONE";
+ ONE 
+-----
+   1
+(1 row)
+
+SELECT 1+1 AS "TWO";
+ TWO 
+-----
+   2
+(1 row)
+
+RESET ROLE;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+               query               | calls | rows 
+-----------------------------------+-------+------
+ CREATE ROLE regress_stats_user1   |     1 |    0
+ CREATE ROLE regress_stats_user2   |     1 |    0
+ RESET ROLE                        |     2 |    0
+ SELECT $1 AS "ONE"                |     1 |    1
+ SELECT $1 AS "ONE"                |     1 |    1
+ SELECT $1+$2 AS "TWO"             |     1 |    1
+ SELECT $1+$2 AS "TWO"             |     1 |    1
+ SELECT pg_stat_statements_reset() |     1 |    1
+ SET ROLE regress_stats_user1      |     1 |    0
+ SET ROLE regress_stats_user2      |     1 |    0
+(10 rows)
+
+--
+-- Don't reset anything if any of the parameter is NULL
+--
+SELECT pg_stat_statements_reset(NULL);
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                    query                                     | calls | rows 
+------------------------------------------------------------------------------+-------+------
+ CREATE ROLE regress_stats_user1                                              |     1 |    0
+ CREATE ROLE regress_stats_user2                                              |     1 |    0
+ RESET ROLE                                                                   |     2 |    0
+ SELECT $1 AS "ONE"                                                           |     1 |    1
+ SELECT $1 AS "ONE"                                                           |     1 |    1
+ SELECT $1+$2 AS "TWO"                                                        |     1 |    1
+ SELECT $1+$2 AS "TWO"                                                        |     1 |    1
+ SELECT pg_stat_statements_reset($1)                                          |     1 |    1
+ SELECT pg_stat_statements_reset()                                            |     1 |    1
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |   10
+ SET ROLE regress_stats_user1                                                 |     1 |    0
+ SET ROLE regress_stats_user2                                                 |     1 |    0
+(12 rows)
+
+--
+-- remove query ('SELECT $1+$2 AS "TWO"') executed by regress_stats_user2
+-- in the current_database
+--
+SELECT pg_stat_statements_reset(
+	(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'),
+	(SELECT d.oid FROM pg_database As d where datname = current_database()),
+	(SELECT s.queryid FROM pg_stat_statements AS s
+				WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1));
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                      query                                       | calls | rows 
+----------------------------------------------------------------------------------+-------+------
+ CREATE ROLE regress_stats_user1                                                  |     1 |    0
+ CREATE ROLE regress_stats_user2                                                  |     1 |    0
+ RESET ROLE                                                                       |     2 |    0
+ SELECT $1 AS "ONE"                                                               |     1 |    1
+ SELECT $1 AS "ONE"                                                               |     1 |    1
+ SELECT $1+$2 AS "TWO"                                                            |     1 |    1
+ SELECT pg_stat_statements_reset(                                                +|     1 |    1
+         (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1),                 +|       | 
+         (SELECT d.oid FROM pg_database As d where datname = current_database()),+|       | 
+         (SELECT s.queryid FROM pg_stat_statements AS s                          +|       | 
+                                 WHERE s.query = $2 LIMIT $3))                    |       | 
+ SELECT pg_stat_statements_reset($1)                                              |     1 |    1
+ SELECT pg_stat_statements_reset()                                                |     1 |    1
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"     |     2 |   22
+ SET ROLE regress_stats_user1                                                     |     1 |    0
+ SET ROLE regress_stats_user2                                                     |     1 |    0
+(12 rows)
+
+--
+-- remove query ('SELECT $1 AS "ONE"') executed by two users
+--
+SELECT pg_stat_statements_reset(0,0,s.queryid)
+	FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
+ pg_stat_statements_reset 
+--------------------------
+ 
+ 
+(2 rows)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                      query                                       | calls | rows 
+----------------------------------------------------------------------------------+-------+------
+ CREATE ROLE regress_stats_user1                                                  |     1 |    0
+ CREATE ROLE regress_stats_user2                                                  |     1 |    0
+ RESET ROLE                                                                       |     2 |    0
+ SELECT $1+$2 AS "TWO"                                                            |     1 |    1
+ SELECT pg_stat_statements_reset(                                                +|     1 |    1
+         (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1),                 +|       | 
+         (SELECT d.oid FROM pg_database As d where datname = current_database()),+|       | 
+         (SELECT s.queryid FROM pg_stat_statements AS s                          +|       | 
+                                 WHERE s.query = $2 LIMIT $3))                    |       | 
+ SELECT pg_stat_statements_reset($1)                                              |     1 |    1
+ SELECT pg_stat_statements_reset($1,$2,s.queryid)                                +|     1 |    2
+         FROM pg_stat_statements AS s WHERE s.query = $3                          |       | 
+ SELECT pg_stat_statements_reset()                                                |     1 |    1
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"     |     3 |   34
+ SET ROLE regress_stats_user1                                                     |     1 |    0
+ SET ROLE regress_stats_user2                                                     |     1 |    0
+(11 rows)
+
+--
+-- remove query of a user (regress_stats_user1)
+--
+SELECT pg_stat_statements_reset(r.oid)
+		FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                      query                                       | calls | rows 
+----------------------------------------------------------------------------------+-------+------
+ CREATE ROLE regress_stats_user1                                                  |     1 |    0
+ CREATE ROLE regress_stats_user2                                                  |     1 |    0
+ RESET ROLE                                                                       |     2 |    0
+ SELECT pg_stat_statements_reset(                                                +|     1 |    1
+         (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1),                 +|       | 
+         (SELECT d.oid FROM pg_database As d where datname = current_database()),+|       | 
+         (SELECT s.queryid FROM pg_stat_statements AS s                          +|       | 
+                                 WHERE s.query = $2 LIMIT $3))                    |       | 
+ SELECT pg_stat_statements_reset($1)                                              |     1 |    1
+ SELECT pg_stat_statements_reset($1,$2,s.queryid)                                +|     1 |    2
+         FROM pg_stat_statements AS s WHERE s.query = $3                          |       | 
+ SELECT pg_stat_statements_reset()                                                |     1 |    1
+ SELECT pg_stat_statements_reset(r.oid)                                          +|     1 |    1
+                 FROM pg_roles AS r WHERE r.rolname = $1                          |       | 
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"     |     4 |   45
+ SET ROLE regress_stats_user2                                                     |     1 |    0
+(10 rows)
+
+--
+-- reset all
+--
+SELECT pg_stat_statements_reset(0,0,0);
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                 query                  | calls | rows 
+----------------------------------------+-------+------
+ SELECT pg_stat_statements_reset(0,0,0) |     1 |    1
+(1 row)
+
+--
+-- cleanup
+--
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
diff --git a/contrib/pg_stat_statements/expected/wal.out b/contrib/pg_stat_statements/expected/wal.out
new file mode 100644
index 0000000000..1bfb84fc33
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/wal.out
@@ -0,0 +1,35 @@
+--
+-- Validate WAL generation metrics
+--
+SET pg_stat_statements.track_utility = FALSE;
+-- utility "create table" should not be shown
+CREATE TABLE pgss_wal_tab (a int, b char(20));
+INSERT INTO pgss_wal_tab VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_wal_tab SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_wal_tab WHERE a > 9;
+-- DROP test table
+DROP TABLE pgss_wal_tab;
+-- Check WAL is generated for the above statements
+SELECT query, calls, rows,
+wal_bytes > 0 as wal_bytes_generated,
+wal_records > 0 as wal_records_generated,
+wal_records >= rows as wal_records_ge_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                    query                                     | calls | rows | wal_bytes_generated | wal_records_generated | wal_records_ge_rows 
+------------------------------------------------------------------------------+-------+------+---------------------+-----------------------+---------------------
+ DELETE FROM pgss_wal_tab WHERE a > $1                                        |     1 |    1 | t                   | t                     | t
+ DROP ROLE regress_stats_user1                                                |     1 |    0 | t                   | t                     | t
+ DROP ROLE regress_stats_user2                                                |     1 |    0 | t                   | t                     | t
+ INSERT INTO pgss_wal_tab VALUES(generate_series($1, $2), $3)                 |     1 |   10 | t                   | t                     | t
+ SELECT pg_stat_statements_reset(0,0,0)                                       |     1 |    1 | f                   | f                     | f
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |    1 | f                   | f                     | f
+ SET pg_stat_statements.track_utility = FALSE                                 |     1 |    0 | f                   | f                     | t
+ UPDATE pgss_wal_tab SET b = $1 WHERE a > $2                                  |     1 |    3 | t                   | t                     | t
+(8 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 10ccc26300..3e3062ada9 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -40,11 +40,14 @@ tests += {
   'bd': meson.current_build_dir(),
   'regress': {
     'sql': [
-      'pg_stat_statements',
+      'select',
+      'dml',
       'cursors',
       'utility',
       'level_tracking',
       'planning',
+      'user_activity',
+      'wal',
       'cleanup',
       'oldextversions',
     ],
diff --git a/contrib/pg_stat_statements/sql/dml.sql b/contrib/pg_stat_statements/sql/dml.sql
new file mode 100644
index 0000000000..74fb717023
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/dml.sql
@@ -0,0 +1,78 @@
+--
+-- DMLs on test table
+--
+
+SET pg_stat_statements.track_utility = FALSE;
+
+-- utility "create table" should not be shown
+CREATE TEMP TABLE pgss_dml_tab (a int, b char(20));
+
+INSERT INTO pgss_dml_tab VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_dml_tab SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_dml_tab WHERE a > 9;
+
+-- explicit transaction
+BEGIN;
+UPDATE pgss_dml_tab SET b = '111' WHERE a = 1 ;
+COMMIT;
+
+BEGIN \;
+UPDATE pgss_dml_tab SET b = '222' WHERE a = 2 \;
+COMMIT ;
+
+UPDATE pgss_dml_tab SET b = '333' WHERE a = 3 \;
+UPDATE pgss_dml_tab SET b = '444' WHERE a = 4 ;
+
+BEGIN \;
+UPDATE pgss_dml_tab SET b = '555' WHERE a = 5 \;
+UPDATE pgss_dml_tab SET b = '666' WHERE a = 6 \;
+COMMIT ;
+
+-- many INSERT values
+INSERT INTO pgss_dml_tab (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
+
+-- SELECT with constants
+SELECT * FROM pgss_dml_tab WHERE a > 5 ORDER BY a ;
+
+SELECT *
+  FROM pgss_dml_tab
+  WHERE a > 9
+  ORDER BY a ;
+
+-- these two need to be done on a different table
+-- SELECT without constants
+SELECT * FROM pgss_dml_tab ORDER BY a;
+
+-- SELECT with IN clause
+SELECT * FROM pgss_dml_tab WHERE a IN (1, 2, 3, 4, 5);
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- MERGE
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT VALUES (0, NULL);	-- same as above
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
+ WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN DELETE;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN MATCHED THEN DO NOTHING;
+MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
+ WHEN NOT MATCHED THEN DO NOTHING;
+
+DROP TABLE pgss_dml_tab;
+
+SELECT calls, rows, query 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
deleted file mode 100644
index 3a3d235066..0000000000
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ /dev/null
@@ -1,300 +0,0 @@
-CREATE EXTENSION pg_stat_statements;
-
---
--- simple and compound statements
---
-SET pg_stat_statements.track_utility = FALSE;
-SET pg_stat_statements.track_planning = TRUE;
-SELECT pg_stat_statements_reset();
-
-SELECT 1 AS "int";
-
-SELECT 'hello'
-  -- multiline
-  AS "text";
-
-SELECT 'world' AS "text";
-
--- transaction
-BEGIN;
-SELECT 1 AS "int";
-SELECT 'hello' AS "text";
-COMMIT;
-
--- compound transaction
-BEGIN \;
-SELECT 2.0 AS "float" \;
-SELECT 'world' AS "text" \;
-COMMIT;
-
--- compound with empty statements and spurious leading spacing
-\;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
-
--- non ;-terminated statements
-SELECT 1 + 1 + 1 AS "add" \gset
-SELECT :add + 1 + 1 AS "add" \;
-SELECT :add + 1 + 1 AS "add" \gset
-
--- set operator
-SELECT 1 AS i UNION SELECT 2 ORDER BY i;
-
--- ? operator
-select '{"a":1, "b":2}'::jsonb ? 'b';
-
--- cte
-WITH t(f) AS (
-  VALUES (1.0), (2.0)
-)
-  SELECT f FROM t ORDER BY f;
-
--- prepared statement with parameter
-PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
-EXECUTE pgss_test(1);
-DEALLOCATE pgss_test;
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- CRUD: INSERT SELECT UPDATE DELETE on test table
---
-SELECT pg_stat_statements_reset();
-
--- utility "create table" should not be shown
-CREATE TEMP TABLE test (a int, b char(20));
-
-INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
-UPDATE test SET b = 'bbb' WHERE a > 7;
-DELETE FROM test WHERE a > 9;
-
--- explicit transaction
-BEGIN;
-UPDATE test SET b = '111' WHERE a = 1 ;
-COMMIT;
-
-BEGIN \;
-UPDATE test SET b = '222' WHERE a = 2 \;
-COMMIT ;
-
-UPDATE test SET b = '333' WHERE a = 3 \;
-UPDATE test SET b = '444' WHERE a = 4 ;
-
-BEGIN \;
-UPDATE test SET b = '555' WHERE a = 5 \;
-UPDATE test SET b = '666' WHERE a = 6 \;
-COMMIT ;
-
--- many INSERT values
-INSERT INTO test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
-
--- SELECT with constants
-SELECT * FROM test WHERE a > 5 ORDER BY a ;
-
-SELECT *
-  FROM test
-  WHERE a > 9
-  ORDER BY a ;
-
--- SELECT without constants
-SELECT * FROM test ORDER BY a;
-
--- SELECT with IN clause
-SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
-
--- MERGE
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = test.b || st.a::text;
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT VALUES (0, NULL);	-- same as above
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
-MERGE INTO test USING test st ON (st.a = test.a)
- WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN DELETE;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN MATCHED THEN DO NOTHING;
-MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
- WHEN NOT MATCHED THEN DO NOTHING;
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
---
-SELECT pg_stat_statements_reset();
-
--- utility "create table" should not be shown
-CREATE TABLE pgss_test (a int, b char(20));
-
-INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
-UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
-DELETE FROM pgss_test WHERE a > 9;
--- DROP test table
-DROP TABLE pgss_test;
-
--- Check WAL is generated for the above statements
-SELECT query, calls, rows,
-wal_bytes > 0 as wal_bytes_generated,
-wal_records > 0 as wal_records_generated,
-wal_records >= rows as wal_records_ge_rows
-FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- queries with locking clauses
---
-CREATE TABLE pgss_a (id integer PRIMARY KEY);
-CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
-
-SELECT pg_stat_statements_reset();
-
--- control query
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
-
--- test range tables
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE;
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a;
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b;
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a, pgss_b; -- matches plain "FOR UPDATE"
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a;
-
--- test strengths
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE;
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE;
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE;
-
--- test wait policies
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT;
-SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED;
-
-SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-
-DROP TABLE pgss_a, pgss_b CASCADE;
-
---
--- 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;
-
-SET ROLE regress_stats_user1;
-
-SELECT 1 AS "ONE";
-SELECT 1+1 AS "TWO";
-
-RESET ROLE;
-SET ROLE regress_stats_user2;
-
-SELECT 1 AS "ONE";
-SELECT 1+1 AS "TWO";
-
-RESET ROLE;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- Don't reset anything if any of the parameter is NULL
---
-SELECT pg_stat_statements_reset(NULL);
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- remove query ('SELECT $1+$2 AS "TWO"') executed by regress_stats_user2
--- in the current_database
---
-SELECT pg_stat_statements_reset(
-	(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'),
-	(SELECT d.oid FROM pg_database As d where datname = current_database()),
-	(SELECT s.queryid FROM pg_stat_statements AS s
-				WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1));
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- remove query ('SELECT $1 AS "ONE"') executed by two users
---
-SELECT pg_stat_statements_reset(0,0,s.queryid)
-	FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- remove query of a user (regress_stats_user1)
---
-SELECT pg_stat_statements_reset(r.oid)
-		FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- reset all
---
-SELECT pg_stat_statements_reset(0,0,0);
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- cleanup
---
-DROP ROLE regress_stats_user1;
-DROP ROLE regress_stats_user2;
-
---
--- access to pg_stat_statements_info view
---
-SELECT pg_stat_statements_reset();
-SELECT dealloc FROM pg_stat_statements_info;
-
--- 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 *
-FROM limitoption
-WHERE val < 2
-ORDER BY val
-FETCH FIRST 2 ROWS WITH TIES;
-
-SELECT *
-FROM limitoption
-WHERE val < 2
-ORDER BY val
-FETCH FIRST 2 ROW ONLY;
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
-
--- GROUP BY [DISTINCT]
-SELECT a, b, c
-FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
-GROUP BY ROLLUP(a, b), rollup(a, c)
-ORDER BY a, b, c;
-SELECT a, b, c
-FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
-GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
-ORDER BY a, b, c;
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
-
--- GROUPING SET agglevelsup
-SELECT (
-  SELECT (
-    SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
-  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
-) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
-SELECT (
-  SELECT (
-    SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
-  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
-) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
-
-SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
new file mode 100644
index 0000000000..09fb5154e8
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -0,0 +1,146 @@
+CREATE EXTENSION pg_stat_statements;
+
+--
+-- simple and compound statements
+--
+SET pg_stat_statements.track_utility = FALSE;
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+
+SELECT 1 AS "int";
+
+SELECT 'hello'
+  -- multiline
+  AS "text";
+
+SELECT 'world' AS "text";
+
+-- transaction
+BEGIN;
+SELECT 1 AS "int";
+SELECT 'hello' AS "text";
+COMMIT;
+
+-- compound transaction
+BEGIN \;
+SELECT 2.0 AS "float" \;
+SELECT 'world' AS "text" \;
+COMMIT;
+
+-- compound with empty statements and spurious leading spacing
+\;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
+
+-- non ;-terminated statements
+SELECT 1 + 1 + 1 AS "add" \gset
+SELECT :add + 1 + 1 AS "add" \;
+SELECT :add + 1 + 1 AS "add" \gset
+
+-- set operator
+SELECT 1 AS i UNION SELECT 2 ORDER BY i;
+
+-- ? operator
+select '{"a":1, "b":2}'::jsonb ? 'b';
+
+-- cte
+WITH t(f) AS (
+  VALUES (1.0), (2.0)
+)
+  SELECT f FROM t ORDER BY f;
+
+-- prepared statement with parameter
+PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
+EXECUTE pgss_test(1);
+DEALLOCATE pgss_test;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+--
+-- queries with locking clauses
+--
+CREATE TABLE pgss_a (id integer PRIMARY KEY);
+CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
+
+SELECT pg_stat_statements_reset();
+
+-- control query
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
+
+-- test range tables
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE;
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a;
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b;
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a, pgss_b; -- matches plain "FOR UPDATE"
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a;
+
+-- test strengths
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE;
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE;
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE;
+
+-- test wait policies
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT;
+SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED;
+
+SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+DROP TABLE pgss_a, pgss_b CASCADE;
+
+--
+-- access to pg_stat_statements_info view
+--
+SELECT pg_stat_statements_reset();
+SELECT dealloc FROM pg_stat_statements_info;
+
+-- 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 *
+FROM limitoption
+WHERE val < 2
+ORDER BY val
+FETCH FIRST 2 ROWS WITH TIES;
+
+SELECT *
+FROM limitoption
+WHERE val < 2
+ORDER BY val
+FETCH FIRST 2 ROW ONLY;
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
+
+-- GROUP BY [DISTINCT]
+SELECT a, b, c
+FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
+GROUP BY ROLLUP(a, b), rollup(a, c)
+ORDER BY a, b, c;
+SELECT a, b, c
+FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
+GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
+ORDER BY a, b, c;
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
+
+-- GROUPING SET agglevelsup
+SELECT (
+  SELECT (
+    SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
+  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
+) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
+SELECT (
+  SELECT (
+    SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
+  ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
+) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/user_activity.sql b/contrib/pg_stat_statements/sql/user_activity.sql
new file mode 100644
index 0000000000..8ceabe3924
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/user_activity.sql
@@ -0,0 +1,65 @@
+--
+-- 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;
+
+SET ROLE regress_stats_user1;
+
+SELECT 1 AS "ONE";
+SELECT 1+1 AS "TWO";
+
+RESET ROLE;
+SET ROLE regress_stats_user2;
+
+SELECT 1 AS "ONE";
+SELECT 1+1 AS "TWO";
+
+RESET ROLE;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- Don't reset anything if any of the parameter is NULL
+--
+SELECT pg_stat_statements_reset(NULL);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- remove query ('SELECT $1+$2 AS "TWO"') executed by regress_stats_user2
+-- in the current_database
+--
+SELECT pg_stat_statements_reset(
+	(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'),
+	(SELECT d.oid FROM pg_database As d where datname = current_database()),
+	(SELECT s.queryid FROM pg_stat_statements AS s
+				WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1));
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- remove query ('SELECT $1 AS "ONE"') executed by two users
+--
+SELECT pg_stat_statements_reset(0,0,s.queryid)
+	FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- remove query of a user (regress_stats_user1)
+--
+SELECT pg_stat_statements_reset(r.oid)
+		FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- reset all
+--
+SELECT pg_stat_statements_reset(0,0,0);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- cleanup
+--
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+
diff --git a/contrib/pg_stat_statements/sql/wal.sql b/contrib/pg_stat_statements/sql/wal.sql
new file mode 100644
index 0000000000..143560f3d6
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/wal.sql
@@ -0,0 +1,22 @@
+--
+-- Validate WAL generation metrics
+--
+
+SET pg_stat_statements.track_utility = FALSE;
+
+-- utility "create table" should not be shown
+CREATE TABLE pgss_wal_tab (a int, b char(20));
+
+INSERT INTO pgss_wal_tab VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_wal_tab SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_wal_tab WHERE a > 9;
+-- DROP test table
+DROP TABLE pgss_wal_tab;
+
+-- Check WAL is generated for the above statements
+SELECT query, calls, rows,
+wal_bytes > 0 as wal_bytes_generated,
+wal_records > 0 as wal_records_generated,
+wal_records >= rows as wal_records_ge_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
-- 
2.39.2

From e76d5c33363ce8c3548156240a1062ee6378e02a Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 1 Mar 2023 13:39:12 +0900
Subject: [PATCH v5 2/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            |   4 +-
 .../pg_stat_statements/expected/cursors.out   |  14 +-
 contrib/pg_stat_statements/expected/dml.out   |   2 +-
 .../expected/level_tracking.out               |  20 +-
 .../pg_stat_statements/expected/utility.out   | 174 ++++++++----------
 contrib/pg_stat_statements/expected/wal.out   |   2 +-
 .../pg_stat_statements/pg_stat_statements.c   |   4 +-
 10 files changed, 118 insertions(+), 142 deletions(-)

index b4292253cc..4220c63ab7 100644

 static void
index b1214ee645..0b7fffadca 100644
@@ -490,7 +490,9 @@
    <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>)
    and utility commands are combined into a single
    appearing in the query.
 
@@ -49,14 +49,14 @@ CLOSE cursor_stats_1;
 CLOSE cursor_stats_2;
 COMMIT;
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows |                        query                         
--------+------+------------------------------------------------------
+ calls | rows |                         query                         
+-------+------+-------------------------------------------------------
      1 |    0 | 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 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1
+     1 |    0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1
      1 |    1 | FETCH 1 IN cursor_stats_1
      1 |    1 | FETCH 1 IN cursor_stats_2
      1 |    1 | SELECT pg_stat_statements_reset()
diff --git a/contrib/pg_stat_statements/expected/dml.out b/contrib/pg_stat_statements/expected/dml.out
index 803d993e85..d7cfb7fceb 100644
--- a/contrib/pg_stat_statements/expected/dml.out
+++ b/contrib/pg_stat_statements/expected/dml.out
@@ -83,7 +83,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
      2 |    4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
      1 |    8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5)
      1 |    1 | SELECT pg_stat_statements_reset()
-     1 |    0 | SET pg_stat_statements.track_utility = FALSE
+     1 |    0 | SET pg_stat_statements.track_utility = $1
      6 |    6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
      1 |    3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2
 (10 rows)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index c824ebdac5..615be0775f 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -49,22 +49,22 @@ BEGIN
 END; $$;
 SELECT toplevel, calls, query FROM pg_stat_statements
   ORDER BY query COLLATE "C", toplevel;
- toplevel | calls |                query                 
-----------+-------+--------------------------------------
+ toplevel | calls |               query               
+----------+-------+-----------------------------------
  f        |     1 | DELETE FROM stats_track_tab
  t        |     1 | DELETE FROM stats_track_tab
- t        |     1 | DO $$                               +
-          |       | BEGIN                               +
-          |       |   DELETE FROM stats_track_tab;      +
+ t        |     1 | DO $$                            +
+          |       | BEGIN                            +
+          |       |   DELETE FROM stats_track_tab;   +
           |       | END; $$
- t        |     1 | DO LANGUAGE plpgsql $$              +
-          |       | BEGIN                               +
-          |       |   -- this is a SELECT               +
-          |       |   PERFORM 'hello world'::TEXT;      +
+ t        |     1 | DO LANGUAGE plpgsql $$           +
+          |       | BEGIN                            +
+          |       |   -- this is a SELECT            +
+          |       |   PERFORM 'hello world'::TEXT;   +
           |       | END; $$
  f        |     1 | SELECT $1::TEXT
  t        |     1 | SELECT pg_stat_statements_reset()
- t        |     1 | SET pg_stat_statements.track = 'all'
+ t        |     1 | SET pg_stat_statements.track = $1
 (7 rows)

 
 
 -- COPY
 
 DROP TABLE copy_stats;

 
 COMMIT;

diff --git a/contrib/pg_stat_statements/expected/wal.out b/contrib/pg_stat_statements/expected/wal.out
index 1bfb84fc33..5c2c9c5ffa 100644
--- a/contrib/pg_stat_statements/expected/wal.out
+++ b/contrib/pg_stat_statements/expected/wal.out
@@ -23,7 +23,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
  INSERT INTO pgss_wal_tab VALUES(generate_series($1, $2), $3)                 |     1 |   10 | t                   | t                     | t
  SELECT pg_stat_statements_reset(0,0,0)                                       |     1 |    1 | f                   | f                     | f
  SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |    1 | f                   | f                     | f
- 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_wal_tab SET b = $1 WHERE a > $2                                  |     1 |    3 | t                   | t                     | t
 (8 rows)

 	/*
-- 
2.39.2

From a3a082ac37bb7c116fc60416d608d6da16192a6a Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 1 Mar 2023 13:40:34 +0900
Subject: [PATCH v5 3/3] Remove normalization of A_Const nodes

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 ++-
 contrib/pg_stat_statements/expected/dml.out   |   2 +-
 .../expected/level_tracking.out               |  20 +--
 .../pg_stat_statements/expected/utility.out   | 136 ++++++++++--------
 contrib/pg_stat_statements/expected/wal.out   |   2 +-
 5 files changed, 109 insertions(+), 74 deletions(-)

 static void
diff --git a/contrib/pg_stat_statements/expected/dml.out b/contrib/pg_stat_statements/expected/dml.out
index d7cfb7fceb..803d993e85 100644
--- a/contrib/pg_stat_statements/expected/dml.out
+++ b/contrib/pg_stat_statements/expected/dml.out
@@ -83,7 +83,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
      2 |    4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
      1 |    8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5)
      1 |    1 | SELECT pg_stat_statements_reset()
-     1 |    0 | SET pg_stat_statements.track_utility = $1
+     1 |    0 | SET pg_stat_statements.track_utility = FALSE
      6 |    6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
      1 |    3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2
 (10 rows)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 615be0775f..c824ebdac5 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -49,22 +49,22 @@ BEGIN
 END; $$;
 SELECT toplevel, calls, query FROM pg_stat_statements
   ORDER BY query COLLATE "C", toplevel;
- toplevel | calls |               query               
-----------+-------+-----------------------------------
+ toplevel | calls |                query                 
+----------+-------+--------------------------------------
  f        |     1 | DELETE FROM stats_track_tab
  t        |     1 | DELETE FROM stats_track_tab
- t        |     1 | DO $$                            +
-          |       | BEGIN                            +
-          |       |   DELETE FROM stats_track_tab;   +
+ t        |     1 | DO $$                               +
+          |       | BEGIN                               +
+          |       |   DELETE FROM stats_track_tab;      +
           |       | END; $$
- t        |     1 | DO LANGUAGE plpgsql $$           +
-          |       | BEGIN                            +
-          |       |   -- this is a SELECT            +
-          |       |   PERFORM 'hello world'::TEXT;   +
+ t        |     1 | DO LANGUAGE plpgsql $$              +
+          |       | BEGIN                               +
+          |       |   -- this is a SELECT               +
+          |       |   PERFORM 'hello world'::TEXT;      +
           |       | END; $$
  f        |     1 | SELECT $1::TEXT
  t        |     1 | SELECT pg_stat_statements_reset()
- t        |     1 | SET pg_stat_statements.track = $1
+ t        |     1 | SET pg_stat_statements.track = 'all'
 (7 rows)

 -- COPY
 
 DROP TABLE copy_stats;

 
diff --git a/contrib/pg_stat_statements/expected/wal.out b/contrib/pg_stat_statements/expected/wal.out
index 5c2c9c5ffa..1bfb84fc33 100644
--- a/contrib/pg_stat_statements/expected/wal.out
+++ b/contrib/pg_stat_statements/expected/wal.out
@@ -23,7 +23,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
  INSERT INTO pgss_wal_tab VALUES(generate_series($1, $2), $3)                 |     1 |   10 | t                   | t                     | t
  SELECT pg_stat_statements_reset(0,0,0)                                       |     1 |    1 | f                   | f                     | f
  SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |    1 | f                   | f                     | f
- 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_wal_tab SET b = $1 WHERE a > $2                                  |     1 |    3 | t                   | t                     | t
 (8 rows)
 
-- 
2.39.2

---

From: "Drouvot, Bertrand" <[email-masked]>
Date: Thu, 2 Mar 2023 08:12:24 +0100

Hi,

> - The main file is renamed to select.sql, as it now only covers SELECT

Thanks!

Splitting even more and removing pg_stat_statements.sql/out does make sense to me,
so +1 for the patch.

Applying 0001 produces:

Applying: Split more regression tests of pg_stat_statements
.git/rebase-apply/patch:1735: new blank line at EOF.
+
.git/rebase-apply/patch:2264: new blank line at EOF.
+
warning: 2 lines add whitespace errors.

Nits:

This comment is coming from the previous pg_stat_statements.sql but
I wonder if it makes sense here as testing utility is not the initial purpose
of wal.sql.

Same comment for dml.sql:

What about removing those comments?

> There is no change in the code coverage or the patterns tested.

I had a look (comparing the new .sql files with the old pg_stat_statements.sql content) and I agree.

Except from the Nits above, 0001 LGTM.

Regards,

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

---

From: Michael Paquier <[email-masked]>
Date: Fri, 3 Mar 2023 09:37:27 +0900

> warning: 2 lines add whitespace errors.

Indeed, removed.

> What about removing those comments?

Removing these two as well.

> Except from the Nits above, 0001 LGTM.

Thanks for double-checking, applied 0001 to finish this part of the
work.  I am attaching the remaining bits as of the attached, combined
into a single patch.  I am going to look at it again at the beginning
of next week and potentially apply it so as the normalization reflects
to the reports of pg_stat_statements.
--
Michael
From f96f6a65d5318b059527230d074660ffec099129 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Fri, 3 Mar 2023 09:28:13 +0900
Subject: [PATCH v6] Remove normalization of A_Const nodes

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/include/nodes/parsenodes.h                |  8 +++-
 src/include/nodes/primnodes.h                 |  9 +++--
 .../pg_stat_statements/expected/cursors.out   | 14 +++----
 .../pg_stat_statements/expected/utility.out   | 38 +++++++++----------
 .../pg_stat_statements/pg_stat_statements.c   |  4 +-
 5 files changed, 41 insertions(+), 32 deletions(-)

 
 
index dbb8f661c0..826ada4eaa 100644

@@ -326,12 +326,12 @@ CREATE TABLE ctas_stats_2 AS
@@ -354,11 +354,11 @@ CREATE MATERIALIZED VIEW matview_stats_1 AS
@@ -508,19 +508,19 @@ FETCH FORWARD ALL pgss_cursor;
 
 COMMIT;

 	/*
-- 
2.39.2

---

From: Andrei Zubkov <[email-masked]>
Date: Mon, 06 Mar 2023 15:50:55 +0300

Hi Michael!

I'm rebasing a patch "Tracking statements entry timestamp in
pg_stat_statements" for applying after this patch. I've noted that
current tests are not quite independent one from another. There is two
statements in the end of user_activity.sql test:

DROP ROLE regress_stats_user1;
DROP ROLE regress_stats_user2;

Those are done after the last pg_stat_statements_reset call in this
test file and thus, those are included in checks of wal.out file:

                                    query                             
| calls | rows | wal_bytes_generated | wal_records_generated |
wal_records_ge_rows 
-----------------------------------------------------------------------
-------+-------+------+---------------------+-----------------------+--
-------------------
 DELETE FROM pgss_wal_tab WHERE a > $1                                
|     1 |    1 | t                   | t                     | t
 DROP ROLE regress_stats_user1                                        
|     1 |    0 | t                   | t                     | t
 DROP ROLE regress_stats_user2                                        
|     1 |    0 | t                   | t                     | t

Those statements is not related to any WAL tests. It seems a little bit
incorrect to me.

Are we need some changes here?
-- 
Andrei Zubkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

---

From: Michael Paquier <[email-masked]>
Date: Tue, 7 Mar 2023 09:01:03 +0900

> Those statements is not related to any WAL tests. It seems a little bit

The intention is to have each file run in isolation, so this is
incorrect as it stands.  Thanks for the report!
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Wed, 8 Mar 2023 15:19:33 +0900

On Fri, Mar 03, 2023 at 09:37:27AM +0900, Michael Paquier wrote:
> into a single patch.

Doing so as a single patch was not feeling right as this actually
fixes issues with the location calculations for the Const node, so I
have split that into three commits and finally applied the whole.

As a bonus, please see attached a patch to apply the normalization to
CALL statements using the new automated infrastructure.  OUT
parameters can be passed to a procedure, hence I guess that these had
better be silenced as well.  This is not aimed at being integrated,
just for reference.
--
Michael
From 7fff41b050b8da4b7a006071b51ae5fa43bc7c0b Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 8 Mar 2023 15:14:15 +0900
Subject: [PATCH] Apply normalization to CALL statements

---
 src/include/nodes/parsenodes.h                |  6 ++--
 src/backend/nodes/queryjumblefuncs.c          | 12 +++++++
 .../pg_stat_statements/expected/utility.out   | 33 ++++++++++++++++---
 contrib/pg_stat_statements/sql/utility.sql    |  9 +++++
 4 files changed, 53 insertions(+), 7 deletions(-)

diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 259e814253..32e5f535c1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3227,12 +3227,14 @@ typedef struct InlineCodeBlock
  */
 typedef struct CallStmt
 {
+	pg_node_attr(custom_query_jumble)
+
 	NodeTag		type;
 	FuncCall   *funccall;		/* from the parser */
 	/* transformed call, with only input args */
-	FuncExpr   *funcexpr pg_node_attr(query_jumble_ignore);
+	FuncExpr   *funcexpr;
 	/* transformed output-argument expressions */
-	List	   *outargs pg_node_attr(query_jumble_ignore);
+	List	   *outargs;
 } CallStmt;
 
 typedef struct CallContext
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index d7fd72d70f..709f91ab0e 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -52,6 +52,7 @@ static void _jumbleNode(JumbleState *jstate, Node *node);
 static void _jumbleA_Const(JumbleState *jstate, Node *node);
 static void _jumbleList(JumbleState *jstate, Node *node);
 static void _jumbleRangeTblEntry(JumbleState *jstate, Node *node);
+static void _jumbleCallStmt(JumbleState *jstate, Node *node);
 
 /*
  * Given a possibly multi-statement source string, confine our attention to the
@@ -395,3 +396,14 @@ _jumbleRangeTblEntry(JumbleState *jstate, Node *node)
 			break;
 	}
 }
+
+static void
+_jumbleCallStmt(JumbleState *jstate, Node *node)
+{
+	CallStmt *expr = (CallStmt *) node;
+	FuncExpr *func = expr->funcexpr;
+
+	JUMBLE_FIELD_SINGLE(func->funcid);
+	_jumbleNode(jstate, (Node *) func->args);
+	_jumbleNode(jstate, (Node *) expr->outargs);
+}
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 0047aba5d1..b5a8c6937c 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -240,6 +240,12 @@ DECLARE
 BEGIN
   SELECT (i + i)::int INTO r;
 END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_out(IN i int, IN j int, OUT k int, OUT l 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;
@@ -256,15 +262,32 @@ CALL sum_one(3);
 CALL sum_one(199);
 CALL sum_two(1,1);
 CALL sum_two(1,2);
+CALL sum_out(1,1,1,1);
+ k | l 
+---+---
+   |  
+(1 row)
+
+CALL sum_out(2,2,1,1);
+ k | l 
+---+---
+   |  
+(1 row)
+
+CALL sum_out(2,3,4,5);
+ k | l 
+---+---
+   |  
+(1 row)
+
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
  calls | rows |               query               
 -------+------+-----------------------------------
-     1 |    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)
+     2 |    0 | CALL sum_one($1)
+     3 |    0 | CALL sum_out($1,$2,$3,$4)
+     2 |    0 | CALL sum_two($1,$2)
      1 |    1 | SELECT pg_stat_statements_reset()
-(5 rows)
+(4 rows)
 
 -- COPY
 CREATE TABLE copy_stats (a int, b int);
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
index 225d30a62a..dbf38c31bc 100644
--- a/contrib/pg_stat_statements/sql/utility.sql
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -131,6 +131,12 @@ DECLARE
 BEGIN
   SELECT (i + i)::int INTO r;
 END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_out(IN i int, IN j int, OUT k int, OUT l 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;
@@ -142,6 +148,9 @@ CALL sum_one(3);
 CALL sum_one(199);
 CALL sum_two(1,1);
 CALL sum_two(1,2);
+CALL sum_out(1,1,1,1);
+CALL sum_out(2,2,1,1);
+CALL sum_out(2,3,4,5);
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 -- COPY
-- 
2.39.2

---

From: jian he <[email-masked]>
Date: Wed, 16 Aug 2023 17:11:47 +0800

> better be silenced as well.  This is not aimed at being integrated,

I tested it. all normally works fine. only 1 corner case:
set pg_stat_statements.track =  'all';
drop table if Exists cp_test;
CREATE TABLE cp_test (a int, b text);
CREATE or REPLACE PROCEDURE ptest1(x text) LANGUAGE SQL AS $$ INSERT
INTO cp_test VALUES (1, x); $$;

CREATE or REPLACE PROCEDURE ptest3(y text)
LANGUAGE SQL
AS $$
CALL ptest1(y);
CALL ptest1($1);
$$;
SELECT pg_stat_statements_reset();

CALL ptest3('b');

SELECT calls, toplevel, rows, query FROM pg_stat_statements ORDER BY
query COLLATE "C";
returns:
 calls | toplevel | rows |               query
-------+----------+------+------------------------------------
     1 | t        |    0 | CALL ptest3($1)
     2 | f        |    2 | INSERT INTO cp_test VALUES ($2, x)
     1 | t        |    1 | SELECT pg_stat_statements_reset()

here, the intermediate CALL part is optimized away. or should I expect
CALL ptest1($1) also in pg_stat_statements?

---

From: Michael Paquier <[email-masked]>
Date: Fri, 18 Aug 2023 08:31:37 +0900

>  calls | toplevel | rows |               query

I would have guessed that ptest1() being called as part of ptest3()
should show up in the report if you use track = all, as all the nested
queries of a function, even if it is pure SQL, ought to show up.  Now
note that ptest1() not showing up is not a new behavior, ~15 does the
same thing by missing it.
--
Michael