Adding upstream version 16.2.upstream/16.2

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

10 files changed, 809 insertions, 0 deletions

diff --git a/contrib/pg_stat_statements/sql/cleanup.sql b/contrib/pg_stat_statements/sql/cleanup.sql
new file mode 100644
index 0000000..36bec35
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/cleanup.sql
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql
new file mode 100644
index 0000000..cef6dc9
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/cursors.sql
@@ -0,0 +1,30 @@
+--
+-- Cursors
+--
+
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+-- DECLARE
+-- SELECT is normalized.
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1;
+CLOSE cursor_stats_1;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+CLOSE cursor_stats_1;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- FETCH
+BEGIN;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3;
+FETCH 1 IN cursor_stats_1;
+FETCH 1 IN cursor_stats_2;
+CLOSE cursor_stats_1;
+CLOSE cursor_stats_2;
+COMMIT;
+
+SELECT 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/dml.sql b/contrib/pg_stat_statements/sql/dml.sql
new file mode 100644
index 0000000..3b5d2af
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/dml.sql
@@ -0,0 +1,95 @@
+--
+-- DMLs on test table
+--
+
+SET pg_stat_statements.track_utility = FALSE;
+
+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";
+
+-- check that [temp] table relation extensions are tracked as writes
+CREATE TABLE pgss_extend_tab (a int, b text);
+CREATE TEMP TABLE pgss_extend_temp_tab (a int, b text);
+SELECT pg_stat_statements_reset();
+INSERT INTO pgss_extend_tab (a, b) SELECT generate_series(1, 1000), 'something';
+INSERT INTO pgss_extend_temp_tab (a, b) SELECT generate_series(1, 1000), 'something';
+WITH sizes AS (
+  SELECT
+    pg_relation_size('pgss_extend_tab') / current_setting('block_size')::int8 AS rel_size,
+    pg_relation_size('pgss_extend_temp_tab') / current_setting('block_size')::int8 AS temp_rel_size
+)
+SELECT
+    SUM(local_blks_written) >= (SELECT temp_rel_size FROM sizes) AS temp_written_ok,
+    SUM(local_blks_dirtied) >= (SELECT temp_rel_size FROM sizes) AS temp_dirtied_ok,
+    SUM(shared_blks_written) >= (SELECT rel_size FROM sizes) AS written_ok,
+    SUM(shared_blks_dirtied) >= (SELECT rel_size FROM sizes) AS dirtied_ok
+FROM pg_stat_statements;
+
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql
new file mode 100644
index 0000000..0c20b8c
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/level_tracking.sql
@@ -0,0 +1,100 @@
+--
+-- Statement level tracking
+--
+
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+-- DO block - top-level tracking.
+CREATE TABLE stats_track_tab (x int);
+SET pg_stat_statements.track = 'top';
+DELETE FROM stats_track_tab;
+DO $$
+BEGIN
+  DELETE FROM stats_track_tab;
+END;
+$$ LANGUAGE plpgsql;
+SELECT toplevel, calls, query FROM pg_stat_statements
+  WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+SELECT pg_stat_statements_reset();
+
+-- DO block - all-level tracking.
+SET pg_stat_statements.track = 'all';
+DELETE FROM stats_track_tab;
+DO $$
+BEGIN
+  DELETE FROM stats_track_tab;
+END; $$;
+DO LANGUAGE plpgsql $$
+BEGIN
+  -- this is a SELECT
+  PERFORM 'hello world'::TEXT;
+END; $$;
+SELECT toplevel, calls, query FROM pg_stat_statements
+  ORDER BY query COLLATE "C", toplevel;
+
+-- PL/pgSQL function - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset();
+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);
+SELECT PLUS_TWO(7);
+
+-- 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);
+SELECT PLUS_ONE(10);
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- PL/pgSQL function - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset();
+
+-- 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);
+SELECT PLUS_TWO(2);
+
+-- 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);
+SELECT PLUS_ONE(1);
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DROP FUNCTION PLUS_ONE(INTEGER);
+
+--
+-- pg_stat_statements.track = none
+--
+SET pg_stat_statements.track = 'none';
+SELECT pg_stat_statements_reset();
+
+SELECT 1 AS "one";
+SELECT 1 + 1 AS "two";
+
+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/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql
new file mode 100644
index 0000000..e2a8310
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/oldextversions.sql
@@ -0,0 +1,51 @@
+-- test old extension version entry points
+
+CREATE EXTENSION pg_stat_statements WITH VERSION '1.4';
+-- Execution of pg_stat_statements_reset() is granted only to
+-- superusers in 1.4, so this fails.
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.5';
+-- Execution of pg_stat_statements_reset() should be granted to
+-- pg_read_all_stats now, so this works.
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+
+-- In 1.6, it got restricted back to superusers.
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.6';
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+
+-- New function for pg_stat_statements_reset introduced, still
+-- restricted for non-superusers.
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.7';
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+\d pg_stat_statements
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+
+-- New functions and views for pg_stat_statements in 1.8
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
+\d pg_stat_statements
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+
+-- New function pg_stat_statement_info, and new function
+-- and view for pg_stat_statements introduced in 1.9
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
+\d pg_stat_statements
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+
+-- New functions and views for pg_stat_statements in 1.10
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
+\d pg_stat_statements
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql
new file mode 100644
index 0000000..a59b936
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/planning.sql
@@ -0,0 +1,31 @@
+--
+-- Information related to planning
+--
+
+-- These tests require track_planning to be enabled.
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+
+--
+-- [re]plan counting
+--
+CREATE TABLE stats_plan_test ();
+PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test;
+EXECUTE prep1;
+EXECUTE prep1;
+EXECUTE prep1;
+ALTER TABLE stats_plan_test ADD COLUMN x int;
+EXECUTE prep1;
+SELECT 42;
+SELECT 42;
+SELECT 42;
+SELECT plans, calls, rows, query FROM pg_stat_statements
+  WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+-- for the prepared statement we expect at least one replan, but cache
+-- invalidations could force more
+SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements
+  WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+
+-- Cleanup
+DROP TABLE stats_plan_test;
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
new file mode 100644
index 0000000..eef7b0b
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -0,0 +1,149 @@
+--
+-- SELECT statements
+--
+
+CREATE EXTENSION pg_stat_statements;
+SET pg_stat_statements.track_utility = FALSE;
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+
+--
+-- simple and compound statements
+--
+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 0000000..4b95edd
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/user_activity.sql
@@ -0,0 +1,66 @@
+--
+-- 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;
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
new file mode 100644
index 0000000..aec97d3
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -0,0 +1,266 @@
+--
+-- Utility commands
+--
+
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+-- Tables, indexes, triggers
+CREATE TEMP TABLE tab_stats (a int, b char(20));
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0;
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a';
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b;
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0);
+DROP TABLE tab_stats \;
+DROP TABLE IF EXISTS tab_stats \;
+-- This DROP query uses two different strings, still they count as one entry.
+DROP TABLE IF EXISTS tab_stats \;
+Drop Table If Exists tab_stats \;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Partitions
+CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a);
+CREATE TABLE pt_stats1 (a int, b int);
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100);
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200);
+CREATE INDEX pt_stats_index ON ONLY pt_stats (a);
+CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a);
+ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index;
+DROP TABLE pt_stats;
+
+-- Views
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b;
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2;
+DROP VIEW view_stats;
+
+-- Foreign tables
+CREATE FOREIGN DATA WRAPPER wrapper_stats;
+CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats;
+CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats;
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1;
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0);
+DROP FOREIGN TABLE foreign_stats;
+DROP SERVER server_stats;
+DROP FOREIGN DATA WRAPPER wrapper_stats;
+
+-- Functions
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+  RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
+DROP FUNCTION func_stats;
+
+-- Rules
+CREATE TABLE tab_rule_stats (a int, b int);
+CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int);
+CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
+  INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2);
+DROP RULE rules_stats ON tab_rule_stats;
+DROP TABLE tab_rule_stats, tab_rule_stats_2;
+
+-- Types
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2));
+DROP TYPE stats_type;
+
+-- Triggers
+CREATE TABLE trigger_tab_stats (a int, b int);
+CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
+  AS $$ BEGIN return OLD; end; $$;
+CREATE TRIGGER trigger_tab_stats
+    AFTER UPDATE ON trigger_tab_stats
+    FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+    EXECUTE FUNCTION trigger_func_stats();
+DROP TABLE trigger_tab_stats;
+
+-- Policies
+CREATE TABLE tab_policy_stats (a int, b int);
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5);
+DROP TABLE tab_policy_stats;
+
+-- Statistics
+CREATE TABLE tab_expr_stats (a int, b int);
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
+DROP TABLE tab_expr_stats;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Transaction statements
+BEGIN;
+ABORT;
+BEGIN;
+ROLLBACK;
+-- WORK
+BEGIN WORK;
+COMMIT WORK;
+BEGIN WORK;
+ABORT WORK;
+-- TRANSACTION
+BEGIN TRANSACTION;
+COMMIT TRANSACTION;
+BEGIN TRANSACTION;
+ABORT TRANSACTION;
+-- More isolation levels
+BEGIN TRANSACTION DEFERRABLE;
+COMMIT TRANSACTION AND NO CHAIN;
+BEGIN ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+-- List of A_Const nodes, same lists.
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
+COMMIT;
+BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE;
+COMMIT;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- EXPLAIN statements
+-- A Query is used, normalized by the query jumbling.
+EXPLAIN (costs off) SELECT 1;
+EXPLAIN (costs off) SELECT 2;
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3;
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CALL
+CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + j)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+SELECT pg_stat_statements_reset();
+CALL sum_one(3);
+CALL sum_one(199);
+CALL sum_two(1,1);
+CALL sum_two(1,2);
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- COPY
+CREATE TABLE copy_stats (a int, b int);
+SELECT pg_stat_statements_reset();
+-- Some queries with A_Const nodes.
+COPY (SELECT 1) TO STDOUT;
+COPY (SELECT 2) TO STDOUT;
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT;
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT;
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT;
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DROP TABLE copy_stats;
+SELECT pg_stat_statements_reset();
+
+-- CREATE TABLE AS
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE TABLE ctas_stats_1 AS SELECT 1 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_1 AS SELECT 2 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP TABLE ctas_stats_2;
+CREATE TABLE ctas_stats_2 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
+DROP TABLE ctas_stats_2;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- CREATE MATERIALIZED VIEW
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP MATERIALIZED VIEW matview_stats_1;
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP MATERIALIZED VIEW matview_stats_1;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- CREATE VIEW
+CREATE VIEW view_stats_1 AS
+  SELECT a AS col1, 2::int AS col2
+    FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP VIEW view_stats_1;
+CREATE VIEW view_stats_1 AS
+  SELECT a AS col1, 4::int AS col2
+    FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP VIEW view_stats_1;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Domains
+CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0);
+ALTER DOMAIN domain_stats SET DEFAULT '3';
+ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
+DROP DOMAIN domain_stats;
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- SET statements.
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+-- SET TRANSACTION ISOLATION
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+-- SET SESSION CHARACTERISTICS
+SET SESSION SESSION AUTHORIZATION DEFAULT;
+RESET SESSION AUTHORIZATION;
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION DEFAULT;
+RESET SESSION AUTHORIZATION;
+COMMIT;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
+--
+CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
+SELECT generate_series(1, 10) c INTO pgss_select_into;
+COPY pgss_ctas (a, b) FROM STDIN;
+11	copy
+12	copy
+13	copy
+\.
+CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
+BEGIN;
+DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
+FETCH NEXT pgss_cursor;
+FETCH FORWARD 5 pgss_cursor;
+FETCH FORWARD ALL pgss_cursor;
+COMMIT;
+
+SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
+
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/wal.sql b/contrib/pg_stat_statements/sql/wal.sql
new file mode 100644
index 0000000..34b21c0
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/wal.sql
@@ -0,0 +1,20 @@
+--
+-- Validate WAL generation metrics
+--
+
+SET pg_stat_statements.track_utility = FALSE;
+
+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 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();