diff -U3 /home/postgres/postgres/contrib/pg_stat_statements/expected/utility.out /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/utility.out --- /home/postgres/postgres/contrib/pg_stat_statements/expected/utility.out 2025-12-07 14:51:09.560502331 +0000 +++ /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/utility.out 2025-12-07 14:54:25.333417915 +0000 @@ -44,696 +44,7 @@ -- 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 - SET work_mem = '256kB'; -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"; - 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 (0) TO (100) - 1 | 0 | ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2 - 1 | 0 | CREATE FOREIGN DATA WRAPPER wrapper_stats - 1 | 0 | CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats - 1 | 0 | CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))+ - | | RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL + - | | SET work_mem = $1 - 1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql + - | | AS $$ BEGIN return OLD; end; $$ - 1 | 0 | CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a) - 1 | 0 | CREATE INDEX pt_stats_index ON ONLY pt_stats (a) - 1 | 0 | CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5) - 1 | 0 | CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD + - | | INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2) - 1 | 0 | CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats - 1 | 0 | CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats - 1 | 0 | CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a) - 1 | 0 | CREATE TABLE pt_stats1 (a int, b int) - 1 | 0 | CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200) - 1 | 0 | CREATE TABLE tab_expr_stats (a int, b int) - 1 | 0 | CREATE TABLE tab_policy_stats (a int, b int) - 1 | 0 | CREATE TABLE tab_rule_stats (a int, b int) - 1 | 0 | CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int) - 1 | 0 | CREATE TABLE trigger_tab_stats (a int, b int) - 1 | 0 | CREATE TRIGGER trigger_tab_stats + - | | AFTER UPDATE ON trigger_tab_stats + - | | FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) + - | | EXECUTE FUNCTION trigger_func_stats() - 1 | 0 | CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2)) - 1 | 0 | CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b - 1 | 0 | DROP FOREIGN DATA WRAPPER wrapper_stats - 1 | 0 | DROP FOREIGN TABLE foreign_stats - 1 | 0 | DROP FUNCTION func_stats - 1 | 0 | DROP RULE rules_stats ON tab_rule_stats - 1 | 0 | DROP SERVER server_stats - 1 | 0 | DROP TABLE pt_stats - 1 | 0 | DROP TABLE tab_expr_stats - 1 | 0 | DROP TABLE tab_policy_stats - 1 | 0 | DROP TABLE tab_rule_stats, tab_rule_stats_2 - 1 | 0 | DROP TABLE trigger_tab_stats - 1 | 0 | DROP TYPE stats_type - 1 | 0 | DROP VIEW view_stats - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(39 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- 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"; - 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 - 7 | 0 | COMMIT WORK - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(8 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Two-phase transactions -BEGIN; -PREPARE TRANSACTION 'stat_trans1'; -COMMIT PREPARED 'stat_trans1'; -BEGIN; -PREPARE TRANSACTION 'stat_trans2'; -ROLLBACK PREPARED 'stat_trans2'; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 2 | 0 | BEGIN - 1 | 0 | COMMIT PREPARED $1 - 2 | 0 | PREPARE TRANSACTION $1 - 1 | 0 | ROLLBACK PREPARED $1 - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Savepoints -BEGIN; -SAVEPOINT sp1; -SAVEPOINT sp2; -SAVEPOINT sp3; -SAVEPOINT sp4; -ROLLBACK TO sp4; -ROLLBACK TO SAVEPOINT sp4; -ROLLBACK TRANSACTION TO SAVEPOINT sp3; -RELEASE sp3; -RELEASE SAVEPOINT sp2; -ROLLBACK TO sp1; -RELEASE SAVEPOINT sp1; -COMMIT; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 1 | 0 | BEGIN - 1 | 0 | COMMIT - 3 | 0 | RELEASE $1 - 4 | 0 | ROLLBACK TO $1 - 4 | 0 | SAVEPOINT $1 - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(6 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- EXPLAIN statements --- A Query is used, normalized by the query jumbling. -EXPLAIN (costs off) SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (costs off) SELECT 2; - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3; - QUERY PLAN --------------------------------------- - Function Scan on generate_series tab - Filter: (a = 3) -(2 rows) - -EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7; - QUERY PLAN --------------------------------------- - Function Scan on generate_series tab - Filter: (a = 7) -(2 rows) - -SELECT 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,$2) AS tab(a) WHERE a = $3 - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(3 rows) - --- CALL -CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$ -DECLARE - r int; -BEGIN - SELECT (i + i)::int INTO r; -END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$ -DECLARE - r int; -BEGIN - SELECT (i + j)::int INTO r; -END; $$ LANGUAGE plpgsql; --- Overloaded functions. -CREATE OR REPLACE PROCEDURE overload(i int) AS $$ -DECLARE - r int; -BEGIN - SELECT (i + i)::int INTO r; -END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE PROCEDURE overload(i text) AS $$ -DECLARE - r text; -BEGIN - SELECT i::text INTO r; -END; $$ LANGUAGE plpgsql; --- Mix of IN/OUT parameters. -CREATE OR REPLACE PROCEDURE in_out(i int, i2 OUT int, i3 INOUT int) AS $$ -DECLARE - r int; -BEGIN - i2 := i; - i3 := i3 + i; -END; $$ LANGUAGE plpgsql; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CALL sum_one(3); -CALL sum_one(199); -CALL sum_two(1,1); -CALL sum_two(1,2); -CALL overload(1); -CALL overload('A'); -CALL in_out(1, NULL, 1); - i2 | i3 -----+---- - 1 | 2 -(1 row) - -CALL in_out(2, 1, 2); - i2 | i3 -----+---- - 2 | 4 -(1 row) - -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 2 | 0 | CALL in_out($1, $2, $3) - 1 | 0 | CALL overload($1) - 1 | 0 | CALL overload($1) - 2 | 0 | CALL sum_one($1) - 2 | 0 | CALL sum_two($1,$2) - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(6 rows) - --- COPY -CREATE TABLE copy_stats (a int, b int); -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Some queries with A_Const nodes. -COPY (SELECT 1) TO STDOUT; -1 -COPY (SELECT 2) TO STDOUT; -2 -COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT; -1 1 -COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT; -2 2 -COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT; -1 2 -2 3 -COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT; -1 4 -2 5 -COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT; -1 4 -SELECT 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 - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(8 rows) - -DROP TABLE copy_stats; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- CREATE TABLE AS --- SELECT queries are normalized, creating matching query IDs. -CREATE TABLE ctas_stats_1 AS SELECT 1 AS a; -DROP TABLE ctas_stats_1; -CREATE TABLE ctas_stats_1 AS SELECT 2 AS a; -DROP TABLE ctas_stats_1; -CREATE TABLE ctas_stats_2 AS - SELECT a AS col1, 2::int AS col2 - FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; -DROP TABLE ctas_stats_2; -CREATE TABLE ctas_stats_2 AS - SELECT a AS col1, 4::int AS col2 - FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1; -DROP TABLE ctas_stats_2; -SELECT 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, $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() IS NOT NULL AS t -(5 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- CREATE MATERIALIZED VIEW --- SELECT queries are normalized, creating matching query IDs. -CREATE MATERIALIZED VIEW matview_stats_1 AS - SELECT a AS col1, 2::int AS col2 - FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; -DROP MATERIALIZED VIEW matview_stats_1; -CREATE MATERIALIZED VIEW matview_stats_1 AS - SELECT a AS col1, 4::int AS col2 - FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3; -DROP MATERIALIZED VIEW matview_stats_1; -SELECT 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, $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() IS NOT NULL AS t -(3 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- CREATE VIEW -CREATE VIEW view_stats_1 AS - SELECT a AS col1, 2::int AS col2 - FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; -DROP VIEW view_stats_1; -CREATE VIEW view_stats_1 AS - SELECT a AS col1, 4::int AS col2 - FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3; -DROP VIEW view_stats_1; -SELECT 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 - 2 | 0 | DROP VIEW view_stats_1 - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(4 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Domains -CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0); -ALTER DOMAIN domain_stats SET DEFAULT '3'; -ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1); -DROP DOMAIN domain_stats; -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) - 1 | 0 | DROP DOMAIN domain_stats - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Execution statements -SELECT 1 as a; - a ---- - 1 -(1 row) - -PREPARE stat_select AS SELECT $1 AS a; -EXECUTE stat_select (1); - a ---- - 1 -(1 row) - -DEALLOCATE stat_select; -PREPARE stat_select AS SELECT $1 AS a; -EXECUTE stat_select (2); - a ---- - 2 -(1 row) - -DEALLOCATE PREPARE stat_select; -DEALLOCATE ALL; -DEALLOCATE PREPARE ALL; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 2 | 0 | DEALLOCATE $1 - 2 | 0 | DEALLOCATE ALL - 2 | 2 | PREPARE stat_select AS SELECT $1 AS a - 1 | 1 | SELECT $1 as a - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- SET statements. --- These use two different strings, still they count as one entry. -CREATE ROLE regress_stat_set_1; -CREATE ROLE regress_stat_set_2; -SET work_mem = '1MB'; -Set work_mem = '1MB'; -SET work_mem = '2MB'; -SET work_mem = DEFAULT; -SET work_mem TO DEFAULT; -SET work_mem FROM CURRENT; -BEGIN; -SET LOCAL work_mem = '128kB'; -SET LOCAL work_mem = '256kB'; -SET LOCAL work_mem = DEFAULT; -SET LOCAL work_mem TO DEFAULT; -SET LOCAL work_mem FROM CURRENT; -COMMIT; -RESET work_mem; -SET enable_seqscan = off; -SET enable_seqscan = on; -SET SESSION work_mem = '300kB'; -SET SESSION work_mem = '400kB'; -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 AUTHORIZATION -SET SESSION SESSION AUTHORIZATION DEFAULT; -SET SESSION AUTHORIZATION 'regress_stat_set_1'; -SET SESSION AUTHORIZATION 'regress_stat_set_2'; -RESET SESSION AUTHORIZATION; -BEGIN; -SET LOCAL SESSION AUTHORIZATION DEFAULT; -SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_1'; -SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_2'; -RESET SESSION AUTHORIZATION; -COMMIT; --- SET SESSION CHARACTERISTICS -SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; -SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ ONLY; -SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ WRITE; --- SET XML OPTION -SET XML OPTION DOCUMENT; -SET XML OPTION CONTENT; --- SET TIME ZONE -SET TIME ZONE 'America/New_York'; -SET TIME ZONE 'Asia/Tokyo'; -SET TIME ZONE DEFAULT; -SET TIME ZONE LOCAL; -SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0'; -RESET TIME ZONE; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+------------------------------------------------------------------ - 3 | 0 | BEGIN - 3 | 0 | COMMIT - 1 | 0 | CREATE ROLE regress_stat_set_1 - 1 | 0 | CREATE ROLE regress_stat_set_2 - 2 | 0 | RESET SESSION AUTHORIZATION - 1 | 0 | RESET TIME ZONE - 1 | 0 | RESET enable_seqscan - 1 | 0 | RESET work_mem - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - 1 | 0 | SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_1' - 1 | 0 | SET LOCAL SESSION AUTHORIZATION 'regress_stat_set_2' - 1 | 0 | SET LOCAL SESSION AUTHORIZATION DEFAULT - 2 | 0 | SET LOCAL work_mem = $1 - 2 | 0 | SET LOCAL work_mem = DEFAULT - 1 | 0 | SET LOCAL work_mem FROM CURRENT - 1 | 0 | SET SESSION AUTHORIZATION 'regress_stat_set_1' - 1 | 0 | SET SESSION AUTHORIZATION 'regress_stat_set_2' - 1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY - 1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ ONLY - 1 | 0 | SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, READ WRITE - 1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT - 1 | 0 | SET TIME ZONE 'America/New_York' - 1 | 0 | SET TIME ZONE 'Asia/Tokyo' - 1 | 0 | SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0' - 2 | 0 | SET TIME ZONE 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 XML OPTION CONTENT - 1 | 0 | SET XML OPTION DOCUMENT - 2 | 0 | SET enable_seqscan = $1 - 5 | 0 | SET work_mem = $1 - 2 | 0 | SET work_mem = DEFAULT - 1 | 0 | SET work_mem FROM CURRENT -(34 rows) - -DROP ROLE regress_stat_set_1; -DROP ROLE regress_stat_set_2; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- --- Track the total number of rows retrieved or affected by the utility --- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, --- REFRESH MATERIALIZED VIEW and SELECT INTO --- -CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; -SELECT generate_series(1, 10) c INTO pgss_select_into; -COPY pgss_ctas (a, b) FROM STDIN; -CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas; -REFRESH MATERIALIZED VIEW pgss_matv; -BEGIN; -DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv; -FETCH NEXT pgss_cursor; - a | b ----+------ - 1 | ctas -(1 row) - -FETCH FORWARD 5 pgss_cursor; - a | b ----+------ - 2 | ctas - 3 | ctas - 4 | ctas - 5 | ctas - 6 | ctas -(5 rows) - -FETCH FORWARD ALL pgss_cursor; - a | b -----+------ - 7 | ctas - 8 | ctas - 9 | ctas - 10 | ctas - 11 | copy - 12 | copy - 13 | copy -(7 rows) - -COMMIT; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - 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, $1 b FROM generate_series($2, $3) a - 1 | 0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv - 1 | 5 | FETCH FORWARD $1 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, $2) c INTO pgss_select_into - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(12 rows) - -DROP MATERIALIZED VIEW pgss_matv; -DROP TABLE pgss_ctas; -DROP TABLE pgss_select_into; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Special cases. Keep these ones at the end to avoid conflicts. -SET SCHEMA 'foo'; -SET SCHEMA 'public'; -RESET ALL; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 1 | 0 | RESET ALL - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - 2 | 0 | SET SCHEMA $1 -(3 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -U3 /home/postgres/postgres/contrib/pg_stat_statements/expected/level_tracking.out /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/level_tracking.out --- /home/postgres/postgres/contrib/pg_stat_statements/expected/level_tracking.out 2025-12-07 14:51:09.551032688 +0000 +++ /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/level_tracking.out 2025-12-07 14:54:25.350642132 +0000 @@ -1,1570 +1 @@ --- --- Statement level tracking --- -SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- 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; - toplevel | calls | query -----------+-------+-------------------------------- - t | 1 | DELETE FROM stats_track_tab - t | 1 | DO $$ + - | | BEGIN + - | | DELETE FROM stats_track_tab;+ - | | END; + - | | $$ LANGUAGE plpgsql -(2 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- 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; - 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; + - | | END; $$ - 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() IS NOT NULL AS t - t | 1 | SET pg_stat_statements.track = $1 -(7 rows) - --- Procedure with multiple utility statements. -CREATE OR REPLACE PROCEDURE proc_with_utility_stmt() -LANGUAGE SQL -AS $$ - SHOW pg_stat_statements.track; - show pg_stat_statements.track; - SHOW pg_stat_statements.track_utility; -$$; -SET pg_stat_statements.track_utility = TRUE; --- all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CALL proc_with_utility_stmt(); -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C", toplevel; - toplevel | calls | query -----------+-------+---------------------------------------------------- - t | 1 | CALL proc_with_utility_stmt() - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - f | 2 | SHOW pg_stat_statements.track - f | 1 | SHOW pg_stat_statements.track_utility -(4 rows) - --- top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CALL proc_with_utility_stmt(); -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C", toplevel; - toplevel | calls | query -----------+-------+---------------------------------------------------- - t | 1 | CALL proc_with_utility_stmt() - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- EXPLAIN - all-level tracking. -CREATE TABLE test_table (x int); -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (SELECT 1, 2); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) TABLE stats_track_tab; - QUERY PLAN ------------------------------ - Seq Scan on stats_track_tab -(1 row) - -EXPLAIN (COSTS OFF) (TABLE test_table); - QUERY PLAN ------------------------- - Seq Scan on test_table -(1 row) - -EXPLAIN (COSTS OFF) VALUES (1); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (VALUES (1, 2)); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1; - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - -EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - -EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1)); - QUERY PLAN ---------------------------- - Insert on stats_track_tab - -> Result -(2 rows) - -EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab - USING (SELECT id FROM generate_series(1, 10) id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id); - QUERY PLAN -------------------------------------------------------- - Merge on stats_track_tab - -> Hash Right Join - Hash Cond: (stats_track_tab.x = id.id) - -> Seq Scan on stats_track_tab - -> Hash - -> Function Scan on generate_series id -(6 rows) - -EXPLAIN (COSTS OFF) SELECT 1 UNION SELECT 2; - QUERY PLAN --------------------------- - Unique - -> Sort - Sort Key: (1) - -> Append - -> Result - -> Result -(6 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2) - f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2); - t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table) - f | 1 | EXPLAIN (COSTS OFF) (TABLE test_table); - t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)) - f | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)); - t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab - f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; - t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)) - f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)); - t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); - t | 1 | EXPLAIN (COSTS OFF) SELECT $1 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2 - f | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2; - f | 1 | EXPLAIN (COSTS OFF) SELECT $1; - t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab - f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; - t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2 - f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2; - t | 1 | EXPLAIN (COSTS OFF) VALUES ($1) - f | 1 | EXPLAIN (COSTS OFF) VALUES ($1); - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(23 rows) - --- EXPLAIN - top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (SELECT 1, 2); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) TABLE stats_track_tab; - QUERY PLAN ------------------------------ - Seq Scan on stats_track_tab -(1 row) - -EXPLAIN (COSTS OFF) (TABLE test_table); - QUERY PLAN ------------------------- - Seq Scan on test_table -(1 row) - -EXPLAIN (COSTS OFF) VALUES (1); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (VALUES (1, 2)); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1; - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - -EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - -EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1)); - QUERY PLAN ---------------------------- - Insert on stats_track_tab - -> Result -(2 rows) - -EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab - USING (SELECT id FROM generate_series(1, 10) id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id); - QUERY PLAN -------------------------------------------------------- - Merge on stats_track_tab - -> Hash Right Join - Hash Cond: (stats_track_tab.x = id.id) - -> Seq Scan on stats_track_tab - -> Hash - -> Function Scan on generate_series id -(6 rows) - -EXPLAIN (COSTS OFF) SELECT 1 UNION SELECT 2; - QUERY PLAN --------------------------- - Unique - -> Sort - Sort Key: (1) - -> Append - -> Result - -> Result -(6 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+-------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2) - t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table) - t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)) - t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab - t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)) - t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - t | 1 | EXPLAIN (COSTS OFF) SELECT $1 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2 - t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab - t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2 - t | 1 | EXPLAIN (COSTS OFF) VALUES ($1) - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(12 rows) - --- EXPLAIN - all-level tracking with multi-statement strings. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- SELECT queries -EXPLAIN (COSTS OFF) SELECT 1\; EXPLAIN (COSTS OFF) SELECT 1, 2; - QUERY PLAN ------------- - Result -(1 row) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (SELECT 1, 2, 3)\; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3, 4); - QUERY PLAN ------------- - Result -(1 row) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4\; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; - QUERY PLAN ----------------------------- - Unique - -> Sort - Sort Key: (1), (2) - -> Append - -> Result - -> Result -(6 rows) - - QUERY PLAN ---------------------------------- - Unique - -> Sort - Sort Key: (1), (2), (3) - -> Append - -> Result - -> Result -(6 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6 - f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3); EXPLAIN (COSTS OFF) (SELECT 1, 2, 3, 4); - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4) - f | 1 | EXPLAIN (COSTS OFF) (SELECT 1, 2, 3); EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4); - t | 1 | EXPLAIN (COSTS OFF) SELECT $1 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4 - f | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; - f | 1 | EXPLAIN (COSTS OFF) SELECT $1; EXPLAIN (COSTS OFF) SELECT 1, 2; - f | 1 | EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4; EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6; - f | 1 | EXPLAIN (COSTS OFF) SELECT 1; EXPLAIN (COSTS OFF) SELECT $1, $2; - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(13 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Most DMLs -EXPLAIN (COSTS OFF) TABLE stats_track_tab\; EXPLAIN (COSTS OFF) (TABLE test_table); - QUERY PLAN ------------------------------ - Seq Scan on stats_track_tab -(1 row) - - QUERY PLAN ------------------------- - Seq Scan on test_table -(1 row) - -EXPLAIN (COSTS OFF) VALUES (1)\; EXPLAIN (COSTS OFF) (VALUES (1, 2)); - QUERY PLAN ------------- - Result -(1 row) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1\; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1; - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - -EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab\; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = 1; - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - -EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1))\; EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (1), (2); - QUERY PLAN ---------------------------- - Insert on stats_track_tab - -> Result -(2 rows) - - QUERY PLAN ---------------------------------- - Insert on stats_track_tab - -> Values Scan on "*VALUES*" -(2 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+---------------------------------------------------------------------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table) - t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)) - t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab - t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1 - f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1; - f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = 1; - t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ($1), ($2) - t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)) - f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)); EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (1), (2); - f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1)); EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ($1), ($2); - t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab - f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; EXPLAIN (COSTS OFF) (TABLE test_table); - f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; EXPLAIN (COSTS OFF) (TABLE test_table); - t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 - t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2 - f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1; - f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1; - t | 1 | EXPLAIN (COSTS OFF) VALUES ($1) - f | 1 | EXPLAIN (COSTS OFF) VALUES ($1); EXPLAIN (COSTS OFF) (VALUES (1, 2)); - f | 1 | EXPLAIN (COSTS OFF) VALUES (1); EXPLAIN (COSTS OFF) (VALUES ($1, $2)); - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(21 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- MERGE, worth its own. -EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab - USING (SELECT id FROM generate_series(1, 10) id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id)\; EXPLAIN (COSTS OFF) SELECT 1, 2, 3, 4, 5; - QUERY PLAN -------------------------------------------------------- - Merge on stats_track_tab - -> Hash Right Join - Hash Cond: (stats_track_tab.x = id.id) - -> Seq Scan on stats_track_tab - -> Hash - -> Function Scan on generate_series id -(6 rows) - - QUERY PLAN ------------- - Result -(1 row) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------------------------ - t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); EXPLAIN (COSTS OFF) SELECT 1, 2, 3, 4, 5; - f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series(1, 10) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5; - t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5 - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - --- EXPLAIN - top-level tracking with multi-statement strings. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) SELECT 1\; EXPLAIN (COSTS OFF) SELECT 1, 2; - QUERY PLAN ------------- - Result -(1 row) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (SELECT 1, 2, 3)\; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3, 4); - QUERY PLAN ------------- - Result -(1 row) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) TABLE stats_track_tab\; EXPLAIN (COSTS OFF) (TABLE test_table); - QUERY PLAN ------------------------------ - Seq Scan on stats_track_tab -(1 row) - - QUERY PLAN ------------------------- - Seq Scan on test_table -(1 row) - -EXPLAIN (COSTS OFF) VALUES (1)\; EXPLAIN (COSTS OFF) (VALUES (1, 2)); - QUERY PLAN ------------- - Result -(1 row) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1\; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1; - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - -EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab\; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = 1; - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - -EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1))\; EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1), (2)); - QUERY PLAN ---------------------------- - Insert on stats_track_tab - -> Result -(2 rows) - -ERROR: INSERT has more expressions than target columns -LINE 1: ...N (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1), (2)); - ^ -EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series(1, 10) id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id)\; EXPLAIN (COSTS OFF) SELECT 1, 2, 3, 4, 5; - QUERY PLAN -------------------------------------------------------- - Merge on stats_track_tab - -> Hash Right Join - Hash Cond: (stats_track_tab.x = id.id) - -> Seq Scan on stats_track_tab - -> Hash - -> Function Scan on generate_series id -(6 rows) - - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4\; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; - QUERY PLAN ----------------------------- - Unique - -> Sort - Sort Key: (1), (2) - -> Append - -> Result - -> Result -(6 rows) - - QUERY PLAN ---------------------------------- - Unique - -> Sort - Sort Key: (1), (2), (3) - -> Append - -> Result - -> Result -(6 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------------------------------------ - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6 - t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4) - t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table) - t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)) - t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab - t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1 - t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)) - t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab USING (SELECT id FROM generate_series($1, $2) id) ON x = id+ - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - t | 1 | EXPLAIN (COSTS OFF) SELECT $1 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4 - t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5 - t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab - t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 - t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2 - t | 1 | EXPLAIN (COSTS OFF) VALUES ($1) - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(18 rows) - --- EXPLAIN with CTEs - all-level tracking -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (WITH a AS (SELECT 4) (SELECT 1, 2)); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) UPDATE stats_track_tab SET x = 1 WHERE x = 1; - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) DELETE FROM stats_track_tab; - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) INSERT INTO stats_track_tab VALUES ((1)); - QUERY PLAN ---------------------------- - Insert on stats_track_tab - -> Result -(2 rows) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) MERGE INTO stats_track_tab - USING (SELECT id FROM generate_series(1, 10) id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id); - QUERY PLAN -------------------------------------------------------- - Merge on stats_track_tab - -> Hash Right Join - Hash Cond: (stats_track_tab.x = id.id) - -> Seq Scan on stats_track_tab - -> Hash - -> Function Scan on generate_series id -(6 rows) - -EXPLAIN (COSTS OFF) WITH a AS (select 4) SELECT 1 UNION SELECT 2; - QUERY PLAN --------------------------- - Unique - -> Sort - Sort Key: (1) - -> Append - -> Result - -> Result -(6 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3)) - f | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3)); - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab - f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab; - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2)) - f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2)); - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2 - f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2; - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3 - f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3; - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3 - f | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3; - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(15 rows) - --- EXPLAIN with CTEs - top-level tracking -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) (WITH a AS (SELECT 4) (SELECT 1, 2)); - QUERY PLAN ------------- - Result -(1 row) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) UPDATE stats_track_tab SET x = 1 WHERE x = 1; - QUERY PLAN ------------------------------------ - Update on stats_track_tab - -> Seq Scan on stats_track_tab - Filter: (x = 1) -(3 rows) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) DELETE FROM stats_track_tab; - QUERY PLAN ------------------------------------ - Delete on stats_track_tab - -> Seq Scan on stats_track_tab -(2 rows) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) INSERT INTO stats_track_tab VALUES ((1)); - QUERY PLAN ---------------------------- - Insert on stats_track_tab - -> Result -(2 rows) - -EXPLAIN (COSTS OFF) WITH a AS (SELECT 4) MERGE INTO stats_track_tab - USING (SELECT id FROM generate_series(1, 10) id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id); - QUERY PLAN -------------------------------------------------------- - Merge on stats_track_tab - -> Hash Right Join - Hash Cond: (stats_track_tab.x = id.id) - -> Seq Scan on stats_track_tab - -> Hash - -> Function Scan on generate_series id -(6 rows) - -EXPLAIN (COSTS OFF) WITH a AS (select 4) SELECT 1 UNION SELECT 2; - QUERY PLAN --------------------------- - Unique - -> Sort - Sort Key: (1) - -> Append - -> Result - -> Result -(6 rows) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------------------ - t | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3)) - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2)) - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2 - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3 - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3 - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(8 rows) - --- Explain analyze, all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 100; - QUERY PLAN ------------------------------------ - Result (actual rows=1.00 loops=1) -(1 row) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) - DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; - QUERY PLAN --------------------------------------------------------- - Seq Scan on stats_track_tab (actual rows=0.00 loops=1) -(1 row) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------- - t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) + - | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab - f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) + - | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; - t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1 - f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1; - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - --- Explain analyze, top tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT 100; - QUERY PLAN ------------------------------------ - Result (actual rows=1.00 loops=1) -(1 row) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) - DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; - QUERY PLAN --------------------------------------------------------- - Seq Scan on stats_track_tab (actual rows=0.00 loops=1) -(1 row) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------ - t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) + - | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab - t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1 - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(3 rows) - --- Create Materialized View, all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CREATE MATERIALIZED VIEW pgss_materialized_view AS - SELECT * FROM generate_series(1, 5) as id; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+---------------------------------------------------- - t | 1 | CREATE MATERIALIZED VIEW pgss_materialized_view AS+ - | | SELECT * FROM generate_series($1, $2) as id - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- CREATE MATERIALIZED VIEW, top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS - SELECT * FROM generate_series(1, 5) as id; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------ - t | 1 | CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS+ - | | SELECT * FROM generate_series($1, $2) as id - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- REFRESH MATERIALIZED VIEW, all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -REFRESH MATERIALIZED VIEW pgss_materialized_view; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+---------------------------------------------------- - t | 1 | REFRESH MATERIALIZED VIEW pgss_materialized_view - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- REFRESH MATERIALIZED VIEW, top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -REFRESH MATERIALIZED VIEW pgss_materialized_view; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+---------------------------------------------------- - t | 1 | REFRESH MATERIALIZED VIEW pgss_materialized_view - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- CREATE TABLE AS, all-level tracking. -SET pg_stat_statements.track = 'all'; -PREPARE test_prepare_pgss AS select generate_series(1, 10); -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT 1; -CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+----------------------------------------------------------------- - t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1 - f | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1; - t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss - f | 1 | PREPARE test_prepare_pgss AS select generate_series($1, $2) - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - --- CREATE TABLE AS, top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT 1; -CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+----------------------------------------------------------------- - t | 1 | CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT $1 - t | 1 | CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(3 rows) - --- EXPLAIN with CREATE TABLE AS - all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+---------------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1 - f | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1; - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(3 rows) - --- EXPLAIN with CREATE TABLE AS - top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; - QUERY PLAN ------------- - Result -(1 row) - -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1 - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- DECLARE CURSOR, all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -BEGIN; -DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; -FETCH FORWARD 1 FROM foocur; - x ---- -(0 rows) - -CLOSE foocur; -COMMIT; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+---------------------------------------------------------- - t | 1 | BEGIN - t | 1 | CLOSE foocur - t | 1 | COMMIT - t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab - f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; - t | 1 | FETCH FORWARD $1 FROM foocur - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(7 rows) - --- DECLARE CURSOR, top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -BEGIN; -DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab; -FETCH FORWARD 1 FROM foocur; - x ---- -(0 rows) - -CLOSE foocur; -COMMIT; -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------- - t | 1 | BEGIN - t | 1 | CLOSE foocur - t | 1 | COMMIT - t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab - t | 1 | FETCH FORWARD $1 FROM foocur - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(6 rows) - --- COPY - all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -COPY (SELECT 1) TO stdout; -1 -COPY (SELECT 1 UNION SELECT 2) TO stdout; -1 -2 -COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout; -1 -COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) TO stdout; -1 -COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout; -2 -2 -COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout; -2 -2 -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+----------------------------------------------------------------------------- - f | 1 | COPY (DELETE FROM stats_track_tab WHERE x = $1 RETURNING x) TO stdout - t | 1 | COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout - f | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x) TO stdout - t | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) TO stdout - f | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout - t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout - f | 1 | COPY (SELECT $1 UNION SELECT $2) TO stdout - f | 1 | COPY (SELECT $1) TO stdout - t | 1 | COPY (SELECT 1 UNION SELECT 2) TO stdout - t | 1 | COPY (SELECT 1) TO stdout - f | 1 | COPY (UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x) TO stdout - t | 1 | COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(13 rows) - --- COPY - top-level tracking. -SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -COPY (SELECT 1) TO stdout; -1 -COPY (SELECT 1 UNION SELECT 2) TO stdout; -1 -2 -COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id - WHEN MATCHED THEN UPDATE SET x = id - WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout; -1 -COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) TO stdout; -1 -COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout; -2 -2 -COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout; -2 -2 -SELECT toplevel, calls, query FROM pg_stat_statements - ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------------------- - t | 1 | COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout - t | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) TO stdout - t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout - t | 1 | COPY (SELECT 1 UNION SELECT 2) TO stdout - t | 1 | COPY (SELECT 1) TO stdout - t | 1 | COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(7 rows) - --- DO block - top-level tracking without utility. -SET pg_stat_statements.track = 'top'; -SET pg_stat_statements.track_utility = FALSE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -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; - toplevel | calls | query -----------+-------+---------------------------------------------------- - t | 1 | DELETE FROM stats_track_tab - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) - --- DO block - all-level tracking without utility. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -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; - toplevel | calls | query -----------+-------+---------------------------------------------------- - f | 1 | DELETE FROM stats_track_tab - t | 1 | DELETE FROM stats_track_tab - f | 1 | SELECT $1::TEXT - t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(4 rows) - --- DO block --- multiple inner queries with separators -SET pg_stat_statements.track = 'all'; -SET pg_stat_statements.track_utility = TRUE; -CREATE TABLE pgss_do_util_tab_1 (a int); -CREATE TABLE pgss_do_util_tab_2 (a int); -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -DO $$ -DECLARE BEGIN - EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table'; - EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2'; -END $$; -SELECT toplevel, calls, rows, query FROM pg_stat_statements - WHERE toplevel IS FALSE - ORDER BY query COLLATE "C"; - toplevel | calls | rows | query -----------+-------+------+------------------------------------- - f | 1 | 0 | CREATE TABLE pgss_do_table (id INT) - f | 1 | 0 | DROP TABLE pgss_do_table - f | 1 | 0 | SELECT a FROM pgss_do_util_tab_1 - f | 1 | 0 | SELECT a FROM pgss_do_util_tab_2 -(4 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- Note the extra semicolon at the end of the query. -DO $$ -DECLARE BEGIN - EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table;'; - EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2;'; -END $$; -SELECT toplevel, calls, rows, query FROM pg_stat_statements - WHERE toplevel IS FALSE - ORDER BY query COLLATE "C"; - toplevel | calls | rows | query -----------+-------+------+------------------------------------- - f | 1 | 0 | CREATE TABLE pgss_do_table (id INT) - f | 1 | 0 | DROP TABLE pgss_do_table - f | 1 | 0 | SELECT a FROM pgss_do_util_tab_1 - f | 1 | 0 | SELECT a FROM pgss_do_util_tab_2 -(4 rows) - -DROP TABLE pgss_do_util_tab_1, pgss_do_util_tab_2; --- PL/pgSQL function - top-level tracking. -SET pg_stat_statements.track = 'top'; -SET pg_stat_statements.track_utility = FALSE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 2 | 2 | SELECT PLUS_ONE($1) - 2 | 2 | SELECT PLUS_TWO($1) - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(3 rows) - --- immutable SQL function --- can be executed at plan time -CREATE FUNCTION PLUS_THREE(i INTEGER) RETURNS INTEGER AS -$$ SELECT i + 3 LIMIT 1 $$ IMMUTABLE LANGUAGE SQL; -SELECT PLUS_THREE(8); - plus_three ------------- - 11 -(1 row) - -SELECT PLUS_THREE(10); - plus_three ------------- - 13 -(1 row) - -SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | rows | query -----------+-------+------+------------------------------------------------------------------------------ - t | 2 | 2 | SELECT PLUS_ONE($1) - t | 2 | 2 | SELECT PLUS_THREE($1) - t | 2 | 2 | SELECT PLUS_TWO($1) - t | 1 | 3 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C" - t | 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - --- PL/pgSQL function - all-level tracking. -SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- we drop and recreate the functions to avoid any caching funnies -DROP FUNCTION PLUS_ONE(INTEGER); -DROP FUNCTION PLUS_TWO(INTEGER); -DROP FUNCTION PLUS_THREE(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+---------------------------------------------------- - 2 | 2 | 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) - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) - --- immutable SQL function --- can be executed at plan time -CREATE FUNCTION PLUS_THREE(i INTEGER) RETURNS INTEGER AS -$$ SELECT i + 3 LIMIT 1 $$ IMMUTABLE LANGUAGE SQL; -SELECT PLUS_THREE(8); - plus_three ------------- - 11 -(1 row) - -SELECT PLUS_THREE(10); - plus_three ------------- - 13 -(1 row) - -SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | rows | query -----------+-------+------+------------------------------------------------------------------------------ - f | 2 | 2 | SELECT (i + $2 + $3)::INTEGER - f | 2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3 - t | 2 | 2 | SELECT PLUS_ONE($1) - t | 2 | 2 | SELECT PLUS_THREE($1) - t | 2 | 2 | SELECT PLUS_TWO($1) - t | 1 | 5 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C" - f | 2 | 2 | SELECT i + $2 LIMIT $3 - t | 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(8 rows) - --- --- pg_stat_statements.track = none --- -SET pg_stat_statements.track = 'none'; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - -SELECT 1 AS "one"; - one ------ - 1 -(1 row) - -SELECT 1 + 1 AS "two"; - two ------ - 2 -(1 row) - -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+------- -(0 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - +psql: error: connection to server on socket "/tmp/pg_regress-s4gEQ8/.s.PGSQL.40038" failed: FATAL: the database system is in recovery mode diff -U3 /home/postgres/postgres/contrib/pg_stat_statements/expected/planning.out /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/planning.out --- /home/postgres/postgres/contrib/pg_stat_statements/expected/planning.out 2025-12-07 14:51:09.555213991 +0000 +++ /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/planning.out 2025-12-07 14:54:25.365686703 +0000 @@ -1,88 +1,2 @@ --- --- Information related to planning --- --- These tests require track_planning to be enabled. -SET pg_stat_statements.track_planning = TRUE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- --- [re]plan counting --- -CREATE TABLE stats_plan_test (); -PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test; -EXECUTE prep1; - count -------- - 0 -(1 row) - -EXECUTE prep1; - count -------- - 0 -(1 row) - -EXECUTE prep1; - count -------- - 0 -(1 row) - -ALTER TABLE stats_plan_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 plans, calls, rows, query FROM pg_stat_statements - WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; - plans | calls | rows | query --------+-------+------+---------------------------------------------------------- - 0 | 1 | 0 | ALTER TABLE stats_plan_test ADD COLUMN x int - 0 | 1 | 0 | CREATE TABLE stats_plan_test () - 3 | 3 | 3 | SELECT $1 - 0 | 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - 1 | 0 | 0 | SELECT plans, calls, rows, query FROM pg_stat_statements+ - | | | 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 plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements - WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; - plans_ok | calls | rows | query -----------+-------+------+------------------------------------------------------- - t | 4 | 4 | PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test -(1 row) - --- Cleanup -DROP TABLE stats_plan_test; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - +psql: error: connection to server on socket "/tmp/pg_regress-s4gEQ8/.s.PGSQL.40038" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/postgres/postgres/contrib/pg_stat_statements/expected/user_activity.out /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/user_activity.out --- /home/postgres/postgres/contrib/pg_stat_statements/expected/user_activity.out 2025-12-07 14:51:09.559375238 +0000 +++ /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/user_activity.out 2025-12-07 14:54:25.376905683 +0000 @@ -1,209 +1,2 @@ --- --- Track user activity and reset them --- -SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(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() IS NOT NULL AS t | 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) IS NOT NULL AS t; - t ---- - f -(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) IS NOT NULL AS t | 1 | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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)) - IS NOT NULL AS t; - t ---- - t -(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)) +| | - IS NOT NULL AS t | | - SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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) IS NOT NULL AS t - FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"'; - t ---- - t - t -(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)) +| | - IS NOT NULL AS t | | - SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 - SELECT pg_stat_statements_reset($1,$2,s.queryid) IS NOT NULL AS t +| 1 | 2 - FROM pg_stat_statements AS s WHERE s.query = $3 | | - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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) IS NOT NULL AS t - FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1'; - t ---- - t -(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)) +| | - IS NOT NULL AS t | | - SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 - SELECT pg_stat_statements_reset($1,$2,s.queryid) IS NOT NULL AS t +| 1 | 2 - FROM pg_stat_statements AS s WHERE s.query = $3 | | - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 - SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t +| 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) IS NOT NULL AS t; - t ---- - t -(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) IS NOT NULL AS t | 1 | 1 -(1 row) - --- --- cleanup --- -DROP ROLE regress_stats_user1; -DROP ROLE regress_stats_user2; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - +psql: error: connection to server on socket "/tmp/pg_regress-s4gEQ8/.s.PGSQL.40038" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/postgres/postgres/contrib/pg_stat_statements/expected/wal.out /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/wal.out --- /home/postgres/postgres/contrib/pg_stat_statements/expected/wal.out 2025-12-07 14:51:09.561429072 +0000 +++ /home/postgres/postgres/build/testrun/pg_stat_statements/regress/results/wal.out 2025-12-07 14:54:25.399277490 +0000 @@ -17,10 +17,9 @@ --------------------------------------------------------------+-------+------+---------------------+-----------------------+--------------------- DELETE FROM pgss_wal_tab WHERE a > $1 | 1 | 1 | t | t | t INSERT INTO pgss_wal_tab VALUES(generate_series($1, $2), $3) | 1 | 10 | t | t | t - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 | f | f | f 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 -(5 rows) +(4 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; t