Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1080 insertions, 0 deletions

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
new file mode 100644
index 0000000..b52d187
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -0,0 +1,1080 @@
+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;
+-- compound with empty statements and spurious leading spacing
+\;\;   SELECT 3 + 3 \;\;\;   SELECT ' ' || ' !' \;\;   SELECT 1 + 4 \;;
+ ?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
+-- 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
+                                                                             +|       | 
+   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)
+
+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
+ 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
+(10 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
+SET pg_stat_statements.track_utility = TRUE;
+DROP TABLE pgss_test;
+SET pg_stat_statements.track_utility = FALSE;
+-- 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_as_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+                           query                           | calls | rows | wal_bytes_generated | wal_records_generated | wal_records_as_rows 
+-----------------------------------------------------------+-------+------+---------------------+-----------------------+---------------------
+ DELETE FROM pgss_test WHERE a > $1                        |     1 |    1 | t                   | t                     | t
+ DROP TABLE pgss_test                                      |     1 |    0 | t                   | t                     | f
+ 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_as_rows                +|       |      |                     |                       | 
+ FROM pg_stat_statements ORDER BY query COLLATE "C"        |       |      |                     |                       | 
+ SET pg_stat_statements.track_utility = FALSE              |     1 |    0 | f                   | f                     | t
+ UPDATE pgss_test SET b = $1 WHERE a > $2                  |     1 |    3 | t                   | t                     | t
+(7 rows)
+
+--
+-- pg_stat_statements.track = none
+--
+SET pg_stat_statements.track = 'none';
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT 1 AS "one";
+ one 
+-----
+   1
+(1 row)
+
+SELECT 1 + 1 AS "two";
+ two 
+-----
+   2
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows 
+-------+-------+------
+(0 rows)
+
+--
+-- pg_stat_statements.track = top
+--
+SET pg_stat_statements.track = 'top';
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+DO LANGUAGE plpgsql $$
+BEGIN
+  -- this is a SELECT
+  PERFORM 'hello world'::TEXT;
+END;
+$$;
+-- PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+  r INTEGER;
+BEGIN
+  SELECT (i + 1 + 1.0)::INTEGER INTO r;
+  RETURN r;
+END; $$ LANGUAGE plpgsql;
+SELECT PLUS_TWO(3);
+ plus_two 
+----------
+        5
+(1 row)
+
+SELECT PLUS_TWO(7);
+ plus_two 
+----------
+        9
+(1 row)
+
+-- SQL function --- use LIMIT to keep it from being inlined
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
+SELECT PLUS_ONE(8);
+ plus_one 
+----------
+        9
+(1 row)
+
+SELECT PLUS_ONE(10);
+ plus_one 
+----------
+       11
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                    query                                     | calls | rows 
+------------------------------------------------------------------------------+-------+------
+ SELECT $1::TEXT                                                              |     1 |    1
+ SELECT PLUS_ONE($1)                                                          |     2 |    2
+ SELECT PLUS_TWO($1)                                                          |     2 |    2
+ SELECT pg_stat_statements_reset()                                            |     1 |    1
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
+(5 rows)
+
+--
+-- pg_stat_statements.track = all
+--
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- we drop and recreate the functions to avoid any caching funnies
+DROP FUNCTION PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
+-- PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+  r INTEGER;
+BEGIN
+  SELECT (i + 1 + 1.0)::INTEGER INTO r;
+  RETURN r;
+END; $$ LANGUAGE plpgsql;
+SELECT PLUS_TWO(-1);
+ plus_two 
+----------
+        1
+(1 row)
+
+SELECT PLUS_TWO(2);
+ plus_two 
+----------
+        4
+(1 row)
+
+-- SQL function --- use LIMIT to keep it from being inlined
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
+SELECT PLUS_ONE(3);
+ plus_one 
+----------
+        4
+(1 row)
+
+SELECT PLUS_ONE(1);
+ plus_one 
+----------
+        2
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                    query                                     | calls | rows 
+------------------------------------------------------------------------------+-------+------
+ SELECT (i + $2 + $3)::INTEGER                                                |     2 |    2
+ SELECT (i + $2)::INTEGER LIMIT $3                                            |     2 |    2
+ SELECT PLUS_ONE($1)                                                          |     2 |    2
+ SELECT PLUS_TWO($1)                                                          |     2 |    2
+ SELECT pg_stat_statements_reset()                                            |     1 |    1
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
+(6 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;
+--
+-- utility commands
+--
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+CREATE INDEX test_b ON test(b);
+DROP TABLE test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION PLUS_ONE(INTEGER);
+NOTICE:  table "test" does not exist, skipping
+DROP TABLE IF EXISTS test \;
+DROP TABLE IF EXISTS test \;
+DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
+NOTICE:  table "test" does not exist, skipping
+NOTICE:  table "test" does not exist, skipping
+NOTICE:  function plus_one(pg_catalog.int4) does not exist, skipping
+DROP FUNCTION PLUS_TWO(INTEGER);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                    query                                     | calls | rows 
+------------------------------------------------------------------------------+-------+------
+ CREATE INDEX test_b ON test(b)                                               |     1 |    0
+ DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER)                                    |     1 |    0
+ DROP FUNCTION PLUS_ONE(INTEGER)                                              |     1 |    0
+ DROP FUNCTION PLUS_TWO(INTEGER)                                              |     1 |    0
+ DROP TABLE IF EXISTS test                                                    |     3 |    0
+ DROP TABLE test                                                              |     1 |    0
+ SELECT $1                                                                    |     1 |    1
+ SELECT pg_stat_statements_reset()                                            |     1 |    1
+ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     0 |    0
+(9 rows)
+
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
+SELECT generate_series(1, 10) c INTO pgss_select_into;
+COPY pgss_ctas (a, b) FROM STDIN;
+CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
+BEGIN;
+DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
+FETCH NEXT pgss_cursor;
+ a |  b   
+---+------
+ 1 | ctas
+(1 row)
+
+FETCH FORWARD 5 pgss_cursor;
+ a |  b   
+---+------
+ 2 | ctas
+ 3 | ctas
+ 4 | ctas
+ 5 | ctas
+ 6 | ctas
+(5 rows)
+
+FETCH FORWARD ALL pgss_cursor;
+ a  |  b   
+----+------
+  7 | ctas
+  8 | ctas
+  9 | ctas
+ 10 | ctas
+ 11 | copy
+ 12 | copy
+ 13 | copy
+(7 rows)
+
+COMMIT;
+SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                        query                                        | plans | calls | rows 
+-------------------------------------------------------------------------------------+-------+-------+------
+ BEGIN                                                                               |     0 |     1 |    0
+ COMMIT                                                                              |     0 |     1 |    0
+ COPY pgss_ctas (a, b) FROM STDIN                                                    |     0 |     1 |    3
+ CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas                       |     0 |     1 |   13
+ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a          |     0 |     1 |   10
+ DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv                              |     0 |     1 |    0
+ FETCH FORWARD 5 pgss_cursor                                                         |     0 |     1 |    5
+ FETCH FORWARD ALL pgss_cursor                                                       |     0 |     1 |    7
+ FETCH NEXT pgss_cursor                                                              |     0 |     1 |    1
+ REFRESH MATERIALIZED VIEW pgss_matv                                                 |     0 |     1 |   13
+ SELECT generate_series(1, 10) c INTO pgss_select_into                               |     0 |     1 |   10
+ SELECT pg_stat_statements_reset()                                                   |     0 |     1 |    1
+ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |     0 |    0
+(13 rows)
+
+--
+-- Track user activity and reset them
+--
+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;
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
+--
+-- [re]plan counting
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+CREATE TABLE test ();
+PREPARE prep1 AS SELECT COUNT(*) FROM test;
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+ALTER TABLE test ADD COLUMN x int;
+EXECUTE prep1;
+ count 
+-------
+     0
+(1 row)
+
+SELECT 42;
+ ?column? 
+----------
+       42
+(1 row)
+
+SELECT 42;
+ ?column? 
+----------
+       42
+(1 row)
+
+SELECT 42;
+ ?column? 
+----------
+       42
+(1 row)
+
+SELECT query, plans, calls, rows FROM pg_stat_statements
+  WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+                          query                           | plans | calls | rows 
+----------------------------------------------------------+-------+-------+------
+ ALTER TABLE test ADD COLUMN x int                        |     0 |     1 |    0
+ CREATE TABLE test ()                                     |     0 |     1 |    0
+ SELECT $1                                                |     3 |     3 |    3
+ SELECT pg_stat_statements_reset()                        |     0 |     1 |    1
+ SELECT query, plans, calls, rows FROM pg_stat_statements+|     1 |     0 |    0
+   WHERE query NOT LIKE $1 ORDER BY query COLLATE "C"     |       |       | 
+(5 rows)
+
+-- for the prepared statement we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
+  WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+                   query                    | plans_ok | calls | rows 
+--------------------------------------------+----------+-------+------
+ PREPARE prep1 AS SELECT COUNT(*) FROM test | t        |     4 |    4
+(1 row)
+
+--
+-- access to pg_stat_statements_info view
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT dealloc FROM pg_stat_statements_info;
+ dealloc 
+---------
+       0
+(1 row)
+
+--
+-- top level handling
+--
+SET pg_stat_statements.track = 'top';
+DELETE FROM test;
+DO $$
+BEGIN
+    DELETE FROM test;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+         query         | toplevel | plans | calls 
+-----------------------+----------+-------+-------
+ DELETE FROM test      | t        |     1 |     1
+ DO $$                +| t        |     0 |     1
+ BEGIN                +|          |       | 
+     DELETE FROM test;+|          |       | 
+ END;                 +|          |       | 
+ $$ LANGUAGE plpgsql   |          |       | 
+(2 rows)
+
+SET pg_stat_statements.track = 'all';
+DELETE FROM test;
+DO $$
+BEGIN
+    DELETE FROM test;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+         query         | toplevel | plans | calls 
+-----------------------+----------+-------+-------
+ DELETE FROM test      | f        |     1 |     1
+ DELETE FROM test      | t        |     2 |     2
+ DO $$                +| t        |     0 |     2
+ BEGIN                +|          |       | 
+     DELETE FROM test;+|          |       | 
+ END;                 +|          |       | 
+ $$ LANGUAGE plpgsql   |          |       | 
+(3 rows)
+
+-- FROM [ONLY]
+CREATE TABLE tbl_inh(id integer);
+CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
+INSERT INTO tbl_inh_1 SELECT 1;
+SELECT * FROM tbl_inh;
+ id 
+----
+  1
+(1 row)
+
+SELECT * FROM ONLY tbl_inh;
+ id 
+----
+(0 rows)
+
+SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
+ count 
+-------
+     2
+(1 row)
+
+-- WITH TIES
+CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10);
+SELECT *
+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)
+
+DROP EXTENSION pg_stat_statements;