diff -U3 /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/select.out /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/select.out --- /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/select.out 2024-03-11 18:11:52.460951000 +0000 +++ /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/select.out 2024-03-11 18:16:48.936007000 +0000 @@ -319,96 +319,7 @@ FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c) GROUP BY ROLLUP(a, b), rollup(a, c) ORDER BY a, b, c; - a | b | c ----+---+--- - 1 | 2 | 3 - 1 | 2 | - 1 | 2 | - 1 | | 3 - 1 | | 3 - 1 | | - 1 | | - 1 | | - 4 | | 6 - 4 | | 6 - 4 | | 6 - 4 | | - 4 | | - 4 | | - 4 | | - 4 | | - 7 | 8 | 9 - 7 | 8 | - 7 | 8 | - 7 | | 9 - 7 | | 9 - 7 | | - 7 | | - 7 | | - | | -(25 rows) - -SELECT a, b, c -FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c) -GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c) -ORDER BY a, b, c; - a | b | c ----+---+--- - 1 | 2 | 3 - 1 | 2 | - 1 | | 3 - 1 | | - 4 | | 6 - 4 | | 6 - 4 | | - 4 | | - 7 | 8 | 9 - 7 | 8 | - 7 | | 9 - 7 | | - | | -(13 rows) - -SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%'; - count -------- - 2 -(1 row) - --- GROUPING SET agglevelsup -SELECT ( - SELECT ( - SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c) - ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b) -) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f); - grouping ----------- - 0 - 0 - 0 -(3 rows) - -SELECT ( - SELECT ( - SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c) - ) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b) -) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f); - grouping ----------- - 3 - 0 - 1 -(3 rows) - -SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; - count -------- - 2 -(1 row) - -SELECT pg_stat_statements_reset() 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 /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/dml.out /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/dml.out --- /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/dml.out 2024-03-11 18:11:52.460851000 +0000 +++ /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/dml.out 2024-03-11 18:16:48.946464000 +0000 @@ -1,174 +1,2 @@ --- --- 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 ; - a | b ----+---------------------- - 6 | 666 - 7 | aaa - 8 | bbb - 9 | bbb -(4 rows) - -SELECT * - FROM pgss_dml_tab - WHERE a > 9 - ORDER BY a ; - a | b ----+--- -(0 rows) - --- these two need to be done on a different table --- SELECT without constants -SELECT * FROM pgss_dml_tab ORDER BY a; - a | b ----+---------------------- - 1 | a - 1 | 111 - 2 | b - 2 | 222 - 3 | c - 3 | 333 - 4 | 444 - 5 | 555 - 6 | 666 - 7 | aaa - 8 | bbb - 9 | bbb -(12 rows) - --- SELECT with IN clause -SELECT * FROM pgss_dml_tab WHERE a IN (1, 2, 3, 4, 5); - a | b ----+---------------------- - 1 | 111 - 2 | 222 - 3 | 333 - 4 | 444 - 5 | 555 - 1 | a - 2 | b - 3 | c -(8 rows) - -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+--------------------------------------------------------------------- - 1 | 1 | DELETE FROM pgss_dml_tab WHERE a > $1 - 1 | 3 | INSERT INTO pgss_dml_tab (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) - 1 | 10 | INSERT INTO pgss_dml_tab VALUES(generate_series($1, $2), $3) - 1 | 12 | SELECT * FROM pgss_dml_tab ORDER BY a - 2 | 4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a - 1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5) - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - 1 | 0 | SET pg_stat_statements.track_utility = FALSE - 6 | 6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2 - 1 | 3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2 -(10 rows) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- MERGE -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) - WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text; -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) - WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text; -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) - WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text; -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) - WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL); -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) - WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) - WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0); -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) - WHEN NOT MATCHED THEN INSERT (a) VALUES (0); -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) - WHEN MATCHED THEN DELETE; -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) - WHEN MATCHED THEN DO NOTHING; -MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) - WHEN NOT MATCHED THEN DO NOTHING; -DROP TABLE pgss_dml_tab; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+----------------------------------------------------------------------------------------- - 1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+ - | | WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text - 1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+ - | | WHEN MATCHED THEN DELETE - 1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+ - | | WHEN MATCHED THEN DO NOTHING - 1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+ - | | WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text - 1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+ - | | WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text - 1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+ - | | WHEN NOT MATCHED THEN DO NOTHING - 1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) + - | | WHEN NOT MATCHED THEN INSERT (a) VALUES ($1) - 2 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) + - | | WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2) - 1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) + - | | WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2) - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(10 rows) - --- 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() IS NOT NULL AS t; - t ---- - t -(1 row) - -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; - temp_written_ok | temp_dirtied_ok | written_ok | dirtied_ok ------------------+-----------------+------------+------------ - t | t | t | t -(1 row) - -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - +psql: error: connection to server on socket "/tmp/pg_regress-6Hej5z/.s.PGSQL.40034" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/cursors.out /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/cursors.out --- /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/cursors.out 2024-03-11 18:11:52.460836000 +0000 +++ /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/cursors.out 2024-03-11 18:16:48.955182000 +0000 @@ -1,70 +1,2 @@ --- --- Cursors --- --- These tests require track_utility to be enabled. -SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- 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"; - calls | rows | query --------+------+------------------------------------------------------- - 2 | 0 | CLOSE cursor_stats_1 - 2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $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) - --- 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; - ?column? ----------- - 2 -(1 row) - -FETCH 1 IN cursor_stats_2; - ?column? ----------- - 3 -(1 row) - -CLOSE cursor_stats_1; -CLOSE cursor_stats_2; -COMMIT; -SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+------------------------------------------------------- - 1 | 0 | BEGIN - 1 | 0 | CLOSE cursor_stats_1 - 1 | 0 | CLOSE cursor_stats_2 - 1 | 0 | COMMIT - 1 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 - 1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1 - 1 | 1 | FETCH 1 IN cursor_stats_1 - 1 | 1 | FETCH 1 IN cursor_stats_2 - 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(9 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-6Hej5z/.s.PGSQL.40034" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/utility.out /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/utility.out --- /tmp/cirrus-ci-build/contrib/pg_stat_statements/expected/utility.out 2024-03-11 18:11:52.460986000 +0000 +++ /tmp/cirrus-ci-build/build/testrun/pg_stat_statements/regress/results/utility.out 2024-03-11 18:16:48.965972000 +0000 @@ -1,666 +1,2 @@ --- --- Utility commands --- --- These tests require track_utility to be enabled. -SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset() IS NOT NULL AS t; - t ---- - t -(1 row) - --- 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"; -NOTICE: table "tab_stats" does not exist, skipping -NOTICE: table "tab_stats" does not exist, skipping -NOTICE: table "tab_stats" does not exist, skipping - calls | rows | query --------+------+-------------------------------------------------------------------------------------- - 1 | 0 | ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0) - 1 | 0 | ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b - 1 | 0 | ALTER TABLE tab_stats ALTER COLUMN b set default 'a' - 1 | 0 | CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0 - 1 | 0 | CREATE TEMP TABLE tab_stats (a int, b char(20)) - 3 | 0 | DROP TABLE IF EXISTS tab_stats - 1 | 0 | DROP TABLE tab_stats - 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) - --- 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"; - 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 - 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. -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"; - calls | rows | query --------+------+---------------------------------------------------- - 2 | 0 | BEGIN - 2 | 0 | COMMIT - 2 | 0 | RESET SESSION AUTHORIZATION - 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 DEFAULT - 1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT - 1 | 0 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED - 1 | 0 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ - 1 | 0 | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE - 1 | 0 | SET enable_seqscan = off - 1 | 0 | SET enable_seqscan = on - 2 | 0 | SET work_mem = '1MB' - 1 | 0 | SET work_mem = '2MB' -(15 rows) - -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 5 pgss_cursor - 1 | 7 | FETCH FORWARD ALL pgss_cursor - 1 | 1 | FETCH NEXT pgss_cursor - 1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv - 1 | 10 | SELECT generate_series($1, $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) - +psql: error: connection to server on socket "/tmp/pg_regress-6Hej5z/.s.PGSQL.40034" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached.