On Mon, Feb 20, 2023 at 11:34:59AM +0900, Michael Paquier wrote: > 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 <mich...@paquier.xyz>
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 <mich...@paquier.xyz>
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(-)
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 b4292253cc..4220c63ab7 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 b1214ee645..0b7fffadca 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -490,7 +490,9 @@
<command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>)
and utility commands 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.
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/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)
-- 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/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)
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 a3a082ac37bb7c116fc60416d608d6da16192a6a Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 1 Mar 2023 13:40:34 +0900
Subject: [PATCH v5 3/3] 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 ++-
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(-)
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/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)
-- 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)
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
signature.asc
Description: PGP signature
