diff -w -U3 C:/cirrus/src/test/regress/expected/privileges.out C:/cirrus/build/testrun/regress/regress/results/privileges.out --- C:/cirrus/src/test/regress/expected/privileges.out 2024-03-11 18:11:56.538828000 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/privileges.out 2024-03-11 18:14:46.906884200 +0000 @@ -1731,1185 +1731,10 @@ -- Do the same concurrently CREATE INDEX CONCURRENTLY sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))) WHERE sro_ifun(a + 10) > sro_ifun(10); --- REINDEX -REINDEX TABLE sro_tab; -REINDEX INDEX sro_idx; -REINDEX TABLE CONCURRENTLY sro_tab; -DROP INDEX sro_idx; --- CLUSTER -CREATE INDEX sro_cluster_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))); -CLUSTER sro_tab USING sro_cluster_idx; -DROP INDEX sro_cluster_idx; --- BRIN index -CREATE INDEX sro_brin ON sro_tab USING brin ((sro_ifun(a) + sro_ifun(0))); -SELECT brin_desummarize_range('sro_brin', 0); - brin_desummarize_range ------------------------- - -(1 row) - -SELECT brin_summarize_range('sro_brin', 0); - brin_summarize_range ----------------------- - 1 -(1 row) - -DROP TABLE sro_tab; --- Check with a partitioned table -CREATE TABLE sro_ptab (a int) PARTITION BY RANGE (a); -ALTER TABLE sro_ptab OWNER TO regress_sro_user; -CREATE TABLE sro_part PARTITION OF sro_ptab FOR VALUES FROM (1) TO (10); -ALTER TABLE sro_part OWNER TO regress_sro_user; -INSERT INTO sro_ptab VALUES (1), (2), (3); -CREATE INDEX sro_pidx ON sro_ptab ((sro_ifun(a) + sro_ifun(0))) - WHERE sro_ifun(a + 10) > sro_ifun(10); -REINDEX TABLE sro_ptab; -REINDEX INDEX CONCURRENTLY sro_pidx; -SET SESSION AUTHORIZATION regress_sro_user; -CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS - 'GRANT regress_priv_group2 TO regress_sro_user'; -CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS - 'DECLARE c CURSOR WITH HOLD FOR SELECT public.unwanted_grant(); SELECT true'; --- REFRESH of this MV will queue a GRANT at end of transaction -CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; -REFRESH MATERIALIZED VIEW sro_mv; -ERROR: cannot create a cursor WITH HOLD within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 -\c - -REFRESH MATERIALIZED VIEW sro_mv; -ERROR: cannot create a cursor WITH HOLD within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 -SET SESSION AUTHORIZATION regress_sro_user; --- INSERT to this table will queue a GRANT at end of transaction -CREATE TABLE sro_trojan_table (); -CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS - 'BEGIN PERFORM public.unwanted_grant(); RETURN NULL; END'; -CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table - INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); --- Now, REFRESH will issue such an INSERT, queueing the GRANT -CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS - 'INSERT INTO public.sro_trojan_table DEFAULT VALUES; SELECT true'; -REFRESH MATERIALIZED VIEW sro_mv; -ERROR: cannot fire deferred trigger within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 -\c - -REFRESH MATERIALIZED VIEW sro_mv; -ERROR: cannot fire deferred trigger within security-restricted operation -CONTEXT: SQL function "mv_action" statement 1 -BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; -ERROR: permission denied to grant role "regress_priv_group2" -DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. -CONTEXT: SQL function "unwanted_grant" statement 1 -SQL statement "SELECT public.unwanted_grant()" -PL/pgSQL function public.sro_trojan() line 1 at PERFORM -SQL function "mv_action" statement 1 --- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions() -SET SESSION AUTHORIZATION regress_sro_user; -CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int - IMMUTABLE LANGUAGE plpgsql AS $$ -BEGIN - PERFORM public.unwanted_grant(); - RAISE WARNING 'owned'; - RETURN 1; -EXCEPTION WHEN OTHERS THEN - RETURN 2; -END$$; -CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c; -CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0; -\c - -REFRESH MATERIALIZED VIEW CONCURRENTLY sro_index_mv; -REFRESH MATERIALIZED VIEW sro_index_mv; -DROP OWNED BY regress_sro_user; -DROP ROLE regress_sro_user; --- Admin options -SET SESSION AUTHORIZATION regress_priv_user4; -CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS - 'GRANT regress_priv_group2 TO regress_priv_user5'; -GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION -SET ROLE regress_priv_group2; -GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege -ERROR: permission denied to grant role "regress_priv_group2" -DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. -SET SESSION AUTHORIZATION regress_priv_user1; -GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION -ERROR: permission denied to grant role "regress_priv_group2" -DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. -SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN -NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4" - dogrant_ok ------------- - -(1 row) - -SET ROLE regress_priv_group2; -GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help -ERROR: permission denied to grant role "regress_priv_group2" -DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. -SET SESSION AUTHORIZATION regress_priv_group2; -GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin -ERROR: permission denied to grant role "regress_priv_group2" -DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. -SET SESSION AUTHORIZATION regress_priv_user4; -DROP FUNCTION dogrant_ok(); -REVOKE regress_priv_group2 FROM regress_priv_user5; --- has_sequence_privilege tests -\c - -CREATE SEQUENCE x_seq; -GRANT USAGE on x_seq to regress_priv_user2; -SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT'); -ERROR: "atest1" is not a sequence -SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT'); -ERROR: unrecognized privilege type: "INSERT" -SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT'); - has_sequence_privilege ------------------------- - f -(1 row) - -SET SESSION AUTHORIZATION regress_priv_user2; -SELECT has_sequence_privilege('x_seq', 'USAGE'); - has_sequence_privilege ------------------------- - t -(1 row) - --- largeobject privilege tests -\c - -SET SESSION AUTHORIZATION regress_priv_user1; -SELECT lo_create(1001); - lo_create ------------ - 1001 -(1 row) - -SELECT lo_create(1002); - lo_create ------------ - 1002 -(1 row) - -SELECT lo_create(1003); - lo_create ------------ - 1003 -(1 row) - -SELECT lo_create(1004); - lo_create ------------ - 1004 -(1 row) - -SELECT lo_create(1005); - lo_create ------------ - 1005 -(1 row) - -GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; -GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2; -GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2; -GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2; -GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION; -GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed -ERROR: invalid privilege type INSERT for large object -GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed -ERROR: role "nosuchuser" does not exist -GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed -ERROR: large object 999 does not exist -\c - -SET SESSION AUTHORIZATION regress_priv_user2; -SELECT lo_create(2001); - lo_create ------------ - 2001 -(1 row) - -SELECT lo_create(2002); - lo_create ------------ - 2002 -(1 row) - -SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now - loread --------- - \x -(1 row) - -SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode -ERROR: large object descriptor 0 was not opened for writing -SELECT loread(lo_open(1001, x'40000'::int), 32); - loread --------- - \x -(1 row) - -SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied -ERROR: permission denied for large object 1002 -SELECT loread(lo_open(1003, x'40000'::int), 32); - loread --------- - \x -(1 row) - -SELECT loread(lo_open(1004, x'40000'::int), 32); - loread --------- - \x -(1 row) - -SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); - lowrite ---------- - 4 -(1 row) - -SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied -ERROR: permission denied for large object 1002 -SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied -ERROR: permission denied for large object 1003 -SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); - lowrite ---------- - 4 -(1 row) - -GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3; -GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied -ERROR: large object 1006 does not exist -REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; -GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3; -SELECT lo_unlink(1001); -- to be denied -ERROR: must be owner of large object 1001 -SELECT lo_unlink(2002); - lo_unlink ------------ - 1 -(1 row) - -\c - --- confirm ACL setting -SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; - oid | ownername | lomacl -------+--------------------+------------------------------------------------------------------------------------------------------------------------------ - 1001 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,=rw/regress_priv_user1} - 1002 | regress_priv_user1 | - 1003 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r/regress_priv_user1} - 1004 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=rw/regress_priv_user1} - 1005 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r*w/regress_priv_user1,regress_priv_user3=r/regress_priv_user2} - 2001 | regress_priv_user2 | {regress_priv_user2=rw/regress_priv_user2,regress_priv_user3=rw/regress_priv_user2} -(6 rows) - -SET SESSION AUTHORIZATION regress_priv_user3; -SELECT loread(lo_open(1001, x'40000'::int), 32); - loread ------------- - \x61626364 -(1 row) - -SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied -ERROR: permission denied for large object 1003 -SELECT loread(lo_open(1005, x'40000'::int), 32); - loread --------- - \x -(1 row) - -SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied -ERROR: permission denied for large object 1005 -SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); - lo_truncate -------------- - 0 -(1 row) - --- compatibility mode in largeobject permission -\c - -SET lo_compat_privileges = false; -- default setting -SET SESSION AUTHORIZATION regress_priv_user4; -SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied -ERROR: permission denied for large object 1002 -SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied -ERROR: permission denied for large object 1002 -SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied -ERROR: permission denied for large object 1002 -SELECT lo_put(1002, 1, 'abcd'); -- to be denied -ERROR: permission denied for large object 1002 -SELECT lo_unlink(1002); -- to be denied -ERROR: must be owner of large object 1002 -SELECT lo_export(1001, '/dev/null'); -- to be denied -ERROR: permission denied for function lo_export -SELECT lo_import('/dev/null'); -- to be denied -ERROR: permission denied for function lo_import -SELECT lo_import('/dev/null', 2003); -- to be denied -ERROR: permission denied for function lo_import -\c - -SET lo_compat_privileges = true; -- compatibility mode -SET SESSION AUTHORIZATION regress_priv_user4; -SELECT loread(lo_open(1002, x'40000'::int), 32); - loread --------- - \x -(1 row) - -SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); - lowrite ---------- - 4 -(1 row) - -SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); - lo_truncate -------------- - 0 -(1 row) - -SELECT lo_unlink(1002); - lo_unlink ------------ - 1 -(1 row) - -SELECT lo_export(1001, '/dev/null'); -- to be denied -ERROR: permission denied for function lo_export --- don't allow unpriv users to access pg_largeobject contents -\c - -SELECT * FROM pg_largeobject LIMIT 0; - loid | pageno | data -------+--------+------ -(0 rows) - -SET SESSION AUTHORIZATION regress_priv_user1; -SELECT * FROM pg_largeobject LIMIT 0; -- to be denied -ERROR: permission denied for table pg_largeobject --- pg_signal_backend can't signal superusers -RESET SESSION AUTHORIZATION; -BEGIN; -CREATE OR REPLACE FUNCTION terminate_nothrow(pid int) RETURNS bool - LANGUAGE plpgsql SECURITY DEFINER SET client_min_messages = error AS $$ -BEGIN - RETURN pg_terminate_backend($1); -EXCEPTION WHEN OTHERS THEN - RETURN false; -END$$; -ALTER FUNCTION terminate_nothrow OWNER TO pg_signal_backend; -SELECT backend_type FROM pg_stat_activity -WHERE CASE WHEN COALESCE(usesysid, 10) = 10 THEN terminate_nothrow(pid) END; - backend_type --------------- -(0 rows) - -ROLLBACK; --- test pg_database_owner -RESET SESSION AUTHORIZATION; -GRANT pg_database_owner TO regress_priv_user1; -ERROR: role "pg_database_owner" cannot have explicit members -GRANT regress_priv_user1 TO pg_database_owner; -ERROR: role "pg_database_owner" cannot be a member of any role -CREATE TABLE datdba_only (); -ALTER TABLE datdba_only OWNER TO pg_database_owner; -REVOKE DELETE ON datdba_only FROM pg_database_owner; -SELECT - pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv, - pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem, - pg_has_role('regress_priv_user1', 'pg_database_owner', - 'MEMBER WITH ADMIN OPTION') as admin; - priv | mem | admin -------+-----+------- - f | f | f -(1 row) - -BEGIN; -DO $$BEGIN EXECUTE format( - 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$; -SELECT - pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv, - pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem, - pg_has_role('regress_priv_user1', 'pg_database_owner', - 'MEMBER WITH ADMIN OPTION') as admin; - priv | mem | admin -------+-----+------- - t | t | f -(1 row) - -SET SESSION AUTHORIZATION regress_priv_user1; -TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C"; - role_name ---------------------- - pg_database_owner - regress_priv_group2 - regress_priv_user1 -(3 rows) - -TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C"; - grantee | role_name | is_grantable ----------------------+---------------------+-------------- - regress_priv_group2 | pg_database_owner | NO - regress_priv_user1 | regress_priv_group2 | NO -(2 rows) - -INSERT INTO datdba_only DEFAULT VALUES; -SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q; -ERROR: permission denied for table datdba_only -SET SESSION AUTHORIZATION regress_priv_user2; -TABLE information_schema.enabled_roles; - role_name --------------------- - regress_priv_user2 -(1 row) - -INSERT INTO datdba_only DEFAULT VALUES; -ERROR: permission denied for table datdba_only -ROLLBACK; --- test default ACLs -\c - -CREATE SCHEMA testns; -GRANT ALL ON SCHEMA testns TO regress_priv_user1; -CREATE TABLE testns.acltest1 (x int); -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no - has_table_privilege ---------------------- - f -(1 row) - --- placeholder for test with duplicated schema and role names -ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -DROP TABLE testns.acltest1; -CREATE TABLE testns.acltest1 (x int); -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes - has_table_privilege ---------------------- - t -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1; -DROP TABLE testns.acltest1; -CREATE TABLE testns.acltest1 (x int); -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes - has_table_privilege ---------------------- - t -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes - has_table_privilege ---------------------- - t -(1 row) - -ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1; -DROP TABLE testns.acltest1; -CREATE TABLE testns.acltest1 (x int); -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes - has_table_privilege ---------------------- - t -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; -ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error -ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS --- Test makeaclitem() -SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole, - 'SELECT', TRUE); -- single privilege - makeaclitem ------------------------------------------- - regress_priv_user1=r*/regress_priv_user2 -(1 row) - -SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole, - 'SELECT, INSERT, UPDATE , DELETE ', FALSE); -- multiple privileges - makeaclitem --------------------------------------------- - regress_priv_user1=arwd/regress_priv_user2 -(1 row) - -SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole, - 'SELECT, fake_privilege', FALSE); -- error -ERROR: unrecognized privilege type: "fake_privilege" --- Test non-throwing aclitem I/O -SELECT pg_input_is_valid('regress_priv_user1=r/regress_priv_user2', 'aclitem'); - pg_input_is_valid -------------------- - t -(1 row) - -SELECT pg_input_is_valid('regress_priv_user1=r/', 'aclitem'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('regress_priv_user1=r/', 'aclitem'); - message | detail | hint | sql_error_code ----------------------------------+--------+------+---------------- - a name must follow the "/" sign | | | 22P02 -(1 row) - -SELECT pg_input_is_valid('regress_priv_user1=r/regress_no_such_user', 'aclitem'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('regress_priv_user1=r/regress_no_such_user', 'aclitem'); - message | detail | hint | sql_error_code ---------------------------------------------+--------+------+---------------- - role "regress_no_such_user" does not exist | | | 42704 -(1 row) - -SELECT pg_input_is_valid('regress_priv_user1=rY', 'aclitem'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('regress_priv_user1=rY', 'aclitem'); - message | detail | hint | sql_error_code ----------------------------------------------------------+--------+------+---------------- - invalid mode character: must be one of "arwdDxtXUCTcsA" | | | 22P02 -(1 row) - --- --- Testing blanket default grants is very hazardous since it might change --- the privileges attached to objects created by concurrent regression tests. --- To avoid that, be sure to revoke the privileges again before committing. --- -BEGIN; -ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2; -CREATE SCHEMA testns2; -SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes - has_schema_privilege ----------------------- - t -(1 row) - -SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes - has_schema_privilege ----------------------- - t -(1 row) - -SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no - has_schema_privilege ----------------------- - f -(1 row) - -ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2; -CREATE SCHEMA testns3; -SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no - has_schema_privilege ----------------------- - f -(1 row) - -SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no - has_schema_privilege ----------------------- - f -(1 row) - -ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; -CREATE SCHEMA testns4; -SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes - has_schema_privilege ----------------------- - t -(1 row) - -SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes - has_schema_privilege ----------------------- - t -(1 row) - -ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2; -COMMIT; --- Test for DROP OWNED BY with shared dependencies. This is done in a --- separate, rollbacked, transaction to avoid any trouble with other --- regression sessions. -BEGIN; -ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2; -ALTER DEFAULT PRIVILEGES GRANT ALL ON TYPES TO regress_priv_user2; -SELECT count(*) FROM pg_shdepend - WHERE deptype = 'a' AND - refobjid = 'regress_priv_user2'::regrole AND - classid = 'pg_default_acl'::regclass; - count -------- - 5 -(1 row) - -DROP OWNED BY regress_priv_user2, regress_priv_user2; -SELECT count(*) FROM pg_shdepend - WHERE deptype = 'a' AND - refobjid = 'regress_priv_user2'::regrole AND - classid = 'pg_default_acl'::regclass; - count -------- - 0 -(1 row) - -ROLLBACK; -CREATE SCHEMA testns5; -SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no - has_schema_privilege ----------------------- - f -(1 row) - -SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no - has_schema_privilege ----------------------- - f -(1 row) - -SET ROLE regress_priv_user1; -CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; -CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); -CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; -SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no - has_function_privilege ------------------------- - f -(1 row) - -SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no - has_function_privilege ------------------------- - f -(1 row) - -SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no - has_function_privilege ------------------------- - f -(1 row) - -ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; -DROP FUNCTION testns.foo(); -CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; -DROP AGGREGATE testns.agg1(int); -CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); -DROP PROCEDURE testns.bar(); -CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; -SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes - has_function_privilege ------------------------- - t -(1 row) - -SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes - has_function_privilege ------------------------- - t -(1 row) - -SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here) - has_function_privilege ------------------------- - t -(1 row) - -DROP FUNCTION testns.foo(); -DROP AGGREGATE testns.agg1(int); -DROP PROCEDURE testns.bar(); -ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public; -CREATE DOMAIN testns.priv_testdomain1 AS int; -SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no - has_type_privilege --------------------- - f -(1 row) - -ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; -DROP DOMAIN testns.priv_testdomain1; -CREATE DOMAIN testns.priv_testdomain1 AS int; -SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes - has_type_privilege --------------------- - t -(1 row) - -DROP DOMAIN testns.priv_testdomain1; -RESET ROLE; -SELECT count(*) - FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid - WHERE nspname = 'testns'; - count -------- - 3 -(1 row) - -DROP SCHEMA testns CASCADE; -NOTICE: drop cascades to table testns.acltest1 -DROP SCHEMA testns2 CASCADE; -DROP SCHEMA testns3 CASCADE; -DROP SCHEMA testns4 CASCADE; -DROP SCHEMA testns5 CASCADE; -SELECT d.* -- check that entries went away - FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid - WHERE nspname IS NULL AND defaclnamespace != 0; - oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl ------+------------+-----------------+---------------+----------- -(0 rows) - --- Grant on all objects of given type in a schema -\c - -CREATE SCHEMA testns; -CREATE TABLE testns.t1 (f1 int); -CREATE TABLE testns.t2 (f1 int); -SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false - has_table_privilege ---------------------- - f -(1 row) - -GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1; -SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true - has_table_privilege ---------------------- - t -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true - has_table_privilege ---------------------- - t -(1 row) - -REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1; -SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false - has_table_privilege ---------------------- - f -(1 row) - -SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false - has_table_privilege ---------------------- - f -(1 row) - -CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; -CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4); -CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql; -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default - has_function_privilege ------------------------- - t -(1 row) - -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default - has_function_privilege ------------------------- - t -(1 row) - -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default - has_function_privilege ------------------------- - t -(1 row) - -REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false - has_function_privilege ------------------------- - f -(1 row) - -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false - has_function_privilege ------------------------- - f -(1 row) - -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function - has_function_privilege ------------------------- - t -(1 row) - -REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false - has_function_privilege ------------------------- - f -(1 row) - -GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true - has_function_privilege ------------------------- - t -(1 row) - -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true - has_function_privilege ------------------------- - t -(1 row) - -SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true - has_function_privilege ------------------------- - t -(1 row) - -DROP SCHEMA testns CASCADE; -NOTICE: drop cascades to 5 other objects -DETAIL: drop cascades to table testns.t1 -drop cascades to table testns.t2 -drop cascades to function testns.priv_testfunc(integer) -drop cascades to function testns.priv_testagg(integer) -drop cascades to function testns.priv_testproc(integer) --- Change owner of the schema & and rename of new schema owner -\c - -CREATE ROLE regress_schemauser1 superuser login; -CREATE ROLE regress_schemauser2 superuser login; -SET SESSION ROLE regress_schemauser1; -CREATE SCHEMA testns; -SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; - nspname | rolname ----------+--------------------- - testns | regress_schemauser1 -(1 row) - -ALTER SCHEMA testns OWNER TO regress_schemauser2; -ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; -SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; - nspname | rolname ----------+---------------------------- - testns | regress_schemauser_renamed -(1 row) - -set session role regress_schemauser_renamed; -DROP SCHEMA testns CASCADE; --- clean up -\c - -DROP ROLE regress_schemauser1; -DROP ROLE regress_schemauser_renamed; --- test that dependent privileges are revoked (or not) properly -\c - -set session role regress_priv_user1; -create table dep_priv_test (a int); -grant select on dep_priv_test to regress_priv_user2 with grant option; -grant select on dep_priv_test to regress_priv_user3 with grant option; -set session role regress_priv_user2; -grant select on dep_priv_test to regress_priv_user4 with grant option; -set session role regress_priv_user3; -grant select on dep_priv_test to regress_priv_user4 with grant option; -set session role regress_priv_user4; -grant select on dep_priv_test to regress_priv_user5; -\dp dep_priv_test - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-----------------------------------------------+-------------------+---------- - public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 +| | - | | | regress_priv_user4=r*/regress_priv_user2 +| | - | | | regress_priv_user4=r*/regress_priv_user3 +| | - | | | regress_priv_user5=r/regress_priv_user4 | | -(1 row) - -set session role regress_priv_user2; -revoke select on dep_priv_test from regress_priv_user4 cascade; -\dp dep_priv_test - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-----------------------------------------------+-------------------+---------- - public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 +| | - | | | regress_priv_user4=r*/regress_priv_user3 +| | - | | | regress_priv_user5=r/regress_priv_user4 | | -(1 row) - -set session role regress_priv_user3; -revoke select on dep_priv_test from regress_priv_user4 cascade; -\dp dep_priv_test - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-----------------------------------------------+-------------------+---------- - public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 | | -(1 row) - -set session role regress_priv_user1; -drop table dep_priv_test; --- clean up -\c -drop sequence x_seq; -DROP AGGREGATE priv_testagg1(int); -DROP FUNCTION priv_testfunc2(int); -DROP FUNCTION priv_testfunc4(boolean); -DROP PROCEDURE priv_testproc1(int); -DROP VIEW atestv0; -DROP VIEW atestv1; -DROP VIEW atestv2; --- this should cascade to drop atestv4 -DROP VIEW atestv3 CASCADE; -NOTICE: drop cascades to view atestv4 --- this should complain "does not exist" -DROP VIEW atestv4; -ERROR: view "atestv4" does not exist -DROP TABLE atest1; -DROP TABLE atest2; -DROP TABLE atest3; -DROP TABLE atest4; -DROP TABLE atest5; -DROP TABLE atest6; -DROP TABLE atestc; -DROP TABLE atestp1; -DROP TABLE atestp2; -SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; - lo_unlink ------------ - 1 - 1 - 1 - 1 - 1 -(5 rows) - -DROP GROUP regress_priv_group1; -DROP GROUP regress_priv_group2; --- these are needed to clean up permissions -REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1; -DROP OWNED BY regress_priv_user1; -DROP USER regress_priv_user1; -DROP USER regress_priv_user2; -DROP USER regress_priv_user3; -DROP USER regress_priv_user4; -DROP USER regress_priv_user5; -DROP USER regress_priv_user6; -DROP USER regress_priv_user7; -DROP USER regress_priv_user8; -- does not exist -ERROR: role "regress_priv_user8" does not exist --- permissions with LOCK TABLE -CREATE USER regress_locktable_user; -CREATE TABLE lock_table (a int); --- LOCK TABLE and SELECT permission -GRANT SELECT ON lock_table TO regress_locktable_user; -SET SESSION AUTHORIZATION regress_locktable_user; -BEGIN; -LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass -COMMIT; -BEGIN; -LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail -ERROR: permission denied for table lock_table -ROLLBACK; -BEGIN; -LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail -ERROR: permission denied for table lock_table -ROLLBACK; -\c -REVOKE SELECT ON lock_table FROM regress_locktable_user; --- LOCK TABLE and INSERT permission -GRANT INSERT ON lock_table TO regress_locktable_user; -SET SESSION AUTHORIZATION regress_locktable_user; -BEGIN; -LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass -ROLLBACK; -BEGIN; -LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass -COMMIT; -BEGIN; -LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail -ERROR: permission denied for table lock_table -ROLLBACK; -\c -REVOKE INSERT ON lock_table FROM regress_locktable_user; --- LOCK TABLE and UPDATE permission -GRANT UPDATE ON lock_table TO regress_locktable_user; -SET SESSION AUTHORIZATION regress_locktable_user; -BEGIN; -LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass -ROLLBACK; -BEGIN; -LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass -COMMIT; -BEGIN; -LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass -COMMIT; -\c -REVOKE UPDATE ON lock_table FROM regress_locktable_user; --- LOCK TABLE and DELETE permission -GRANT DELETE ON lock_table TO regress_locktable_user; -SET SESSION AUTHORIZATION regress_locktable_user; -BEGIN; -LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass -ROLLBACK; -BEGIN; -LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass -COMMIT; -BEGIN; -LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass -COMMIT; -\c -REVOKE DELETE ON lock_table FROM regress_locktable_user; --- LOCK TABLE and TRUNCATE permission -GRANT TRUNCATE ON lock_table TO regress_locktable_user; -SET SESSION AUTHORIZATION regress_locktable_user; -BEGIN; -LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass -ROLLBACK; -BEGIN; -LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass -COMMIT; -BEGIN; -LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass -COMMIT; -\c -REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; --- clean up -DROP TABLE lock_table; -DROP USER regress_locktable_user; --- test to check privileges of system views pg_shmem_allocations and --- pg_backend_memory_contexts. --- switch to superuser -\c - -CREATE ROLE regress_readallstats; -SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- no - has_table_privilege ---------------------- - f -(1 row) - -GRANT pg_read_all_stats TO regress_readallstats; -SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- yes - has_table_privilege ---------------------- - t -(1 row) - -SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- yes - has_table_privilege ---------------------- - t -(1 row) - --- run query to ensure that functions within views can be executed -SET ROLE regress_readallstats; -SELECT COUNT(*) >= 0 AS ok FROM pg_backend_memory_contexts; - ok ----- - t -(1 row) - -SELECT COUNT(*) >= 0 AS ok FROM pg_shmem_allocations; - ok ----- - t -(1 row) - -RESET ROLE; --- clean up -DROP ROLE regress_readallstats; --- test role grantor machinery -CREATE ROLE regress_group; -CREATE ROLE regress_group_direct_manager; -CREATE ROLE regress_group_indirect_manager; -CREATE ROLE regress_group_member; -GRANT regress_group TO regress_group_direct_manager WITH INHERIT FALSE, ADMIN TRUE; -GRANT regress_group_direct_manager TO regress_group_indirect_manager; -SET SESSION AUTHORIZATION regress_group_direct_manager; -GRANT regress_group TO regress_group_member; -SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE grantor::regrole::text END FROM pg_auth_members WHERE roleid = 'regress_group'::regrole ORDER BY 1, 2; - member | grantor -------------------------------+------------------------------ - regress_group_direct_manager | BOOTSTRAP SUPERUSER - regress_group_member | regress_group_direct_manager -(2 rows) - -REVOKE regress_group FROM regress_group_member; -SET SESSION AUTHORIZATION regress_group_indirect_manager; -GRANT regress_group TO regress_group_member; -SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE grantor::regrole::text END FROM pg_auth_members WHERE roleid = 'regress_group'::regrole ORDER BY 1, 2; - member | grantor -------------------------------+------------------------------ - regress_group_direct_manager | BOOTSTRAP SUPERUSER - regress_group_member | regress_group_direct_manager -(2 rows) - -REVOKE regress_group FROM regress_group_member; -RESET SESSION AUTHORIZATION; -DROP ROLE regress_group; -DROP ROLE regress_group_direct_manager; -DROP ROLE regress_group_indirect_manager; -DROP ROLE regress_group_member; --- test SET and INHERIT options with object ownership changes -CREATE ROLE regress_roleoption_protagonist; -CREATE ROLE regress_roleoption_donor; -CREATE ROLE regress_roleoption_recipient; -CREATE SCHEMA regress_roleoption; -GRANT CREATE, USAGE ON SCHEMA regress_roleoption TO PUBLIC; -GRANT regress_roleoption_donor TO regress_roleoption_protagonist WITH INHERIT TRUE, SET FALSE; -GRANT regress_roleoption_recipient TO regress_roleoption_protagonist WITH INHERIT FALSE, SET TRUE; -SET SESSION AUTHORIZATION regress_roleoption_protagonist; -CREATE TABLE regress_roleoption.t1 (a int); -CREATE TABLE regress_roleoption.t2 (a int); -SET SESSION AUTHORIZATION regress_roleoption_donor; -CREATE TABLE regress_roleoption.t3 (a int); -SET SESSION AUTHORIZATION regress_roleoption_recipient; -CREATE TABLE regress_roleoption.t4 (a int); -SET SESSION AUTHORIZATION regress_roleoption_protagonist; -ALTER TABLE regress_roleoption.t1 OWNER TO regress_roleoption_donor; -- fails, can't be come donor -ERROR: must be able to SET ROLE "regress_roleoption_donor" -ALTER TABLE regress_roleoption.t2 OWNER TO regress_roleoption_recipient; -- works -ALTER TABLE regress_roleoption.t3 OWNER TO regress_roleoption_protagonist; -- works -ALTER TABLE regress_roleoption.t4 OWNER TO regress_roleoption_protagonist; -- fails, we don't inherit from recipient -ERROR: must be owner of table t4 -RESET SESSION AUTHORIZATION; -DROP TABLE regress_roleoption.t1; -DROP TABLE regress_roleoption.t2; -DROP TABLE regress_roleoption.t3; -DROP TABLE regress_roleoption.t4; -DROP SCHEMA regress_roleoption; -DROP ROLE regress_roleoption_protagonist; -DROP ROLE regress_roleoption_donor; -DROP ROLE regress_roleoption_recipient; +WARNING: terminating connection because of crash of another server process +DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. +HINT: In a moment you should be able to reconnect to the database and repeat your command. +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -w -U3 C:/cirrus/src/test/regress/expected/groupingsets.out C:/cirrus/build/testrun/regress/regress/results/groupingsets.out --- C:/cirrus/src/test/regress/expected/groupingsets.out 2024-03-11 18:11:56.467531400 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/groupingsets.out 2024-03-11 18:14:46.860022100 +0000 @@ -34,2234 +34,7 @@ -- (and with ordering differing from grouping) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by rollup (a,b); - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | 1 | 0 | 21 | 2 | 11 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 3 | 0 | 14 | 1 | 14 - 1 | | 1 | 60 | 5 | 14 - 2 | 3 | 0 | 15 | 1 | 15 - 2 | | 1 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 3 | 4 | 0 | 17 | 1 | 17 - 3 | | 1 | 33 | 2 | 17 - 4 | 1 | 0 | 37 | 2 | 19 - 4 | | 1 | 37 | 2 | 19 - | | 3 | 145 | 10 | 19 -(12 rows) - -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by rollup (a,b) order by a,b; - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | 1 | 0 | 21 | 2 | 11 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 3 | 0 | 14 | 1 | 14 - 1 | | 1 | 60 | 5 | 14 - 2 | 3 | 0 | 15 | 1 | 15 - 2 | | 1 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 3 | 4 | 0 | 17 | 1 | 17 - 3 | | 1 | 33 | 2 | 17 - 4 | 1 | 0 | 37 | 2 | 19 - 4 | | 1 | 37 | 2 | 19 - | | 3 | 145 | 10 | 19 -(12 rows) - -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by rollup (a,b) order by b desc, a; - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | | 1 | 60 | 5 | 14 - 2 | | 1 | 15 | 1 | 15 - 3 | | 1 | 33 | 2 | 17 - 4 | | 1 | 37 | 2 | 19 - | | 3 | 145 | 10 | 19 - 3 | 4 | 0 | 17 | 1 | 17 - 1 | 3 | 0 | 14 | 1 | 14 - 2 | 3 | 0 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 1 | 0 | 21 | 2 | 11 - 4 | 1 | 0 | 37 | 2 | 19 -(12 rows) - -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by rollup (a,b) order by coalesce(a,0)+coalesce(b,0); - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - | | 3 | 145 | 10 | 19 - 1 | | 1 | 60 | 5 | 14 - 1 | 1 | 0 | 21 | 2 | 11 - 2 | | 1 | 15 | 1 | 15 - 3 | | 1 | 33 | 2 | 17 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 3 | 0 | 14 | 1 | 14 - 4 | | 1 | 37 | 2 | 19 - 4 | 1 | 0 | 37 | 2 | 19 - 2 | 3 | 0 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 3 | 4 | 0 | 17 | 1 | 17 -(12 rows) - --- various types of ordered aggs -select a, b, grouping(a,b), - array_agg(v order by v), - string_agg(v::text, ':' order by v desc), - percentile_disc(0.5) within group (order by v), - rank(1,2,12) within group (order by a,b,v) - from gstest1 group by rollup (a,b) order by a,b; - a | b | grouping | array_agg | string_agg | percentile_disc | rank ----+---+----------+---------------------------------+-------------------------------+-----------------+------ - 1 | 1 | 0 | {10,11} | 11:10 | 10 | 3 - 1 | 2 | 0 | {12,13} | 13:12 | 12 | 1 - 1 | 3 | 0 | {14} | 14 | 14 | 1 - 1 | | 1 | {10,11,12,13,14} | 14:13:12:11:10 | 12 | 3 - 2 | 3 | 0 | {15} | 15 | 15 | 1 - 2 | | 1 | {15} | 15 | 15 | 1 - 3 | 3 | 0 | {16} | 16 | 16 | 1 - 3 | 4 | 0 | {17} | 17 | 17 | 1 - 3 | | 1 | {16,17} | 17:16 | 16 | 1 - 4 | 1 | 0 | {18,19} | 19:18 | 18 | 1 - 4 | | 1 | {18,19} | 19:18 | 18 | 1 - | | 3 | {10,11,12,13,14,15,16,17,18,19} | 19:18:17:16:15:14:13:12:11:10 | 14 | 3 -(12 rows) - --- test usage of grouped columns in direct args of aggs -select grouping(a), a, array_agg(b), - rank(a) within group (order by b nulls first), - rank(a) within group (order by b nulls last) - from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) - group by rollup (a) order by a; - grouping | a | array_agg | rank | rank -----------+---+-------------+------+------ - 0 | 1 | {1,4,5} | 1 | 1 - 0 | 3 | {1,2} | 3 | 3 - 1 | | {1,4,5,1,2} | 1 | 6 -(3 rows) - --- nesting with window functions -select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum - from gstest2 group by rollup (a,b) order by rsum, a, b; - a | b | sum | rsum ----+---+-----+------ - 1 | 1 | 8 | 8 - 1 | 2 | 2 | 10 - 1 | | 10 | 20 - 2 | 2 | 2 | 22 - 2 | | 2 | 24 - | | 12 | 36 -(6 rows) - --- nesting with grouping sets -select sum(c) from gstest2 - group by grouping sets((), grouping sets((), grouping sets(()))) - order by 1 desc; - sum ------ - 12 - 12 - 12 -(3 rows) - -select sum(c) from gstest2 - group by grouping sets((), grouping sets((), grouping sets(((a, b))))) - order by 1 desc; - sum ------ - 12 - 12 - 8 - 2 - 2 -(5 rows) - -select sum(c) from gstest2 - group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) - order by 1 desc; - sum ------ - 12 - 12 - 6 - 6 - 6 - 6 -(6 rows) - -select sum(c) from gstest2 - group by grouping sets(a, grouping sets(a, cube(b))) - order by 1 desc; - sum ------ - 12 - 10 - 10 - 8 - 4 - 2 - 2 -(7 rows) - -select sum(c) from gstest2 - group by grouping sets(grouping sets((a, (b)))) - order by 1 desc; - sum ------ - 8 - 2 - 2 -(3 rows) - -select sum(c) from gstest2 - group by grouping sets(grouping sets((a, b))) - order by 1 desc; - sum ------ - 8 - 2 - 2 -(3 rows) - -select sum(c) from gstest2 - group by grouping sets(grouping sets(a, grouping sets(a), a)) - order by 1 desc; - sum ------ - 10 - 10 - 10 - 2 - 2 - 2 -(6 rows) - -select sum(c) from gstest2 - group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) - order by 1 desc; - sum ------ - 10 - 10 - 10 - 10 - 10 - 10 - 10 - 10 - 2 - 2 - 2 - 2 - 2 - 2 - 2 - 2 -(16 rows) - -select sum(c) from gstest2 - group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) - order by 1 desc; - sum ------ - 10 - 8 - 8 - 2 - 2 - 2 - 2 - 2 -(8 rows) - --- empty input: first is 0 rows, second 1, third 3 etc. -select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); - a | b | sum | count ----+---+-----+------- -(0 rows) - -select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); - a | b | sum | count ----+---+-----+------- - | | | 0 -(1 row) - -select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); - a | b | sum | count ----+---+-----+------- - | | | 0 - | | | 0 - | | | 0 -(3 rows) - -select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); - sum | count ------+------- - | 0 - | 0 - | 0 -(3 rows) - --- empty input with joins tests some important code paths -select t1.a, t2.b, sum(t1.v), count(*) from gstest_empty t1, gstest_empty t2 - group by grouping sets ((t1.a,t2.b),()); - a | b | sum | count ----+---+-----+------- - | | | 0 -(1 row) - --- simple joins, var resolution, GROUPING on join vars -select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) - from gstest1 t1, gstest2 t2 - group by grouping sets ((t1.a, t2.b), ()); - a | b | grouping | sum | max ----+---+----------+------+----- - 1 | 1 | 0 | 420 | 1 - 1 | 2 | 0 | 120 | 2 - 2 | 1 | 0 | 105 | 1 - 2 | 2 | 0 | 30 | 2 - 3 | 1 | 0 | 231 | 1 - 3 | 2 | 0 | 66 | 2 - 4 | 1 | 0 | 259 | 1 - 4 | 2 | 0 | 74 | 2 - | | 3 | 1305 | 2 -(9 rows) - -select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) - from gstest1 t1 join gstest2 t2 on (t1.a=t2.a) - group by grouping sets ((t1.a, t2.b), ()); - a | b | grouping | sum | max ----+---+----------+-----+----- - 1 | 1 | 0 | 420 | 1 - 1 | 2 | 0 | 60 | 1 - 2 | 2 | 0 | 15 | 2 - | | 3 | 495 | 2 -(4 rows) - -select a, b, grouping(a, b), sum(t1.v), max(t2.c) - from gstest1 t1 join gstest2 t2 using (a,b) - group by grouping sets ((a, b), ()); - a | b | grouping | sum | max ----+---+----------+-----+----- - 1 | 1 | 0 | 147 | 2 - 1 | 2 | 0 | 25 | 2 - | | 3 | 172 | 2 -(3 rows) - --- check that functionally dependent cols are not nulled -select a, d, grouping(a,b,c) - from gstest3 - group by grouping sets ((a,b), (a,c)); - a | d | grouping ----+---+---------- - 1 | 1 | 1 - 2 | 2 | 1 - 1 | 1 | 2 - 2 | 2 | 2 -(4 rows) - --- check that distinct grouping columns are kept separate --- even if they are equal() -explain (costs off) -select g as alias1, g as alias2 - from generate_series(1,3) g - group by alias1, rollup(alias2); - QUERY PLAN ------------------------------------------------- - GroupAggregate - Group Key: g, g - Group Key: g - -> Sort - Sort Key: g - -> Function Scan on generate_series g -(6 rows) - -select g as alias1, g as alias2 - from generate_series(1,3) g - group by alias1, rollup(alias2); - alias1 | alias2 ---------+-------- - 1 | 1 - 1 | - 2 | 2 - 2 | - 3 | 3 - 3 | -(6 rows) - --- check that pulled-up subquery outputs still go to null when appropriate -select four, x - from (select four, ten, 'foo'::text as x from tenk1) as t - group by grouping sets (four, x) - having x = 'foo'; - four | x -------+----- - | foo -(1 row) - -select four, x || 'x' - from (select four, ten, 'foo'::text as x from tenk1) as t - group by grouping sets (four, x) - order by four; - four | ?column? -------+---------- - 0 | - 1 | - 2 | - 3 | - | foox -(5 rows) - -select (x+y)*1, sum(z) - from (select 1 as x, 2 as y, 3 as z) s - group by grouping sets (x+y, x); - ?column? | sum -----------+----- - 3 | 3 - | 3 -(2 rows) - -select x, not x as not_x, q2 from - (select *, q1 = 1 as x from int8_tbl i1) as t - group by grouping sets(x, q2) - order by x, q2; - x | not_x | q2 ----+-------+------------------- - f | t | - | | -4567890123456789 - | | 123 - | | 456 - | | 4567890123456789 -(5 rows) - --- check qual push-down rules for a subquery with grouping sets -explain (verbose, costs off) -select * from ( - select 1 as x, q1, sum(q2) - from int8_tbl i1 - group by grouping sets(1, 2) -) ss -where x = 1 and q1 = 123; - QUERY PLAN --------------------------------------------------- - Subquery Scan on ss - Output: ss.x, ss.q1, ss.sum - Filter: ((ss.x = 1) AND (ss.q1 = 123)) - -> GroupAggregate - Output: (1), i1.q1, sum(i1.q2) - Group Key: (1) - Sort Key: i1.q1 - Group Key: i1.q1 - -> Sort - Output: (1), i1.q1, i1.q2 - Sort Key: (1) - -> Seq Scan on public.int8_tbl i1 - Output: 1, i1.q1, i1.q2 -(13 rows) - -select * from ( - select 1 as x, q1, sum(q2) - from int8_tbl i1 - group by grouping sets(1, 2) -) ss -where x = 1 and q1 = 123; - x | q1 | sum ----+----+----- -(0 rows) - --- check handling of pulled-up SubPlan in GROUPING() argument (bug #17479) -explain (verbose, costs off) -select grouping(ss.x) -from int8_tbl i1 -cross join lateral (select (select i1.q1) as x) ss -group by ss.x; - QUERY PLAN ------------------------------------------------- - GroupAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 2)) - Group Key: ((SubPlan 2)) - -> Sort - Output: ((SubPlan 2)), i1.q1 - Sort Key: ((SubPlan 2)) - -> Seq Scan on public.int8_tbl i1 - Output: (SubPlan 2), i1.q1 - SubPlan 2 - -> Result - Output: i1.q1 -(11 rows) - -select grouping(ss.x) -from int8_tbl i1 -cross join lateral (select (select i1.q1) as x) ss -group by ss.x; - grouping ----------- - 0 - 0 -(2 rows) - -explain (verbose, costs off) -select (select grouping(ss.x)) -from int8_tbl i1 -cross join lateral (select (select i1.q1) as x) ss -group by ss.x; - QUERY PLAN --------------------------------------------- - GroupAggregate - Output: (SubPlan 2), ((SubPlan 3)) - Group Key: ((SubPlan 3)) - -> Sort - Output: ((SubPlan 3)), i1.q1 - Sort Key: ((SubPlan 3)) - -> Seq Scan on public.int8_tbl i1 - Output: (SubPlan 3), i1.q1 - SubPlan 3 - -> Result - Output: i1.q1 - SubPlan 2 - -> Result - Output: GROUPING((SubPlan 1)) -(14 rows) - -select (select grouping(ss.x)) -from int8_tbl i1 -cross join lateral (select (select i1.q1) as x) ss -group by ss.x; - grouping ----------- - 0 - 0 -(2 rows) - --- simple rescan tests -select a, b, sum(v.x) - from (values (1),(2)) v(x), gstest_data(v.x) - group by rollup (a,b); - a | b | sum ----+---+----- - 1 | 1 | 1 - 1 | 2 | 1 - 1 | 3 | 1 - 1 | | 3 - 2 | 1 | 2 - 2 | 2 | 2 - 2 | 3 | 2 - 2 | | 6 - | | 9 -(9 rows) - -select * - from (values (1),(2)) v(x), - lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s; -ERROR: aggregate functions are not allowed in FROM clause of their own query level -LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ... - ^ --- min max optimization should still work with GROUP BY () -explain (costs off) - select min(unique1) from tenk1 GROUP BY (); - QUERY PLAN ------------------------------------------------------------- - Result - InitPlan 1 (returns $0) - -> Limit - -> Index Only Scan using tenk1_unique1 on tenk1 - Index Cond: (unique1 IS NOT NULL) -(5 rows) - --- Views with GROUPING SET queries -CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) - from gstest2 group by rollup ((a,b,c),(c,d)); -NOTICE: view "gstest_view" will be a temporary view -select pg_get_viewdef('gstest_view'::regclass, true); - pg_get_viewdef ---------------------------------------- - SELECT a, + - b, + - GROUPING(a, b) AS "grouping", + - sum(c) AS sum, + - count(*) AS count, + - max(c) AS max + - FROM gstest2 + - GROUP BY ROLLUP((a, b, c), (c, d)); -(1 row) - --- Nested queries with 3 or more levels of nesting -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 ----------- - 0 - 1 - 3 -(3 rows) - -select(select (select grouping(c) from (values (1)) v2(c) GROUP BY 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) - --- Combinations of operations -select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d); - a | b | c | d ----+---+---+--- - 1 | 1 | 1 | - 1 | | 1 | - | | 1 | - 1 | 1 | 2 | - 1 | 2 | 2 | - 1 | | 2 | - 2 | 2 | 2 | - 2 | | 2 | - | | 2 | - 1 | 1 | | 1 - 1 | | | 1 - | | | 1 - 1 | 1 | | 2 - 1 | 2 | | 2 - 1 | | | 2 - 2 | 2 | | 2 - 2 | | | 2 - | | | 2 -(18 rows) - -select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a); - a | b ----+--- - 1 | 2 - 2 | 3 -(2 rows) - --- Tests for chained aggregates -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | 1 | 0 | 21 | 2 | 11 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 3 | 0 | 14 | 1 | 14 - 2 | 3 | 0 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 3 | 4 | 0 | 17 | 1 | 17 - 4 | 1 | 0 | 37 | 2 | 19 - | | 3 | 21 | 2 | 11 - | | 3 | 21 | 2 | 11 - | | 3 | 25 | 2 | 13 - | | 3 | 25 | 2 | 13 - | | 3 | 14 | 1 | 14 - | | 3 | 14 | 1 | 14 - | | 3 | 15 | 1 | 15 - | | 3 | 15 | 1 | 15 - | | 3 | 16 | 1 | 16 - | | 3 | 16 | 1 | 16 - | | 3 | 17 | 1 | 17 - | | 3 | 17 | 1 | 17 - | | 3 | 37 | 2 | 19 - | | 3 | 37 | 2 | 19 -(21 rows) - -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+1),(f+1)); - grouping ----------- - 0 - 0 - 0 -(3 rows) - -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 CUBE((e+1),(f+1)) ORDER BY (e+1),(f+1); - grouping ----------- - 0 - 0 - 0 - 0 -(4 rows) - -select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum - from gstest2 group by cube (a,b) order by rsum, a, b; - a | b | sum | rsum ----+---+-----+------ - 1 | 1 | 8 | 8 - 1 | 2 | 2 | 10 - 1 | | 10 | 20 - 2 | 2 | 2 | 22 - 2 | | 2 | 24 - | 1 | 8 | 32 - | 2 | 4 | 36 - | | 12 | 48 -(8 rows) - -select a, b, sum(c) from (values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14),(2,3,15),(3,3,16),(3,4,17),(4,1,18),(4,1,19)) v(a,b,c) group by rollup (a,b); - a | b | sum ----+---+----- - 1 | 1 | 21 - 1 | 2 | 25 - 1 | 3 | 14 - 1 | | 60 - 2 | 3 | 15 - 2 | | 15 - 3 | 3 | 16 - 3 | 4 | 17 - 3 | | 33 - 4 | 1 | 37 - 4 | | 37 - | | 145 -(12 rows) - -select a, b, sum(v.x) - from (values (1),(2)) v(x), gstest_data(v.x) - group by cube (a,b) order by a,b; - a | b | sum ----+---+----- - 1 | 1 | 1 - 1 | 2 | 1 - 1 | 3 | 1 - 1 | | 3 - 2 | 1 | 2 - 2 | 2 | 2 - 2 | 3 | 2 - 2 | | 6 - | 1 | 3 - | 2 | 3 - | 3 | 3 - | | 9 -(12 rows) - --- Test reordering of grouping sets -explain (costs off) -select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; - QUERY PLAN ------------------------------------------------------------------------------------- - Incremental Sort - Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 - Presorted Key: "*VALUES*".column3 - -> GroupAggregate - Group Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 - Group Key: "*VALUES*".column3 - -> Sort - Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 - -> Values Scan on "*VALUES*" -(9 rows) - --- Agg level check. This query should error out. -select (select grouping(a,b) from gstest2) from gstest2 group by a,b; -ERROR: arguments to GROUPING must be grouping expressions of the associated query level -LINE 1: select (select grouping(a,b) from gstest2) from gstest2 grou... - ^ ---Nested queries -select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a); - a | b | sum | count ----+---+-----+------- - 1 | 1 | 8 | 7 - 1 | 2 | 2 | 1 - 1 | | 10 | 8 - 1 | | 10 | 8 - 2 | 2 | 2 | 1 - 2 | | 2 | 1 - 2 | | 2 | 1 - | | 12 | 9 -(8 rows) - --- HAVING queries -select ten, sum(distinct four) from onek a -group by grouping sets((ten,four),(ten)) -having exists (select 1 from onek b where sum(distinct a.four) = b.four); - ten | sum ------+----- - 0 | 0 - 0 | 2 - 0 | 2 - 1 | 1 - 1 | 3 - 2 | 0 - 2 | 2 - 2 | 2 - 3 | 1 - 3 | 3 - 4 | 0 - 4 | 2 - 4 | 2 - 5 | 1 - 5 | 3 - 6 | 0 - 6 | 2 - 6 | 2 - 7 | 1 - 7 | 3 - 8 | 0 - 8 | 2 - 8 | 2 - 9 | 1 - 9 | 3 -(25 rows) - --- Tests around pushdown of HAVING clauses, partially testing against previous bugs -select a,count(*) from gstest2 group by rollup(a) order by a; - a | count ----+------- - 1 | 8 - 2 | 1 - | 9 -(3 rows) - -select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; - a | count ----+------- - 2 | 1 - | 9 -(2 rows) - -explain (costs off) - select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; - QUERY PLAN ----------------------------------------- - Sort - Sort Key: a - -> GroupAggregate - Group Key: a - Group Key: () - Filter: (a IS DISTINCT FROM 1) - -> Sort - Sort Key: a - -> Seq Scan on gstest2 -(9 rows) - -select v.c, (select count(*) from gstest2 group by () having v.c) - from (values (false),(true)) v(c) order by v.c; - c | count ----+------- - f | - t | 9 -(2 rows) - -explain (costs off) - select v.c, (select count(*) from gstest2 group by () having v.c) - from (values (false),(true)) v(c) order by v.c; - QUERY PLAN ------------------------------------------------------------ - Sort - Sort Key: "*VALUES*".column1 - -> Values Scan on "*VALUES*" - SubPlan 1 - -> Aggregate - Group Key: () - Filter: "*VALUES*".column1 - -> Result - One-Time Filter: "*VALUES*".column1 - -> Seq Scan on gstest2 -(10 rows) - --- HAVING with GROUPING queries -select ten, grouping(ten) from onek -group by grouping sets(ten) having grouping(ten) >= 0 -order by 2,1; - ten | grouping ------+---------- - 0 | 0 - 1 | 0 - 2 | 0 - 3 | 0 - 4 | 0 - 5 | 0 - 6 | 0 - 7 | 0 - 8 | 0 - 9 | 0 -(10 rows) - -select ten, grouping(ten) from onek -group by grouping sets(ten, four) having grouping(ten) > 0 -order by 2,1; - ten | grouping ------+---------- - | 1 - | 1 - | 1 - | 1 -(4 rows) - -select ten, grouping(ten) from onek -group by rollup(ten) having grouping(ten) > 0 -order by 2,1; - ten | grouping ------+---------- - | 1 -(1 row) - -select ten, grouping(ten) from onek -group by cube(ten) having grouping(ten) > 0 -order by 2,1; - ten | grouping ------+---------- - | 1 -(1 row) - -select ten, grouping(ten) from onek -group by (ten) having grouping(ten) >= 0 -order by 2,1; - ten | grouping ------+---------- - 0 | 0 - 1 | 0 - 2 | 0 - 3 | 0 - 4 | 0 - 5 | 0 - 6 | 0 - 7 | 0 - 8 | 0 - 9 | 0 -(10 rows) - --- FILTER queries -select ten, sum(distinct four) filter (where four::text ~ '123') from onek a -group by rollup(ten); - ten | sum ------+----- - 0 | - 1 | - 2 | - 3 | - 4 | - 5 | - 6 | - 7 | - 8 | - 9 | - | -(11 rows) - --- More rescan tests -select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten; - a | a | four | ten | count ----+---+------+-----+------- - 1 | 1 | 0 | 0 | 50 - 1 | 1 | 0 | 2 | 50 - 1 | 1 | 0 | 4 | 50 - 1 | 1 | 0 | 6 | 50 - 1 | 1 | 0 | 8 | 50 - 1 | 1 | 0 | | 250 - 1 | 1 | 1 | 1 | 50 - 1 | 1 | 1 | 3 | 50 - 1 | 1 | 1 | 5 | 50 - 1 | 1 | 1 | 7 | 50 - 1 | 1 | 1 | 9 | 50 - 1 | 1 | 1 | | 250 - 1 | 1 | 2 | 0 | 50 - 1 | 1 | 2 | 2 | 50 - 1 | 1 | 2 | 4 | 50 - 1 | 1 | 2 | 6 | 50 - 1 | 1 | 2 | 8 | 50 - 1 | 1 | 2 | | 250 - 1 | 1 | 3 | 1 | 50 - 1 | 1 | 3 | 3 | 50 - 1 | 1 | 3 | 5 | 50 - 1 | 1 | 3 | 7 | 50 - 1 | 1 | 3 | 9 | 50 - 1 | 1 | 3 | | 250 - 1 | 1 | | 0 | 100 - 1 | 1 | | 1 | 100 - 1 | 1 | | 2 | 100 - 1 | 1 | | 3 | 100 - 1 | 1 | | 4 | 100 - 1 | 1 | | 5 | 100 - 1 | 1 | | 6 | 100 - 1 | 1 | | 7 | 100 - 1 | 1 | | 8 | 100 - 1 | 1 | | 9 | 100 - 1 | 1 | | | 1000 - 2 | 2 | 0 | 0 | 50 - 2 | 2 | 0 | 2 | 50 - 2 | 2 | 0 | 4 | 50 - 2 | 2 | 0 | 6 | 50 - 2 | 2 | 0 | 8 | 50 - 2 | 2 | 0 | | 250 - 2 | 2 | 1 | 1 | 50 - 2 | 2 | 1 | 3 | 50 - 2 | 2 | 1 | 5 | 50 - 2 | 2 | 1 | 7 | 50 - 2 | 2 | 1 | 9 | 50 - 2 | 2 | 1 | | 250 - 2 | 2 | 2 | 0 | 50 - 2 | 2 | 2 | 2 | 50 - 2 | 2 | 2 | 4 | 50 - 2 | 2 | 2 | 6 | 50 - 2 | 2 | 2 | 8 | 50 - 2 | 2 | 2 | | 250 - 2 | 2 | 3 | 1 | 50 - 2 | 2 | 3 | 3 | 50 - 2 | 2 | 3 | 5 | 50 - 2 | 2 | 3 | 7 | 50 - 2 | 2 | 3 | 9 | 50 - 2 | 2 | 3 | | 250 - 2 | 2 | | 0 | 100 - 2 | 2 | | 1 | 100 - 2 | 2 | | 2 | 100 - 2 | 2 | | 3 | 100 - 2 | 2 | | 4 | 100 - 2 | 2 | | 5 | 100 - 2 | 2 | | 6 | 100 - 2 | 2 | | 7 | 100 - 2 | 2 | | 8 | 100 - 2 | 2 | | 9 | 100 - 2 | 2 | | | 1000 -(70 rows) - -select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by cube(two,four) order by two,four) s1) from (values (1),(2)) v(a); - array ------------------------------------------------------------------------------------------------------------------------------------------------------- - {"(1,0,0,250)","(1,0,2,250)","(1,0,,500)","(1,1,1,250)","(1,1,3,250)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)","(1,,,1000)"} - {"(2,0,0,250)","(2,0,2,250)","(2,0,,500)","(2,1,1,250)","(2,1,3,250)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)","(2,,,1000)"} -(2 rows) - --- Grouping on text columns -select sum(ten) from onek group by two, rollup(four::text) order by 1; - sum ------- - 1000 - 1000 - 1250 - 1250 - 2000 - 2500 -(6 rows) - -select sum(ten) from onek group by rollup(four::text), two order by 1; - sum ------- - 1000 - 1000 - 1250 - 1250 - 2000 - 2500 -(6 rows) - --- hashing support -set enable_hashagg = true; --- failure cases -select count(*) from gstest4 group by rollup(unhashable_col,unsortable_col); -ERROR: could not implement GROUP BY -DETAIL: Some of the datatypes only support hashing, while others only support sorting. -select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortable_col),(id)); -ERROR: could not implement GROUP BY -DETAIL: Some of the datatypes only support hashing, while others only support sorting. --- simple cases -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | | 1 | 60 | 5 | 14 - 2 | | 1 | 15 | 1 | 15 - 3 | | 1 | 33 | 2 | 17 - 4 | | 1 | 37 | 2 | 19 - | 1 | 2 | 58 | 4 | 19 - | 2 | 2 | 25 | 2 | 13 - | 3 | 2 | 45 | 3 | 16 - | 4 | 2 | 17 | 1 | 17 -(8 rows) - -explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; - QUERY PLAN --------------------------------------------------------------------------------------------------------- - Sort - Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 - -> HashAggregate - Hash Key: "*VALUES*".column1 - Hash Key: "*VALUES*".column2 - -> Values Scan on "*VALUES*" -(6 rows) - -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by cube(a,b) order by 3,1,2; - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | 1 | 0 | 21 | 2 | 11 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 3 | 0 | 14 | 1 | 14 - 2 | 3 | 0 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 3 | 4 | 0 | 17 | 1 | 17 - 4 | 1 | 0 | 37 | 2 | 19 - 1 | | 1 | 60 | 5 | 14 - 2 | | 1 | 15 | 1 | 15 - 3 | | 1 | 33 | 2 | 17 - 4 | | 1 | 37 | 2 | 19 - | 1 | 2 | 58 | 4 | 19 - | 2 | 2 | 25 | 2 | 13 - | 3 | 2 | 45 | 3 | 16 - | 4 | 2 | 17 | 1 | 17 - | | 3 | 145 | 10 | 19 -(16 rows) - -explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by cube(a,b) order by 3,1,2; - QUERY PLAN --------------------------------------------------------------------------------------------------------- - Sort - Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 - -> MixedAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: "*VALUES*".column1 - Hash Key: "*VALUES*".column2 - Group Key: () - -> Values Scan on "*VALUES*" -(8 rows) - --- shouldn't try and hash -explain (costs off) - select a, b, grouping(a,b), array_agg(v order by v) - from gstest1 group by cube(a,b); - QUERY PLAN ----------------------------------------------------------- - GroupAggregate - Group Key: "*VALUES*".column1, "*VALUES*".column2 - Group Key: "*VALUES*".column1 - Group Key: () - Sort Key: "*VALUES*".column2 - Group Key: "*VALUES*".column2 - -> Sort - Sort Key: "*VALUES*".column1, "*VALUES*".column2 - -> Values Scan on "*VALUES*" -(9 rows) - --- unsortable cases -select unsortable_col, count(*) - from gstest4 group by grouping sets ((unsortable_col),(unsortable_col)) - order by unsortable_col::text; - unsortable_col | count -----------------+------- - 1 | 4 - 1 | 4 - 2 | 4 - 2 | 4 -(4 rows) - --- mixed hashable/sortable cases -select unhashable_col, unsortable_col, - grouping(unhashable_col, unsortable_col), - count(*), sum(v) - from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) - order by 3, 5; - unhashable_col | unsortable_col | grouping | count | sum -----------------+----------------+----------+-------+----- - 0000 | | 1 | 2 | 17 - 0001 | | 1 | 2 | 34 - 0010 | | 1 | 2 | 68 - 0011 | | 1 | 2 | 136 - | 2 | 2 | 4 | 60 - | 1 | 2 | 4 | 195 -(6 rows) - -explain (costs off) - select unhashable_col, unsortable_col, - grouping(unhashable_col, unsortable_col), - count(*), sum(v) - from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) - order by 3,5; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) - -> MixedAggregate - Hash Key: unsortable_col - Group Key: unhashable_col - -> Sort - Sort Key: unhashable_col - -> Seq Scan on gstest4 -(8 rows) - -select unhashable_col, unsortable_col, - grouping(unhashable_col, unsortable_col), - count(*), sum(v) - from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) - order by 3,5; - unhashable_col | unsortable_col | grouping | count | sum -----------------+----------------+----------+-------+----- - 0000 | | 1 | 1 | 1 - 0001 | | 1 | 1 | 2 - 0010 | | 1 | 1 | 4 - 0011 | | 1 | 1 | 8 - 0000 | | 1 | 1 | 16 - 0001 | | 1 | 1 | 32 - 0010 | | 1 | 1 | 64 - 0011 | | 1 | 1 | 128 - | 1 | 2 | 1 | 1 - | 1 | 2 | 1 | 2 - | 2 | 2 | 1 | 4 - | 2 | 2 | 1 | 8 - | 2 | 2 | 1 | 16 - | 2 | 2 | 1 | 32 - | 1 | 2 | 1 | 64 - | 1 | 2 | 1 | 128 -(16 rows) - -explain (costs off) - select unhashable_col, unsortable_col, - grouping(unhashable_col, unsortable_col), - count(*), sum(v) - from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) - order by 3,5; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) - -> MixedAggregate - Hash Key: v, unsortable_col - Group Key: v, unhashable_col - -> Sort - Sort Key: v, unhashable_col - -> Seq Scan on gstest4 -(8 rows) - --- empty input: first is 0 rows, second 1, third 3 etc. -select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); - a | b | sum | count ----+---+-----+------- -(0 rows) - -explain (costs off) - select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); - QUERY PLAN --------------------------------- - HashAggregate - Hash Key: a, b - Hash Key: a - -> Seq Scan on gstest_empty -(4 rows) - -select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); - a | b | sum | count ----+---+-----+------- - | | | 0 -(1 row) - -select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); - a | b | sum | count ----+---+-----+------- - | | | 0 - | | | 0 - | | | 0 -(3 rows) - -explain (costs off) - select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); - QUERY PLAN --------------------------------- - MixedAggregate - Hash Key: a, b - Group Key: () - Group Key: () - Group Key: () - -> Seq Scan on gstest_empty -(6 rows) - -select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); - sum | count ------+------- - | 0 - | 0 - | 0 -(3 rows) - -explain (costs off) - select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); - QUERY PLAN --------------------------------- - Aggregate - Group Key: () - Group Key: () - Group Key: () - -> Seq Scan on gstest_empty -(5 rows) - --- check that functionally dependent cols are not nulled -select a, d, grouping(a,b,c) - from gstest3 - group by grouping sets ((a,b), (a,c)); - a | d | grouping ----+---+---------- - 1 | 1 | 1 - 2 | 2 | 1 - 1 | 1 | 2 - 2 | 2 | 2 -(4 rows) - -explain (costs off) - select a, d, grouping(a,b,c) - from gstest3 - group by grouping sets ((a,b), (a,c)); - QUERY PLAN ---------------------------- - HashAggregate - Hash Key: a, b - Hash Key: a, c - -> Seq Scan on gstest3 -(4 rows) - --- simple rescan tests -select a, b, sum(v.x) - from (values (1),(2)) v(x), gstest_data(v.x) - group by grouping sets (a,b) - order by 1, 2, 3; - a | b | sum ----+---+----- - 1 | | 3 - 2 | | 6 - | 1 | 3 - | 2 | 3 - | 3 | 3 -(5 rows) - -explain (costs off) - select a, b, sum(v.x) - from (values (1),(2)) v(x), gstest_data(v.x) - group by grouping sets (a,b) - order by 3, 1, 2; - QUERY PLAN ---------------------------------------------------------------------- - Sort - Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b - -> HashAggregate - Hash Key: gstest_data.a - Hash Key: gstest_data.b - -> Nested Loop - -> Values Scan on "*VALUES*" - -> Function Scan on gstest_data -(8 rows) - -select * - from (values (1),(2)) v(x), - lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; -ERROR: aggregate functions are not allowed in FROM clause of their own query level -LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ... - ^ -explain (costs off) - select * - from (values (1),(2)) v(x), - lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; -ERROR: aggregate functions are not allowed in FROM clause of their own query level -LINE 4: lateral (select a, b, sum(v.x) from gstest_data(v.x... - ^ --- Tests for chained aggregates -select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; - a | b | grouping | sum | count | max ----+---+----------+-----+-------+----- - 1 | 1 | 0 | 21 | 2 | 11 - 1 | 2 | 0 | 25 | 2 | 13 - 1 | 3 | 0 | 14 | 1 | 14 - 2 | 3 | 0 | 15 | 1 | 15 - 3 | 3 | 0 | 16 | 1 | 16 - 3 | 4 | 0 | 17 | 1 | 17 - 4 | 1 | 0 | 37 | 2 | 19 - | | 3 | 21 | 2 | 11 - | | 3 | 21 | 2 | 11 - | | 3 | 25 | 2 | 13 - | | 3 | 25 | 2 | 13 - | | 3 | 14 | 1 | 14 - | | 3 | 14 | 1 | 14 - | | 3 | 15 | 1 | 15 - | | 3 | 15 | 1 | 15 - | | 3 | 16 | 1 | 16 - | | 3 | 16 | 1 | 16 - | | 3 | 17 | 1 | 17 - | | 3 | 17 | 1 | 17 - | | 3 | 37 | 2 | 19 - | | 3 | 37 | 2 | 19 -(21 rows) - -explain (costs off) - select a, b, grouping(a,b), sum(v), count(*), max(v) - from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; - QUERY PLAN -------------------------------------------------------------------------------------------- - Sort - Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) - -> HashAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) - Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) - -> Values Scan on "*VALUES*" -(7 rows) - -select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum - from gstest2 group by cube (a,b) order by rsum, a, b; - a | b | sum | rsum ----+---+-----+------ - 1 | 1 | 8 | 8 - 1 | 2 | 2 | 10 - 1 | | 10 | 20 - 2 | 2 | 2 | 22 - 2 | | 2 | 24 - | 1 | 8 | 32 - | 2 | 4 | 36 - | | 12 | 48 -(8 rows) - -explain (costs off) - select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum - from gstest2 group by cube (a,b) order by rsum, a, b; - QUERY PLAN ---------------------------------------------- - Sort - Sort Key: (sum((sum(c))) OVER (?)), a, b - -> WindowAgg - -> Sort - Sort Key: a, b - -> MixedAggregate - Hash Key: a, b - Hash Key: a - Hash Key: b - Group Key: () - -> Seq Scan on gstest2 -(11 rows) - -select a, b, sum(v.x) - from (values (1),(2)) v(x), gstest_data(v.x) - group by cube (a,b) order by a,b; - a | b | sum ----+---+----- - 1 | 1 | 1 - 1 | 2 | 1 - 1 | 3 | 1 - 1 | | 3 - 2 | 1 | 2 - 2 | 2 | 2 - 2 | 3 | 2 - 2 | | 6 - | 1 | 3 - | 2 | 3 - | 3 | 3 - | | 9 -(12 rows) - -explain (costs off) - select a, b, sum(v.x) - from (values (1),(2)) v(x), gstest_data(v.x) - group by cube (a,b) order by a,b; - QUERY PLAN ------------------------------------------------- - Sort - Sort Key: gstest_data.a, gstest_data.b - -> MixedAggregate - Hash Key: gstest_data.a, gstest_data.b - Hash Key: gstest_data.a - Hash Key: gstest_data.b - Group Key: () - -> Nested Loop - -> Values Scan on "*VALUES*" - -> Function Scan on gstest_data -(10 rows) - --- Verify that we correctly handle the child node returning a --- non-minimal slot, which happens if the input is pre-sorted, --- e.g. due to an index scan. -BEGIN; -SET LOCAL enable_hashagg = false; -EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; - QUERY PLAN ---------------------------------------- - Sort - Sort Key: a, b - -> GroupAggregate - Group Key: a - Group Key: () - Sort Key: b - Group Key: b - -> Sort - Sort Key: a - -> Seq Scan on gstest3 -(10 rows) - -SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; - a | b | count | max | max ----+---+-------+-----+----- - 1 | | 1 | 1 | 1 - 2 | | 1 | 2 | 2 - | 1 | 1 | 1 | 1 - | 2 | 1 | 2 | 2 - | | 2 | 2 | 2 -(5 rows) - -SET LOCAL enable_seqscan = false; -EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; - QUERY PLAN ------------------------------------------------------- - Sort - Sort Key: a, b - -> GroupAggregate - Group Key: a - Group Key: () - Sort Key: b - Group Key: b - -> Index Scan using gstest3_pkey on gstest3 -(8 rows) - -SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; - a | b | count | max | max ----+---+-------+-----+----- - 1 | | 1 | 1 | 1 - 2 | | 1 | 2 | 2 - | 1 | 1 | 1 | 1 - | 2 | 1 | 2 | 2 - | | 2 | 2 | 2 -(5 rows) - -COMMIT; --- More rescan tests -select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten; - a | a | four | ten | count ----+---+------+-----+------- - 1 | 1 | 0 | 0 | 50 - 1 | 1 | 0 | 2 | 50 - 1 | 1 | 0 | 4 | 50 - 1 | 1 | 0 | 6 | 50 - 1 | 1 | 0 | 8 | 50 - 1 | 1 | 0 | | 250 - 1 | 1 | 1 | 1 | 50 - 1 | 1 | 1 | 3 | 50 - 1 | 1 | 1 | 5 | 50 - 1 | 1 | 1 | 7 | 50 - 1 | 1 | 1 | 9 | 50 - 1 | 1 | 1 | | 250 - 1 | 1 | 2 | 0 | 50 - 1 | 1 | 2 | 2 | 50 - 1 | 1 | 2 | 4 | 50 - 1 | 1 | 2 | 6 | 50 - 1 | 1 | 2 | 8 | 50 - 1 | 1 | 2 | | 250 - 1 | 1 | 3 | 1 | 50 - 1 | 1 | 3 | 3 | 50 - 1 | 1 | 3 | 5 | 50 - 1 | 1 | 3 | 7 | 50 - 1 | 1 | 3 | 9 | 50 - 1 | 1 | 3 | | 250 - 1 | 1 | | 0 | 100 - 1 | 1 | | 1 | 100 - 1 | 1 | | 2 | 100 - 1 | 1 | | 3 | 100 - 1 | 1 | | 4 | 100 - 1 | 1 | | 5 | 100 - 1 | 1 | | 6 | 100 - 1 | 1 | | 7 | 100 - 1 | 1 | | 8 | 100 - 1 | 1 | | 9 | 100 - 1 | 1 | | | 1000 - 2 | 2 | 0 | 0 | 50 - 2 | 2 | 0 | 2 | 50 - 2 | 2 | 0 | 4 | 50 - 2 | 2 | 0 | 6 | 50 - 2 | 2 | 0 | 8 | 50 - 2 | 2 | 0 | | 250 - 2 | 2 | 1 | 1 | 50 - 2 | 2 | 1 | 3 | 50 - 2 | 2 | 1 | 5 | 50 - 2 | 2 | 1 | 7 | 50 - 2 | 2 | 1 | 9 | 50 - 2 | 2 | 1 | | 250 - 2 | 2 | 2 | 0 | 50 - 2 | 2 | 2 | 2 | 50 - 2 | 2 | 2 | 4 | 50 - 2 | 2 | 2 | 6 | 50 - 2 | 2 | 2 | 8 | 50 - 2 | 2 | 2 | | 250 - 2 | 2 | 3 | 1 | 50 - 2 | 2 | 3 | 3 | 50 - 2 | 2 | 3 | 5 | 50 - 2 | 2 | 3 | 7 | 50 - 2 | 2 | 3 | 9 | 50 - 2 | 2 | 3 | | 250 - 2 | 2 | | 0 | 100 - 2 | 2 | | 1 | 100 - 2 | 2 | | 2 | 100 - 2 | 2 | | 3 | 100 - 2 | 2 | | 4 | 100 - 2 | 2 | | 5 | 100 - 2 | 2 | | 6 | 100 - 2 | 2 | | 7 | 100 - 2 | 2 | | 8 | 100 - 2 | 2 | | 9 | 100 - 2 | 2 | | | 1000 -(70 rows) - -select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by cube(two,four) order by two,four) s1) from (values (1),(2)) v(a); - array ------------------------------------------------------------------------------------------------------------------------------------------------------- - {"(1,0,0,250)","(1,0,2,250)","(1,0,,500)","(1,1,1,250)","(1,1,3,250)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)","(1,,,1000)"} - {"(2,0,0,250)","(2,0,2,250)","(2,0,,500)","(2,1,1,250)","(2,1,3,250)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)","(2,,,1000)"} -(2 rows) - --- Rescan logic changes when there are no empty grouping sets, so test --- that too: -select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by grouping sets(four,ten)) s on true order by v.a,four,ten; - a | a | four | ten | count ----+---+------+-----+------- - 1 | 1 | 0 | | 250 - 1 | 1 | 1 | | 250 - 1 | 1 | 2 | | 250 - 1 | 1 | 3 | | 250 - 1 | 1 | | 0 | 100 - 1 | 1 | | 1 | 100 - 1 | 1 | | 2 | 100 - 1 | 1 | | 3 | 100 - 1 | 1 | | 4 | 100 - 1 | 1 | | 5 | 100 - 1 | 1 | | 6 | 100 - 1 | 1 | | 7 | 100 - 1 | 1 | | 8 | 100 - 1 | 1 | | 9 | 100 - 2 | 2 | 0 | | 250 - 2 | 2 | 1 | | 250 - 2 | 2 | 2 | | 250 - 2 | 2 | 3 | | 250 - 2 | 2 | | 0 | 100 - 2 | 2 | | 1 | 100 - 2 | 2 | | 2 | 100 - 2 | 2 | | 3 | 100 - 2 | 2 | | 4 | 100 - 2 | 2 | | 5 | 100 - 2 | 2 | | 6 | 100 - 2 | 2 | | 7 | 100 - 2 | 2 | | 8 | 100 - 2 | 2 | | 9 | 100 -(28 rows) - -select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by grouping sets(two,four) order by two,four) s1) from (values (1),(2)) v(a); - array ---------------------------------------------------------------------------------- - {"(1,0,,500)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)"} - {"(2,0,,500)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)"} -(2 rows) - --- test the knapsack -set enable_indexscan = false; -set hash_mem_multiplier = 1.0; -set work_mem = '64kB'; -explain (costs off) - select unique1, - count(two), count(four), count(ten), - count(hundred), count(thousand), count(twothousand), - count(*) - from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); - QUERY PLAN -------------------------------- - MixedAggregate - Hash Key: two - Hash Key: four - Hash Key: ten - Hash Key: hundred - Group Key: unique1 - Sort Key: twothousand - Group Key: twothousand - Sort Key: thousand - Group Key: thousand - -> Sort - Sort Key: unique1 - -> Seq Scan on tenk1 -(13 rows) - -explain (costs off) - select unique1, - count(two), count(four), count(ten), - count(hundred), count(thousand), count(twothousand), - count(*) - from tenk1 group by grouping sets (unique1,hundred,ten,four,two); - QUERY PLAN -------------------------------- - MixedAggregate - Hash Key: two - Hash Key: four - Hash Key: ten - Hash Key: hundred - Group Key: unique1 - -> Sort - Sort Key: unique1 - -> Seq Scan on tenk1 -(9 rows) - -set work_mem = '384kB'; -explain (costs off) - select unique1, - count(two), count(four), count(ten), - count(hundred), count(thousand), count(twothousand), - count(*) - from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); - QUERY PLAN -------------------------------- - MixedAggregate - Hash Key: two - Hash Key: four - Hash Key: ten - Hash Key: hundred - Hash Key: thousand - Group Key: unique1 - Sort Key: twothousand - Group Key: twothousand - -> Sort - Sort Key: unique1 - -> Seq Scan on tenk1 -(12 rows) - --- check collation-sensitive matching between grouping expressions --- (similar to a check for aggregates, but there are additional code --- paths for GROUPING, so check again here) -select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*) - from unnest(array[1,1], array['a','b']) u(i,v) - group by rollup(i, v||'a') order by 1,3; - ?column? | case | count -----------+------+------- - aa | 0 | 1 - ba | 0 | 1 - | 1 | 2 - | 1 | 2 -(4 rows) - -select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*) - from unnest(array[1,1], array['a','b']) u(i,v) - group by rollup(i, v||'a') order by 1,3; - ?column? | case | count -----------+------+------- - aa | 0 | 1 - ba | 0 | 1 - | 1 | 2 - | 1 | 2 -(4 rows) - --- Bug #16784 -create table bug_16784(i int, j int); -analyze bug_16784; -alter table bug_16784 set (autovacuum_enabled = 'false'); -update pg_class set reltuples = 10 where relname='bug_16784'; -insert into bug_16784 select g/10, g from generate_series(1,40) g; -set work_mem='64kB'; -set enable_sort = false; -select * from - (values (1),(2)) v(a), - lateral (select a, i, j, count(*) from - bug_16784 group by cube(i,j)) s - order by v.a, i, j; - a | a | i | j | count ----+---+---+----+------- - 1 | 1 | 0 | 1 | 1 - 1 | 1 | 0 | 2 | 1 - 1 | 1 | 0 | 3 | 1 - 1 | 1 | 0 | 4 | 1 - 1 | 1 | 0 | 5 | 1 - 1 | 1 | 0 | 6 | 1 - 1 | 1 | 0 | 7 | 1 - 1 | 1 | 0 | 8 | 1 - 1 | 1 | 0 | 9 | 1 - 1 | 1 | 0 | | 9 - 1 | 1 | 1 | 10 | 1 - 1 | 1 | 1 | 11 | 1 - 1 | 1 | 1 | 12 | 1 - 1 | 1 | 1 | 13 | 1 - 1 | 1 | 1 | 14 | 1 - 1 | 1 | 1 | 15 | 1 - 1 | 1 | 1 | 16 | 1 - 1 | 1 | 1 | 17 | 1 - 1 | 1 | 1 | 18 | 1 - 1 | 1 | 1 | 19 | 1 - 1 | 1 | 1 | | 10 - 1 | 1 | 2 | 20 | 1 - 1 | 1 | 2 | 21 | 1 - 1 | 1 | 2 | 22 | 1 - 1 | 1 | 2 | 23 | 1 - 1 | 1 | 2 | 24 | 1 - 1 | 1 | 2 | 25 | 1 - 1 | 1 | 2 | 26 | 1 - 1 | 1 | 2 | 27 | 1 - 1 | 1 | 2 | 28 | 1 - 1 | 1 | 2 | 29 | 1 - 1 | 1 | 2 | | 10 - 1 | 1 | 3 | 30 | 1 - 1 | 1 | 3 | 31 | 1 - 1 | 1 | 3 | 32 | 1 - 1 | 1 | 3 | 33 | 1 - 1 | 1 | 3 | 34 | 1 - 1 | 1 | 3 | 35 | 1 - 1 | 1 | 3 | 36 | 1 - 1 | 1 | 3 | 37 | 1 - 1 | 1 | 3 | 38 | 1 - 1 | 1 | 3 | 39 | 1 - 1 | 1 | 3 | | 10 - 1 | 1 | 4 | 40 | 1 - 1 | 1 | 4 | | 1 - 1 | 1 | | 1 | 1 - 1 | 1 | | 2 | 1 - 1 | 1 | | 3 | 1 - 1 | 1 | | 4 | 1 - 1 | 1 | | 5 | 1 - 1 | 1 | | 6 | 1 - 1 | 1 | | 7 | 1 - 1 | 1 | | 8 | 1 - 1 | 1 | | 9 | 1 - 1 | 1 | | 10 | 1 - 1 | 1 | | 11 | 1 - 1 | 1 | | 12 | 1 - 1 | 1 | | 13 | 1 - 1 | 1 | | 14 | 1 - 1 | 1 | | 15 | 1 - 1 | 1 | | 16 | 1 - 1 | 1 | | 17 | 1 - 1 | 1 | | 18 | 1 - 1 | 1 | | 19 | 1 - 1 | 1 | | 20 | 1 - 1 | 1 | | 21 | 1 - 1 | 1 | | 22 | 1 - 1 | 1 | | 23 | 1 - 1 | 1 | | 24 | 1 - 1 | 1 | | 25 | 1 - 1 | 1 | | 26 | 1 - 1 | 1 | | 27 | 1 - 1 | 1 | | 28 | 1 - 1 | 1 | | 29 | 1 - 1 | 1 | | 30 | 1 - 1 | 1 | | 31 | 1 - 1 | 1 | | 32 | 1 - 1 | 1 | | 33 | 1 - 1 | 1 | | 34 | 1 - 1 | 1 | | 35 | 1 - 1 | 1 | | 36 | 1 - 1 | 1 | | 37 | 1 - 1 | 1 | | 38 | 1 - 1 | 1 | | 39 | 1 - 1 | 1 | | 40 | 1 - 1 | 1 | | | 40 - 2 | 2 | 0 | 1 | 1 - 2 | 2 | 0 | 2 | 1 - 2 | 2 | 0 | 3 | 1 - 2 | 2 | 0 | 4 | 1 - 2 | 2 | 0 | 5 | 1 - 2 | 2 | 0 | 6 | 1 - 2 | 2 | 0 | 7 | 1 - 2 | 2 | 0 | 8 | 1 - 2 | 2 | 0 | 9 | 1 - 2 | 2 | 0 | | 9 - 2 | 2 | 1 | 10 | 1 - 2 | 2 | 1 | 11 | 1 - 2 | 2 | 1 | 12 | 1 - 2 | 2 | 1 | 13 | 1 - 2 | 2 | 1 | 14 | 1 - 2 | 2 | 1 | 15 | 1 - 2 | 2 | 1 | 16 | 1 - 2 | 2 | 1 | 17 | 1 - 2 | 2 | 1 | 18 | 1 - 2 | 2 | 1 | 19 | 1 - 2 | 2 | 1 | | 10 - 2 | 2 | 2 | 20 | 1 - 2 | 2 | 2 | 21 | 1 - 2 | 2 | 2 | 22 | 1 - 2 | 2 | 2 | 23 | 1 - 2 | 2 | 2 | 24 | 1 - 2 | 2 | 2 | 25 | 1 - 2 | 2 | 2 | 26 | 1 - 2 | 2 | 2 | 27 | 1 - 2 | 2 | 2 | 28 | 1 - 2 | 2 | 2 | 29 | 1 - 2 | 2 | 2 | | 10 - 2 | 2 | 3 | 30 | 1 - 2 | 2 | 3 | 31 | 1 - 2 | 2 | 3 | 32 | 1 - 2 | 2 | 3 | 33 | 1 - 2 | 2 | 3 | 34 | 1 - 2 | 2 | 3 | 35 | 1 - 2 | 2 | 3 | 36 | 1 - 2 | 2 | 3 | 37 | 1 - 2 | 2 | 3 | 38 | 1 - 2 | 2 | 3 | 39 | 1 - 2 | 2 | 3 | | 10 - 2 | 2 | 4 | 40 | 1 - 2 | 2 | 4 | | 1 - 2 | 2 | | 1 | 1 - 2 | 2 | | 2 | 1 - 2 | 2 | | 3 | 1 - 2 | 2 | | 4 | 1 - 2 | 2 | | 5 | 1 - 2 | 2 | | 6 | 1 - 2 | 2 | | 7 | 1 - 2 | 2 | | 8 | 1 - 2 | 2 | | 9 | 1 - 2 | 2 | | 10 | 1 - 2 | 2 | | 11 | 1 - 2 | 2 | | 12 | 1 - 2 | 2 | | 13 | 1 - 2 | 2 | | 14 | 1 - 2 | 2 | | 15 | 1 - 2 | 2 | | 16 | 1 - 2 | 2 | | 17 | 1 - 2 | 2 | | 18 | 1 - 2 | 2 | | 19 | 1 - 2 | 2 | | 20 | 1 - 2 | 2 | | 21 | 1 - 2 | 2 | | 22 | 1 - 2 | 2 | | 23 | 1 - 2 | 2 | | 24 | 1 - 2 | 2 | | 25 | 1 - 2 | 2 | | 26 | 1 - 2 | 2 | | 27 | 1 - 2 | 2 | | 28 | 1 - 2 | 2 | | 29 | 1 - 2 | 2 | | 30 | 1 - 2 | 2 | | 31 | 1 - 2 | 2 | | 32 | 1 - 2 | 2 | | 33 | 1 - 2 | 2 | | 34 | 1 - 2 | 2 | | 35 | 1 - 2 | 2 | | 36 | 1 - 2 | 2 | | 37 | 1 - 2 | 2 | | 38 | 1 - 2 | 2 | | 39 | 1 - 2 | 2 | | 40 | 1 - 2 | 2 | | | 40 -(172 rows) - --- --- Compare results between plans using sorting and plans using hash --- aggregation. Force spilling in both cases by setting work_mem low --- and altering the statistics. --- -create table gs_data_1 as -select g%1000 as g1000, g%100 as g100, g%10 as g10, g - from generate_series(0,1999) g; -analyze gs_data_1; -alter table gs_data_1 set (autovacuum_enabled = 'false'); -update pg_class set reltuples = 10 where relname='gs_data_1'; -set work_mem='64kB'; --- Produce results with sorting. -set enable_sort = true; -set enable_hashagg = false; -set jit_above_cost = 0; -explain (costs off) -select g100, g10, sum(g::numeric), count(*), max(g::text) -from gs_data_1 group by cube (g1000, g100,g10); - QUERY PLAN ------------------------------------- - GroupAggregate - Group Key: g1000, g100, g10 - Group Key: g1000, g100 - Group Key: g1000 - Group Key: () - Sort Key: g100, g10 - Group Key: g100, g10 - Group Key: g100 - Sort Key: g10, g1000 - Group Key: g10, g1000 - Group Key: g10 - -> Sort - Sort Key: g1000, g100, g10 - -> Seq Scan on gs_data_1 -(14 rows) - -create table gs_group_1 as -select g100, g10, sum(g::numeric), count(*), max(g::text) -from gs_data_1 group by cube (g1000, g100,g10); --- Produce results with hash aggregation. -set enable_hashagg = true; -set enable_sort = false; -explain (costs off) -select g100, g10, sum(g::numeric), count(*), max(g::text) -from gs_data_1 group by cube (g1000, g100,g10); - QUERY PLAN ------------------------------- - MixedAggregate - Hash Key: g1000, g100, g10 - Hash Key: g1000, g100 - Hash Key: g1000 - Hash Key: g100, g10 - Hash Key: g100 - Hash Key: g10, g1000 - Hash Key: g10 - Group Key: () - -> Seq Scan on gs_data_1 -(10 rows) - -create table gs_hash_1 as -select g100, g10, sum(g::numeric), count(*), max(g::text) -from gs_data_1 group by cube (g1000, g100,g10); -set enable_sort = true; -set work_mem to default; -set hash_mem_multiplier to default; --- Compare results -(select * from gs_hash_1 except select * from gs_group_1) - union all -(select * from gs_group_1 except select * from gs_hash_1); - g100 | g10 | sum | count | max -------+-----+-----+-------+----- -(0 rows) - -drop table gs_group_1; -drop table gs_hash_1; --- GROUP BY DISTINCT --- "normal" behavior... -select a, b, c -from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) -group by all 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) - --- ...which is also the default -select a, b, c -from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) -group by rollup(a, b), rollup(a, c) -order by a, b, c; - a | b | c ----+---+--- - 1 | 2 | 3 - 1 | 2 | - 1 | 2 | - 1 | | 3 - 1 | | 3 - 1 | | - 1 | | - 1 | | - 4 | | 6 - 4 | | 6 - 4 | | 6 - 4 | | - 4 | | - 4 | | - 4 | | - 4 | | - 7 | 8 | 9 - 7 | 8 | - 7 | 8 | - 7 | | 9 - 7 | | 9 - 7 | | - 7 | | - 7 | | - | | -(25 rows) - --- "group by distinct" behavior... -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) - --- ...which is not the same as "select distinct" -select distinct a, b, c -from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) -group by rollup(a, b), rollup(a, c) -order by a, b, c; - a | b | c ----+---+--- - 1 | 2 | 3 - 1 | 2 | - 1 | | 3 - 1 | | - 4 | | 6 - 4 | | - 7 | 8 | 9 - 7 | 8 | - 7 | | 9 - 7 | | - | | -(11 rows) - --- test handling of outer GroupingFunc within subqueries -explain (costs off) -select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); - QUERY PLAN ---------------------------- - MixedAggregate - Hash Key: $2 - Group Key: () - InitPlan 1 (returns $1) - -> Result - InitPlan 3 (returns $2) - -> Result - -> Result - SubPlan 2 - -> Result -(10 rows) - -select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); - grouping ----------- - 1 - 0 -(2 rows) - -explain (costs off) -select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; - QUERY PLAN ---------------------------- - GroupAggregate - InitPlan 1 (returns $1) - -> Result - InitPlan 3 (returns $2) - -> Result - -> Result - SubPlan 2 - -> Result -(8 rows) - -select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; - grouping ----------- - 0 -(1 row) - --- expressions nullable by grouping sets -explain (costs off) -select distinct on (a, b) a, b -from (values (1, 1), (2, 2)) as t (a, b) where a = b -group by grouping sets((a, b), (a)) -order by a, b; - QUERY PLAN ----------------------------------------------------------------- - Unique - -> Sort - Sort Key: "*VALUES*".column1, "*VALUES*".column2 - -> HashAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Filter: (column1 = column2) -(8 rows) - -select distinct on (a, b) a, b -from (values (1, 1), (2, 2)) as t (a, b) where a = b -group by grouping sets((a, b), (a)) -order by a, b; - a | b ----+--- - 1 | 1 - 1 | - 2 | 2 - 2 | -(4 rows) - -explain (costs off) -select distinct on (a, b+1) a, b+1 -from (values (1, 0), (2, 1)) as t (a, b) where a = b+1 -group by grouping sets((a, b+1), (a)) -order by a, b+1; - QUERY PLAN ----------------------------------------------------------------------- - Unique - -> Sort - Sort Key: "*VALUES*".column1, (("*VALUES*".column2 + 1)) - -> HashAggregate - Hash Key: "*VALUES*".column1, ("*VALUES*".column2 + 1) - Hash Key: "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Filter: (column1 = (column2 + 1)) -(8 rows) - -select distinct on (a, b+1) a, b+1 -from (values (1, 0), (2, 1)) as t (a, b) where a = b+1 -group by grouping sets((a, b+1), (a)) -order by a, b+1; - a | ?column? ----+---------- - 1 | 1 - 1 | - 2 | 2 - 2 | -(4 rows) - -explain (costs off) -select a, b -from (values (1, 1), (2, 2)) as t (a, b) where a = b -group by grouping sets((a, b), (a)) -order by a, b nulls first; - QUERY PLAN ----------------------------------------------------------------- - Sort - Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST - -> HashAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Filter: (column1 = column2) -(7 rows) - -select a, b -from (values (1, 1), (2, 2)) as t (a, b) where a = b -group by grouping sets((a, b), (a)) -order by a, b nulls first; - a | b ----+--- - 1 | - 1 | 1 - 2 | - 2 | 2 -(4 rows) - -explain (costs off) -select 1 as one group by rollup(one) order by one nulls first; - QUERY PLAN ------------------------------ - Sort - Sort Key: (1) NULLS FIRST - -> MixedAggregate - Hash Key: 1 - Group Key: () - -> Result -(6 rows) - -select 1 as one group by rollup(one) order by one nulls first; - one ------ - - 1 -(2 rows) - --- end +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -w -U3 C:/cirrus/src/test/regress/expected/tsrf.out C:/cirrus/build/testrun/regress/regress/results/tsrf.out --- C:/cirrus/src/test/regress/expected/tsrf.out 2024-03-11 18:11:56.581775000 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/tsrf.out 2024-03-11 18:14:55.140760900 +0000 @@ -304,409 +304,7 @@ -- grouping sets are a bit special, they produce NULLs in columns not actually NULL set enable_hashagg = false; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab); - dataa | b | g | count --------+-----+---+------- - a | bar | 1 | 1 - a | bar | 2 | 1 - a | foo | 1 | 1 - a | foo | 2 | 1 - a | | 1 | 2 - a | | 2 | 2 - b | bar | 1 | 1 - b | bar | 2 | 1 - b | | 1 | 1 - b | | 2 | 1 - | | 1 | 3 - | | 2 | 3 - | bar | 1 | 2 - | bar | 2 | 2 - | foo | 1 | 1 - | foo | 2 | 1 -(16 rows) - -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa; - dataa | b | g | count --------+-----+---+------- - a | bar | 1 | 1 - a | bar | 2 | 1 - a | foo | 1 | 1 - a | foo | 2 | 1 - a | | 1 | 2 - a | | 2 | 2 - b | bar | 1 | 1 - b | bar | 2 | 1 - b | | 1 | 1 - b | | 2 | 1 - | | 1 | 3 - | | 2 | 3 - | bar | 1 | 2 - | bar | 2 | 2 - | foo | 1 | 1 - | foo | 2 | 1 -(16 rows) - -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g; - dataa | b | g | count --------+-----+---+------- - a | bar | 1 | 1 - a | foo | 1 | 1 - a | | 1 | 2 - b | bar | 1 | 1 - b | | 1 | 1 - | | 1 | 3 - | bar | 1 | 2 - | foo | 1 | 1 - | foo | 2 | 1 - a | bar | 2 | 1 - b | | 2 | 1 - a | foo | 2 | 1 - | bar | 2 | 2 - a | | 2 | 2 - | | 2 | 3 - b | bar | 2 | 1 -(16 rows) - -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g); - dataa | b | g | count --------+-----+---+------- - a | bar | 1 | 1 - a | bar | 2 | 1 - a | bar | | 2 - a | foo | 1 | 1 - a | foo | 2 | 1 - a | foo | | 2 - a | | | 4 - b | bar | 1 | 1 - b | bar | 2 | 1 - b | bar | | 2 - b | | | 2 - | | | 6 - | bar | 1 | 2 - | bar | 2 | 2 - | bar | | 4 - | foo | 1 | 1 - | foo | 2 | 1 - | foo | | 2 - a | | 1 | 2 - b | | 1 | 1 - | | 1 | 3 - a | | 2 | 2 - b | | 2 | 1 - | | 2 | 3 -(24 rows) - -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; - dataa | b | g | count --------+-----+---+------- - a | foo | | 2 - a | | | 4 - a | | 2 | 2 - a | bar | 1 | 1 - a | bar | 2 | 1 - a | bar | | 2 - a | foo | 1 | 1 - a | foo | 2 | 1 - a | | 1 | 2 - b | bar | 1 | 1 - b | | | 2 - b | | 1 | 1 - b | bar | 2 | 1 - b | bar | | 2 - b | | 2 | 1 - | | 2 | 3 - | | | 6 - | bar | 1 | 2 - | bar | 2 | 2 - | bar | | 4 - | foo | 1 | 1 - | foo | 2 | 1 - | foo | | 2 - | | 1 | 3 -(24 rows) - -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g; - dataa | b | g | count --------+-----+---+------- - a | bar | 1 | 1 - a | foo | 1 | 1 - b | bar | 1 | 1 - | bar | 1 | 2 - | foo | 1 | 1 - a | | 1 | 2 - b | | 1 | 1 - | | 1 | 3 - a | | 2 | 2 - b | | 2 | 1 - | bar | 2 | 2 - | | 2 | 3 - | foo | 2 | 1 - a | bar | 2 | 1 - a | foo | 2 | 1 - b | bar | 2 | 1 - a | | | 4 - b | bar | | 2 - b | | | 2 - | | | 6 - a | foo | | 2 - a | bar | | 2 - | bar | | 4 - | foo | | 2 -(24 rows) - -reset enable_hashagg; --- case with degenerate ORDER BY -explain (verbose, costs off) -select 'foo' as f, generate_series(1,2) as g from few order by 1; - QUERY PLAN ----------------------------------------------- - ProjectSet - Output: 'foo'::text, generate_series(1, 2) - -> Seq Scan on public.few - Output: id, dataa, datab -(4 rows) - -select 'foo' as f, generate_series(1,2) as g from few order by 1; - f | g ------+--- - foo | 1 - foo | 2 - foo | 1 - foo | 2 - foo | 1 - foo | 2 -(6 rows) - --- data modification -CREATE TABLE fewmore AS SELECT generate_series(1,3) AS data; -INSERT INTO fewmore VALUES(generate_series(4,5)); -SELECT * FROM fewmore; - data ------- - 1 - 2 - 3 - 4 - 5 -(5 rows) - --- SRFs are not allowed in UPDATE (they once were, but it was nonsense) -UPDATE fewmore SET data = generate_series(4,9); -ERROR: set-returning functions are not allowed in UPDATE -LINE 1: UPDATE fewmore SET data = generate_series(4,9); - ^ --- SRFs are not allowed in RETURNING -INSERT INTO fewmore VALUES(1) RETURNING generate_series(1,3); -ERROR: set-returning functions are not allowed in RETURNING -LINE 1: INSERT INTO fewmore VALUES(1) RETURNING generate_series(1,3)... - ^ --- nor standalone VALUES (but surely this is a bug?) -VALUES(1, generate_series(1,2)); -ERROR: set-returning functions are not allowed in VALUES -LINE 1: VALUES(1, generate_series(1,2)); - ^ --- We allow tSRFs that are not at top level -SELECT int4mul(generate_series(1,2), 10); - int4mul ---------- - 10 - 20 -(2 rows) - -SELECT generate_series(1,3) IS DISTINCT FROM 2; - ?column? ----------- - t - f - t -(3 rows) - --- but SRFs in function RTEs must be at top level (annoying restriction) -SELECT * FROM int4mul(generate_series(1,2), 10); -ERROR: set-returning functions must appear at top level of FROM -LINE 1: SELECT * FROM int4mul(generate_series(1,2), 10); - ^ --- DISTINCT ON is evaluated before tSRF evaluation if SRF is not --- referenced either in ORDER BY or in the DISTINCT ON list. The ORDER --- BY reference can be implicitly generated, if there's no other ORDER BY. --- implicit reference (via implicit ORDER) to all columns -SELECT DISTINCT ON (a) a, b, generate_series(1,3) g -FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b); - a | b | g ----+---+--- - 1 | 1 | 1 - 3 | 2 | 1 - 5 | 3 | 1 -(3 rows) - --- unreferenced in DISTINCT ON or ORDER BY -SELECT DISTINCT ON (a) a, b, generate_series(1,3) g -FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b) -ORDER BY a, b DESC; - a | b | g ----+---+--- - 1 | 4 | 1 - 1 | 4 | 2 - 1 | 4 | 3 - 3 | 2 | 1 - 3 | 2 | 2 - 3 | 2 | 3 - 5 | 3 | 1 - 5 | 3 | 2 - 5 | 3 | 3 -(9 rows) - --- referenced in ORDER BY -SELECT DISTINCT ON (a) a, b, generate_series(1,3) g -FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b) -ORDER BY a, b DESC, g DESC; - a | b | g ----+---+--- - 1 | 4 | 3 - 3 | 2 | 3 - 5 | 3 | 3 -(3 rows) - --- referenced in ORDER BY and DISTINCT ON -SELECT DISTINCT ON (a, b, g) a, b, generate_series(1,3) g -FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b) -ORDER BY a, b DESC, g DESC; - a | b | g ----+---+--- - 1 | 4 | 3 - 1 | 4 | 2 - 1 | 4 | 1 - 1 | 1 | 3 - 1 | 1 | 2 - 1 | 1 | 1 - 3 | 2 | 3 - 3 | 2 | 2 - 3 | 2 | 1 - 3 | 1 | 3 - 3 | 1 | 2 - 3 | 1 | 1 - 5 | 3 | 3 - 5 | 3 | 2 - 5 | 3 | 1 - 5 | 1 | 3 - 5 | 1 | 2 - 5 | 1 | 1 -(18 rows) - --- only SRF mentioned in DISTINCT ON -SELECT DISTINCT ON (g) a, b, generate_series(1,3) g -FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b); - a | b | g ----+---+--- - 3 | 2 | 1 - 5 | 1 | 2 - 3 | 1 | 3 -(3 rows) - --- LIMIT / OFFSET is evaluated after SRF evaluation -SELECT a, generate_series(1,2) FROM (VALUES(1),(2),(3)) r(a) LIMIT 2 OFFSET 2; - a | generate_series ----+----------------- - 2 | 1 - 2 | 2 -(2 rows) - --- SRFs are not allowed in LIMIT. -SELECT 1 LIMIT generate_series(1,3); -ERROR: set-returning functions are not allowed in LIMIT -LINE 1: SELECT 1 LIMIT generate_series(1,3); - ^ --- tSRF in correlated subquery, referencing table outside -SELECT (SELECT generate_series(1,3) LIMIT 1 OFFSET few.id) FROM few; - generate_series ------------------ - 2 - 3 - -(3 rows) - --- tSRF in correlated subquery, referencing SRF outside -SELECT (SELECT generate_series(1,3) LIMIT 1 OFFSET g.i) FROM generate_series(0,3) g(i); - generate_series ------------------ - 1 - 2 - 3 - -(4 rows) - --- Operators can return sets too -CREATE OPERATOR |@| (PROCEDURE = unnest, RIGHTARG = ANYARRAY); -SELECT |@|ARRAY[1,2,3]; - ?column? ----------- - 1 - 2 - 3 -(3 rows) - --- Some fun cases involving duplicate SRF calls -explain (verbose, costs off) -select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1; - QUERY PLAN ------------------------------------------------------------------- - Result - Output: (generate_series(1, 3)), ((generate_series(1, 3)) + 1) - -> ProjectSet - Output: generate_series(1, 3) - -> Result -(5 rows) - -select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1; - x | xp1 ----+----- - 1 | 2 - 2 | 3 - 3 | 4 -(3 rows) - -explain (verbose, costs off) -select generate_series(1,3)+1 order by generate_series(1,3); - QUERY PLAN ------------------------------------------------------------------------- - Sort - Output: (((generate_series(1, 3)) + 1)), (generate_series(1, 3)) - Sort Key: (generate_series(1, 3)) - -> Result - Output: ((generate_series(1, 3)) + 1), (generate_series(1, 3)) - -> ProjectSet - Output: generate_series(1, 3) - -> Result -(8 rows) - -select generate_series(1,3)+1 order by generate_series(1,3); - ?column? ----------- - 2 - 3 - 4 -(3 rows) - --- Check that SRFs of same nesting level run in lockstep -explain (verbose, costs off) -select generate_series(1,3) as x, generate_series(3,6) + 1 as y; - QUERY PLAN ------------------------------------------------------------------- - Result - Output: (generate_series(1, 3)), ((generate_series(3, 6)) + 1) - -> ProjectSet - Output: generate_series(1, 3), generate_series(3, 6) - -> Result -(5 rows) - -select generate_series(1,3) as x, generate_series(3,6) + 1 as y; - x | y ----+--- - 1 | 4 - 2 | 5 - 3 | 6 - | 7 -(4 rows) - --- Clean up -DROP TABLE few; -DROP TABLE fewmore; +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -w -U3 C:/cirrus/src/test/regress/expected/stats_ext.out C:/cirrus/build/testrun/regress/regress/results/stats_ext.out --- C:/cirrus/src/test/regress/expected/stats_ext.out 2024-03-11 18:11:56.559878300 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/stats_ext.out 2024-03-11 18:15:00.945931000 +0000 @@ -3252,6 +3252,7 @@ LANGUAGE plpgsql; CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int, restrict = scalarltsel); +ERROR: operator <<< already exists SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied ERROR: permission denied for table priv_test_tbl DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied @@ -3283,6 +3284,7 @@ DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak -- Tidy up DROP OPERATOR <<< (int, int); +ERROR: must be owner of operator <<< DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; DROP SCHEMA tststats CASCADE; diff -w -U3 C:/cirrus/src/test/regress/expected/partition_prune.out C:/cirrus/build/testrun/regress/regress/results/partition_prune.out --- C:/cirrus/src/test/regress/expected/partition_prune.out 2024-03-11 18:11:56.528124700 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/partition_prune.out 2024-03-11 18:15:28.969360700 +0000 @@ -2445,1977 +2445,7 @@ create index ab_a1_b2_a_idx on ab_a1_b2 (a); create index ab_a1_b3_a_idx on ab_a1_b3 (a); create index ab_a3_b1_a_idx on ab_a3_b1 (a); -create index ab_a3_b2_a_idx on ab_a3_b2 (a); -create index ab_a3_b3_a_idx on ab_a3_b3 (a); -set enable_hashjoin = 0; -set enable_mergejoin = 0; -set enable_memoize = 0; -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)'); - explain_parallel_append --------------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 1 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Nested Loop (actual rows=N loops=N) - -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) - Filter: (a = ANY ('{0,0,1}'::integer[])) - -> Append (actual rows=N loops=N) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) - Index Cond: (a = a.a) -(27 rows) - --- Ensure the same partitions are pruned when we make the nested loop --- parameter an Expr rather than a plain Param. -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1)'); - explain_parallel_append --------------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 1 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Nested Loop (actual rows=N loops=N) - -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) - Filter: (a = ANY ('{0,0,1}'::integer[])) - -> Append (actual rows=N loops=N) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) - Index Cond: (a = (a.a + 0)) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) - Index Cond: (a = (a.a + 0)) -(27 rows) - -insert into lprt_a values(3),(3); -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)'); - explain_parallel_append --------------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 1 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Nested Loop (actual rows=N loops=N) - -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) - Filter: (a = ANY ('{1,0,3}'::integer[])) - -> Append (actual rows=N loops=N) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N) - Index Cond: (a = a.a) -(27 rows) - -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); - explain_parallel_append --------------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 1 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Nested Loop (actual rows=N loops=N) - -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) - Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N - -> Append (actual rows=N loops=N) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) - Index Cond: (a = a.a) -(28 rows) - -delete from lprt_a where a = 1; -select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); - explain_parallel_append -------------------------------------------------------------------------------------------------- - Finalize Aggregate (actual rows=N loops=N) - -> Gather (actual rows=N loops=N) - Workers Planned: 1 - Workers Launched: N - -> Partial Aggregate (actual rows=N loops=N) - -> Nested Loop (actual rows=N loops=N) - -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) - Filter: (a = ANY ('{1,0,0}'::integer[])) - Rows Removed by Filter: N - -> Append (actual rows=N loops=N) - -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed) - Index Cond: (a = a.a) - -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) - Index Cond: (a = a.a) -(28 rows) - -reset enable_hashjoin; -reset enable_mergejoin; -reset enable_memoize; -reset parallel_setup_cost; -reset parallel_tuple_cost; -reset min_parallel_table_scan_size; -reset max_parallel_workers_per_gather; --- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) -select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); - QUERY PLAN -------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Aggregate (actual rows=1 loops=1) - -> Seq Scan on lprt_a (actual rows=102 loops=1) - InitPlan 2 (returns $1) - -> Aggregate (actual rows=1 loops=1) - -> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1) - Index Cond: (a = $0) - -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) - Recheck Cond: (a = $0) - Filter: (b = $1) - -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) - Index Cond: (a = $0) -(52 rows) - --- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) -select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); - QUERY PLAN -------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Append (actual rows=0 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - Filter: (b = $0) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) - Recheck Cond: (a = 1) - Filter: (b = $0) - -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) - Recheck Cond: (a = 1) - Filter: (b = $0) - -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) - Index Cond: (a = 1) - -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b = $0) -(37 rows) - --- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) -select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); - QUERY PLAN -------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Append (actual rows=0 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - Filter: (b = $0) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) - Recheck Cond: (a = 1) - Filter: (b = $0) - -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) - Recheck Cond: (a = 1) - Filter: (b = $0) - -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) - Index Cond: (a = 1) - -> Result (actual rows=0 loops=1) - One-Time Filter: (5 = $0) - -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b = $0) -(39 rows) - --- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. -create table xy_1 (x int, y int); -insert into xy_1 values(100,-10); -set enable_bitmapscan = 0; -set enable_indexscan = 0; -prepare ab_q6 as -select * from ( - select tableoid::regclass,a,b from ab -union all - select tableoid::regclass,x,y from xy_1 -union all - select tableoid::regclass,a,b from ab -) ab where a = $1 and b = (select -10); --- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); - QUERY PLAN --------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 12 - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Seq Scan on ab_a1_b1 ab_1 (never executed) - Filter: ((a = $1) AND (b = $0)) - -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: ((a = $1) AND (b = $0)) - -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: ((a = $1) AND (b = $0)) - -> Seq Scan on xy_1 (actual rows=0 loops=1) - Filter: ((x = $1) AND (y = $0)) - Rows Removed by Filter: 1 - -> Seq Scan on ab_a1_b1 ab_4 (never executed) - Filter: ((a = $1) AND (b = $0)) - -> Seq Scan on ab_a1_b2 ab_5 (never executed) - Filter: ((a = $1) AND (b = $0)) - -> Seq Scan on ab_a1_b3 ab_6 (never executed) - Filter: ((a = $1) AND (b = $0)) -(19 rows) - --- Ensure we see just the xy_1 row. -execute ab_q6(100); - tableoid | a | b -----------+-----+----- - xy_1 | 100 | -10 -(1 row) - -reset enable_bitmapscan; -reset enable_indexscan; -deallocate ab_q1; -deallocate ab_q2; -deallocate ab_q3; -deallocate ab_q4; -deallocate ab_q5; -deallocate ab_q6; --- UPDATE on a partition subtree has been seen to have problems. -insert into ab values (1,2); -explain (analyze, costs off, summary off, timing off) -update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; - QUERY PLAN -------------------------------------------------------------------------------------------- - Update on ab_a1 (actual rows=0 loops=1) - Update on ab_a1_b1 ab_a1_1 - Update on ab_a1_b2 ab_a1_2 - Update on ab_a1_b3 ab_a1_3 - -> Nested Loop (actual rows=1 loops=1) - -> Append (actual rows=1 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) - Recheck Cond: (a = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Materialize (actual rows=1 loops=1) - -> Append (actual rows=1 loops=1) - -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) - Recheck Cond: (a = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) - -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) - Recheck Cond: (a = 1) - -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) - Index Cond: (a = 1) -(34 rows) - -table ab; - a | b ----+--- - 1 | 3 -(1 row) - --- Test UPDATE where source relation has run-time pruning enabled -truncate ab; -insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); -explain (analyze, costs off, summary off, timing off) -update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); - QUERY PLAN ------------------------------------------------------------------------------- - Update on ab_a1 (actual rows=0 loops=1) - Update on ab_a1_b1 ab_a1_1 - Update on ab_a1_b2 ab_a1_2 - Update on ab_a1_b3 ab_a1_3 - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Nested Loop (actual rows=3 loops=1) - -> Append (actual rows=3 loops=1) - -> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1 loops=1) - -> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) - -> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1) - -> Materialize (actual rows=1 loops=3) - -> Append (actual rows=1 loops=1) - -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1) - Filter: (b = $0) - -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) - Filter: (b = $0) - -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) - Filter: (b = $0) -(19 rows) - -select tableoid::regclass, * from ab; - tableoid | a | b -----------+---+--- - ab_a1_b3 | 1 | 3 - ab_a1_b3 | 1 | 3 - ab_a1_b3 | 1 | 3 - ab_a2_b1 | 2 | 1 -(4 rows) - -drop table ab, lprt_a; --- Join -create table tbl1(col1 int); -insert into tbl1 values (501), (505); --- Basic table -create table tprt (col1 int) partition by range (col1); -create table tprt_1 partition of tprt for values from (1) to (501); -create table tprt_2 partition of tprt for values from (501) to (1001); -create table tprt_3 partition of tprt for values from (1001) to (2001); -create table tprt_4 partition of tprt for values from (2001) to (3001); -create table tprt_5 partition of tprt for values from (3001) to (4001); -create table tprt_6 partition of tprt for values from (4001) to (5001); -create index tprt1_idx on tprt_1 (col1); -create index tprt2_idx on tprt_2 (col1); -create index tprt3_idx on tprt_3 (col1); -create index tprt4_idx on tprt_4 (col1); -create index tprt5_idx on tprt_5 (col1); -create index tprt6_idx on tprt_6 (col1); -insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); -set enable_hashjoin = off; -set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) -select * from tbl1 join tprt on tbl1.col1 > tprt.col1; - QUERY PLAN --------------------------------------------------------------------------- - Nested Loop (actual rows=6 loops=1) - -> Seq Scan on tbl1 (actual rows=2 loops=1) - -> Append (actual rows=3 loops=2) - -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt3_idx on tprt_3 (never executed) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt4_idx on tprt_4 (never executed) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt5_idx on tprt_5 (never executed) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt6_idx on tprt_6 (never executed) - Index Cond: (col1 < tbl1.col1) -(15 rows) - -explain (analyze, costs off, summary off, timing off) -select * from tbl1 join tprt on tbl1.col1 = tprt.col1; - QUERY PLAN --------------------------------------------------------------------------- - Nested Loop (actual rows=2 loops=1) - -> Seq Scan on tbl1 (actual rows=2 loops=1) - -> Append (actual rows=1 loops=2) - -> Index Scan using tprt1_idx on tprt_1 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt3_idx on tprt_3 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt4_idx on tprt_4 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt5_idx on tprt_5 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt6_idx on tprt_6 (never executed) - Index Cond: (col1 = tbl1.col1) -(15 rows) - -select tbl1.col1, tprt.col1 from tbl1 -inner join tprt on tbl1.col1 > tprt.col1 -order by tbl1.col1, tprt.col1; - col1 | col1 -------+------ - 501 | 10 - 501 | 20 - 505 | 10 - 505 | 20 - 505 | 501 - 505 | 502 -(6 rows) - -select tbl1.col1, tprt.col1 from tbl1 -inner join tprt on tbl1.col1 = tprt.col1 -order by tbl1.col1, tprt.col1; - col1 | col1 -------+------ - 501 | 501 - 505 | 505 -(2 rows) - --- Multiple partitions -insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) -select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; - QUERY PLAN --------------------------------------------------------------------------- - Nested Loop (actual rows=23 loops=1) - -> Seq Scan on tbl1 (actual rows=5 loops=1) - -> Append (actual rows=5 loops=5) - -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt4_idx on tprt_4 (never executed) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt5_idx on tprt_5 (never executed) - Index Cond: (col1 < tbl1.col1) - -> Index Scan using tprt6_idx on tprt_6 (never executed) - Index Cond: (col1 < tbl1.col1) -(15 rows) - -explain (analyze, costs off, summary off, timing off) -select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; - QUERY PLAN --------------------------------------------------------------------------- - Nested Loop (actual rows=3 loops=1) - -> Seq Scan on tbl1 (actual rows=5 loops=1) - -> Append (actual rows=1 loops=5) - -> Index Scan using tprt1_idx on tprt_1 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt4_idx on tprt_4 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt5_idx on tprt_5 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt6_idx on tprt_6 (never executed) - Index Cond: (col1 = tbl1.col1) -(15 rows) - -select tbl1.col1, tprt.col1 from tbl1 -inner join tprt on tbl1.col1 > tprt.col1 -order by tbl1.col1, tprt.col1; - col1 | col1 -------+------ - 501 | 10 - 501 | 20 - 505 | 10 - 505 | 20 - 505 | 501 - 505 | 502 - 1001 | 10 - 1001 | 20 - 1001 | 501 - 1001 | 502 - 1001 | 505 - 1010 | 10 - 1010 | 20 - 1010 | 501 - 1010 | 502 - 1010 | 505 - 1010 | 1001 - 1011 | 10 - 1011 | 20 - 1011 | 501 - 1011 | 502 - 1011 | 505 - 1011 | 1001 -(23 rows) - -select tbl1.col1, tprt.col1 from tbl1 -inner join tprt on tbl1.col1 = tprt.col1 -order by tbl1.col1, tprt.col1; - col1 | col1 -------+------ - 501 | 501 - 505 | 505 - 1001 | 1001 -(3 rows) - --- Last partition -delete from tbl1; -insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) -select * from tbl1 join tprt on tbl1.col1 < tprt.col1; - QUERY PLAN --------------------------------------------------------------------------- - Nested Loop (actual rows=1 loops=1) - -> Seq Scan on tbl1 (actual rows=1 loops=1) - -> Append (actual rows=1 loops=1) - -> Index Scan using tprt1_idx on tprt_1 (never executed) - Index Cond: (col1 > tbl1.col1) - -> Index Scan using tprt2_idx on tprt_2 (never executed) - Index Cond: (col1 > tbl1.col1) - -> Index Scan using tprt3_idx on tprt_3 (never executed) - Index Cond: (col1 > tbl1.col1) - -> Index Scan using tprt4_idx on tprt_4 (never executed) - Index Cond: (col1 > tbl1.col1) - -> Index Scan using tprt5_idx on tprt_5 (never executed) - Index Cond: (col1 > tbl1.col1) - -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1) - Index Cond: (col1 > tbl1.col1) -(15 rows) - -select tbl1.col1, tprt.col1 from tbl1 -inner join tprt on tbl1.col1 < tprt.col1 -order by tbl1.col1, tprt.col1; - col1 | col1 -------+------ - 4400 | 4500 -(1 row) - --- No matching partition -delete from tbl1; -insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) -select * from tbl1 join tprt on tbl1.col1 = tprt.col1; - QUERY PLAN -------------------------------------------------------------------- - Nested Loop (actual rows=0 loops=1) - -> Seq Scan on tbl1 (actual rows=1 loops=1) - -> Append (actual rows=0 loops=1) - -> Index Scan using tprt1_idx on tprt_1 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt2_idx on tprt_2 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt3_idx on tprt_3 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt4_idx on tprt_4 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt5_idx on tprt_5 (never executed) - Index Cond: (col1 = tbl1.col1) - -> Index Scan using tprt6_idx on tprt_6 (never executed) - Index Cond: (col1 = tbl1.col1) -(15 rows) - -select tbl1.col1, tprt.col1 from tbl1 -inner join tprt on tbl1.col1 = tprt.col1 -order by tbl1.col1, tprt.col1; - col1 | col1 -------+------ -(0 rows) - -drop table tbl1, tprt; --- Test with columns defined in varying orders between each level -create table part_abc (a int not null, b int not null, c int not null) partition by list (a); -create table part_bac (b int not null, a int not null, c int not null) partition by list (b); -create table part_cab (c int not null, a int not null, b int not null) partition by list (c); -create table part_abc_p1 (a int not null, b int not null, c int not null); -alter table part_abc attach partition part_bac for values in(1); -alter table part_bac attach partition part_cab for values in(2); -alter table part_cab attach partition part_abc_p1 for values in(3); -prepare part_abc_q1 (int, int, int) as -select * from part_abc where a = $1 and b = $2 and c = $3; --- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); - QUERY PLAN ----------------------------------------------------------- - Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) - Filter: ((a = $1) AND (b = $2) AND (c = $3)) -(2 rows) - -deallocate part_abc_q1; -drop table part_abc; --- Ensure that an Append node properly handles a sub-partitioned table --- matching without any of its leaf partitions matching the clause. -create table listp (a int, b int) partition by list (a); -create table listp_1 partition of listp for values in(1) partition by list (b); -create table listp_1_1 partition of listp_1 for values in(1); -create table listp_2 partition of listp for values in(2) partition by list (b); -create table listp_2_1 partition of listp_2 for values in(2); -select * from listp where b = 1; - a | b ----+--- -(0 rows) - --- Ensure that an Append node properly can handle selection of all first level --- partitions before finally detecting the correct set of 2nd level partitions --- which match the given parameter. -prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); - QUERY PLAN -------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 1 - -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1) - Filter: (b = ANY (ARRAY[$1, $2])) -(4 rows) - -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); - QUERY PLAN -------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 1 - -> Seq Scan on listp_2_1 listp_1 (actual rows=0 loops=1) - Filter: (b = ANY (ARRAY[$1, $2])) -(4 rows) - --- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); - QUERY PLAN --------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 2 -(2 rows) - -deallocate q1; --- Test more complex cases where a not-equal condition further eliminates partitions. -prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; --- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); - QUERY PLAN -------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 1 - -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1) - Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b)) -(4 rows) - --- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); - QUERY PLAN --------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 2 -(2 rows) - --- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) -select * from listp where a = (select null::int); - QUERY PLAN ------------------------------------------------------- - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Seq Scan on listp_1_1 listp_1 (never executed) - Filter: (a = $0) - -> Seq Scan on listp_2_1 listp_2 (never executed) - Filter: (a = $0) -(7 rows) - -drop table listp; --- --- check that stable query clauses are only used in run-time pruning --- -create table stable_qual_pruning (a timestamp) partition by range (a); -create table stable_qual_pruning1 partition of stable_qual_pruning - for values from ('2000-01-01') to ('2000-02-01'); -create table stable_qual_pruning2 partition of stable_qual_pruning - for values from ('2000-02-01') to ('2000-03-01'); -create table stable_qual_pruning3 partition of stable_qual_pruning - for values from ('3000-02-01') to ('3000-03-01'); --- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning where a < localtimestamp; - QUERY PLAN --------------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 1 - -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1) - Filter: (a < LOCALTIMESTAMP) - -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1) - Filter: (a < LOCALTIMESTAMP) -(6 rows) - --- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; - QUERY PLAN --------------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 2 - -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1) - Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone) -(4 rows) - --- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning - where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); - QUERY PLAN --------------------------------- - Result (actual rows=0 loops=1) - One-Time Filter: false -(2 rows) - -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning - where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- - Seq Scan on stable_qual_pruning2 stable_qual_pruning (actual rows=0 loops=1) - Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) -(2 rows) - -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning - where a = any(array['2000-02-01', localtimestamp]::timestamp[]); - QUERY PLAN ------------------------------------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 2 - -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1) - Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) -(4 rows) - -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning - where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); - QUERY PLAN --------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 3 -(2 rows) - -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning - where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - Subplans Removed: 2 - -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1) - Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) -(4 rows) - -explain (analyze, costs off, summary off, timing off) -select * from stable_qual_pruning - where a = any(null::timestamptz[]); - QUERY PLAN --------------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1) - Filter: (a = ANY (NULL::timestamp with time zone[])) - -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1) - Filter: (a = ANY (NULL::timestamp with time zone[])) - -> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0 loops=1) - Filter: (a = ANY (NULL::timestamp with time zone[])) -(7 rows) - -drop table stable_qual_pruning; --- --- Check that pruning with composite range partitioning works correctly when --- it must ignore clauses for trailing keys once it has seen a clause with --- non-inclusive operator for an earlier key --- -create table mc3p (a int, b int, c int) partition by range (a, abs(b), c); -create table mc3p0 partition of mc3p - for values from (0, 0, 0) to (0, maxvalue, maxvalue); -create table mc3p1 partition of mc3p - for values from (1, 1, 1) to (2, minvalue, minvalue); -create table mc3p2 partition of mc3p - for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); -insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) -select * from mc3p where a < 3 and abs(b) = 1; - QUERY PLAN --------------------------------------------------------- - Append (actual rows=3 loops=1) - -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1) - Filter: ((a < 3) AND (abs(b) = 1)) - -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1) - Filter: ((a < 3) AND (abs(b) = 1)) - -> Seq Scan on mc3p2 mc3p_3 (actual rows=1 loops=1) - Filter: ((a < 3) AND (abs(b) = 1)) -(7 rows) - --- --- Check that pruning with composite range partitioning works correctly when --- a combination of runtime parameters is specified, not all of whose values --- are available at the same time --- -prepare ps1 as - select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) -execute ps1(1); - QUERY PLAN --------------------------------------------------------- - Append (actual rows=1 loops=1) - Subplans Removed: 2 - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Seq Scan on mc3p1 mc3p_1 (actual rows=1 loops=1) - Filter: ((a = $1) AND (abs(b) < $0)) -(6 rows) - -deallocate ps1; -prepare ps2 as - select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) -execute ps2(1); - QUERY PLAN --------------------------------------------------------- - Append (actual rows=2 loops=1) - Subplans Removed: 1 - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1) - Filter: ((a <= $1) AND (abs(b) < $0)) - -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1) - Filter: ((a <= $1) AND (abs(b) < $0)) -(8 rows) - -deallocate ps2; -drop table mc3p; --- Ensure runtime pruning works with initplans params with boolean types -create table boolvalues (value bool not null); -insert into boolvalues values('t'),('f'); -create table boolp (a bool) partition by list (a); -create table boolp_t partition of boolp for values in('t'); -create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) -select * from boolp where a = (select value from boolvalues where value); - QUERY PLAN ------------------------------------------------------------ - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Seq Scan on boolvalues (actual rows=1 loops=1) - Filter: value - Rows Removed by Filter: 1 - -> Seq Scan on boolp_f boolp_1 (never executed) - Filter: (a = $0) - -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1) - Filter: (a = $0) -(9 rows) - -explain (analyze, costs off, summary off, timing off) -select * from boolp where a = (select value from boolvalues where not value); - QUERY PLAN ------------------------------------------------------------ - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Seq Scan on boolvalues (actual rows=1 loops=1) - Filter: (NOT value) - Rows Removed by Filter: 1 - -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1) - Filter: (a = $0) - -> Seq Scan on boolp_t boolp_2 (never executed) - Filter: (a = $0) -(9 rows) - -drop table boolp; --- --- Test run-time pruning of MergeAppend subnodes --- -set enable_seqscan = off; -set enable_sort = off; -create table ma_test (a int, b int) partition by range (a); -create table ma_test_p1 partition of ma_test for values from (0) to (10); -create table ma_test_p2 partition of ma_test for values from (10) to (20); -create table ma_test_p3 partition of ma_test for values from (20) to (30); -insert into ma_test select x,x from generate_series(0,29) t(x); -create index on ma_test (b); -analyze ma_test; -prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); - QUERY PLAN ------------------------------------------------------------------------------------------ - Merge Append (actual rows=2 loops=1) - Sort Key: ma_test.b - Subplans Removed: 1 - -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) - Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 - -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) - Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) - -execute mt_q1(15); - a ----- - 15 - 25 -(2 rows) - -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); - QUERY PLAN ------------------------------------------------------------------------------------------ - Merge Append (actual rows=1 loops=1) - Sort Key: ma_test.b - Subplans Removed: 2 - -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) - Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) - -execute mt_q1(25); - a ----- - 25 -(1 row) - --- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); - QUERY PLAN --------------------------------------- - Merge Append (actual rows=0 loops=1) - Sort Key: ma_test.b - Subplans Removed: 3 -(3 rows) - -execute mt_q1(35); - a ---- -(0 rows) - -deallocate mt_q1; -prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; --- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); - QUERY PLAN --------------------------------------------- - Limit (actual rows=0 loops=1) - Output: ma_test.a, ma_test.b - -> Merge Append (actual rows=0 loops=1) - Sort Key: ma_test.b - Subplans Removed: 3 -(5 rows) - -deallocate mt_q2; --- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; - QUERY PLAN ------------------------------------------------------------------------------------------------ - Merge Append (actual rows=20 loops=1) - Sort Key: ma_test.b - InitPlan 2 (returns $1) - -> Result (actual rows=1 loops=1) - InitPlan 1 (returns $0) - -> Limit (actual rows=1 loops=1) - -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1) - Index Cond: (b IS NOT NULL) - -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) - Filter: (a >= $1) - -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1) - Filter: (a >= $1) - -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1) - Filter: (a >= $1) -(14 rows) - -reset enable_seqscan; -reset enable_sort; -drop table ma_test; -reset enable_indexonlyscan; --- --- check that pruning works properly when the partition key is of a --- pseudotype --- --- array type list partition key -create table pp_arrpart (a int[]) partition by list (a); -create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); -create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); -explain (costs off) select * from pp_arrpart where a = '{1}'; - QUERY PLAN ------------------------------------- - Seq Scan on pp_arrpart1 pp_arrpart - Filter: (a = '{1}'::integer[]) -(2 rows) - -explain (costs off) select * from pp_arrpart where a = '{1, 2}'; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); - QUERY PLAN ----------------------------------------------------------------------- - Append - -> Seq Scan on pp_arrpart1 pp_arrpart_1 - Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) - -> Seq Scan on pp_arrpart2 pp_arrpart_2 - Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) -(5 rows) - -explain (costs off) update pp_arrpart set a = a where a = '{1}'; - QUERY PLAN --------------------------------------------- - Update on pp_arrpart - Update on pp_arrpart1 pp_arrpart_1 - -> Seq Scan on pp_arrpart1 pp_arrpart_1 - Filter: (a = '{1}'::integer[]) -(4 rows) - -explain (costs off) delete from pp_arrpart where a = '{1}'; - QUERY PLAN --------------------------------------------- - Delete on pp_arrpart - Delete on pp_arrpart1 pp_arrpart_1 - -> Seq Scan on pp_arrpart1 pp_arrpart_1 - Filter: (a = '{1}'::integer[]) -(4 rows) - -drop table pp_arrpart; --- array type hash partition key -create table pph_arrpart (a int[]) partition by hash (a); -create table pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0); -create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1); -insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}'); -select tableoid::regclass, * from pph_arrpart order by 1; - tableoid | a ---------------+------- - pph_arrpart1 | {1,2} - pph_arrpart1 | {4,5} - pph_arrpart2 | {1} -(3 rows) - -explain (costs off) select * from pph_arrpart where a = '{1}'; - QUERY PLAN --------------------------------------- - Seq Scan on pph_arrpart2 pph_arrpart - Filter: (a = '{1}'::integer[]) -(2 rows) - -explain (costs off) select * from pph_arrpart where a = '{1, 2}'; - QUERY PLAN --------------------------------------- - Seq Scan on pph_arrpart1 pph_arrpart - Filter: (a = '{1,2}'::integer[]) -(2 rows) - -explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}'); - QUERY PLAN ----------------------------------------------------------------------- - Append - -> Seq Scan on pph_arrpart1 pph_arrpart_1 - Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) - -> Seq Scan on pph_arrpart2 pph_arrpart_2 - Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) -(5 rows) - -drop table pph_arrpart; --- enum type list partition key -create type pp_colors as enum ('green', 'blue', 'black'); -create table pp_enumpart (a pp_colors) partition by list (a); -create table pp_enumpart_green partition of pp_enumpart for values in ('green'); -create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); -explain (costs off) select * from pp_enumpart where a = 'blue'; - QUERY PLAN ------------------------------------------- - Seq Scan on pp_enumpart_blue pp_enumpart - Filter: (a = 'blue'::pp_colors) -(2 rows) - -explain (costs off) select * from pp_enumpart where a = 'black'; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -drop table pp_enumpart; -drop type pp_colors; --- record type as partition key -create type pp_rectype as (a int, b int); -create table pp_recpart (a pp_rectype) partition by list (a); -create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); -create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); -explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; - QUERY PLAN --------------------------------------- - Seq Scan on pp_recpart_11 pp_recpart - Filter: (a = '(1,1)'::pp_rectype) -(2 rows) - -explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -drop table pp_recpart; -drop type pp_rectype; --- range type partition key -create table pp_intrangepart (a int4range) partition by list (a); -create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); -create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); -explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; - QUERY PLAN ------------------------------------------------ - Seq Scan on pp_intrangepart12 pp_intrangepart - Filter: (a = '[1,3)'::int4range) -(2 rows) - -explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -drop table pp_intrangepart; --- --- Ensure the enable_partition_prune GUC properly disables partition pruning. --- -create table pp_lp (a int, value int) partition by list (a); -create table pp_lp1 partition of pp_lp for values in(1); -create table pp_lp2 partition of pp_lp for values in(2); -explain (costs off) select * from pp_lp where a = 1; - QUERY PLAN --------------------------- - Seq Scan on pp_lp1 pp_lp - Filter: (a = 1) -(2 rows) - -explain (costs off) update pp_lp set value = 10 where a = 1; - QUERY PLAN ----------------------------------- - Update on pp_lp - Update on pp_lp1 pp_lp_1 - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) -(4 rows) - -explain (costs off) delete from pp_lp where a = 1; - QUERY PLAN ----------------------------------- - Delete on pp_lp - Delete on pp_lp1 pp_lp_1 - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) -(4 rows) - -set enable_partition_pruning = off; -set constraint_exclusion = 'partition'; -- this should not affect the result. -explain (costs off) select * from pp_lp where a = 1; - QUERY PLAN ----------------------------------- - Append - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) - -> Seq Scan on pp_lp2 pp_lp_2 - Filter: (a = 1) -(5 rows) - -explain (costs off) update pp_lp set value = 10 where a = 1; - QUERY PLAN ----------------------------------------- - Update on pp_lp - Update on pp_lp1 pp_lp_1 - Update on pp_lp2 pp_lp_2 - -> Append - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) - -> Seq Scan on pp_lp2 pp_lp_2 - Filter: (a = 1) -(8 rows) - -explain (costs off) delete from pp_lp where a = 1; - QUERY PLAN ----------------------------------------- - Delete on pp_lp - Delete on pp_lp1 pp_lp_1 - Delete on pp_lp2 pp_lp_2 - -> Append - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) - -> Seq Scan on pp_lp2 pp_lp_2 - Filter: (a = 1) -(8 rows) - -set constraint_exclusion = 'off'; -- this should not affect the result. -explain (costs off) select * from pp_lp where a = 1; - QUERY PLAN ----------------------------------- - Append - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) - -> Seq Scan on pp_lp2 pp_lp_2 - Filter: (a = 1) -(5 rows) - -explain (costs off) update pp_lp set value = 10 where a = 1; - QUERY PLAN ----------------------------------------- - Update on pp_lp - Update on pp_lp1 pp_lp_1 - Update on pp_lp2 pp_lp_2 - -> Append - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) - -> Seq Scan on pp_lp2 pp_lp_2 - Filter: (a = 1) -(8 rows) - -explain (costs off) delete from pp_lp where a = 1; - QUERY PLAN ----------------------------------------- - Delete on pp_lp - Delete on pp_lp1 pp_lp_1 - Delete on pp_lp2 pp_lp_2 - -> Append - -> Seq Scan on pp_lp1 pp_lp_1 - Filter: (a = 1) - -> Seq Scan on pp_lp2 pp_lp_2 - Filter: (a = 1) -(8 rows) - -drop table pp_lp; --- Ensure enable_partition_prune does not affect non-partitioned tables. -create table inh_lp (a int, value int); -create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp); -NOTICE: merging column "a" with inherited definition -NOTICE: merging column "value" with inherited definition -create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp); -NOTICE: merging column "a" with inherited definition -NOTICE: merging column "value" with inherited definition -set constraint_exclusion = 'partition'; --- inh_lp2 should be removed in the following 3 cases. -explain (costs off) select * from inh_lp where a = 1; - QUERY PLAN ------------------------------------- - Append - -> Seq Scan on inh_lp inh_lp_1 - Filter: (a = 1) - -> Seq Scan on inh_lp1 inh_lp_2 - Filter: (a = 1) -(5 rows) - -explain (costs off) update inh_lp set value = 10 where a = 1; - QUERY PLAN ------------------------------------------------- - Update on inh_lp - Update on inh_lp inh_lp_1 - Update on inh_lp1 inh_lp_2 - -> Result - -> Append - -> Seq Scan on inh_lp inh_lp_1 - Filter: (a = 1) - -> Seq Scan on inh_lp1 inh_lp_2 - Filter: (a = 1) -(9 rows) - -explain (costs off) delete from inh_lp where a = 1; - QUERY PLAN ------------------------------------------- - Delete on inh_lp - Delete on inh_lp inh_lp_1 - Delete on inh_lp1 inh_lp_2 - -> Append - -> Seq Scan on inh_lp inh_lp_1 - Filter: (a = 1) - -> Seq Scan on inh_lp1 inh_lp_2 - Filter: (a = 1) -(8 rows) - --- Ensure we don't exclude normal relations when we only expect to exclude --- inheritance children -explain (costs off) update inh_lp1 set value = 10 where a = 2; - QUERY PLAN ---------------------------- - Update on inh_lp1 - -> Seq Scan on inh_lp1 - Filter: (a = 2) -(3 rows) - -drop table inh_lp cascade; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to table inh_lp1 -drop cascades to table inh_lp2 -reset enable_partition_pruning; -reset constraint_exclusion; --- Check pruning for a partition tree containing only temporary relations -create temp table pp_temp_parent (a int) partition by list (a); -create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1); -create temp table pp_temp_part_def partition of pp_temp_parent default; -explain (costs off) select * from pp_temp_parent where true; - QUERY PLAN ------------------------------------------------------ - Append - -> Seq Scan on pp_temp_part_1 pp_temp_parent_1 - -> Seq Scan on pp_temp_part_def pp_temp_parent_2 -(3 rows) - -explain (costs off) select * from pp_temp_parent where a = 2; - QUERY PLAN ---------------------------------------------- - Seq Scan on pp_temp_part_def pp_temp_parent - Filter: (a = 2) -(2 rows) - -drop table pp_temp_parent; --- Stress run-time partition pruning a bit more, per bug reports -create temp table p (a int, b int, c int) partition by list (a); -create temp table p1 partition of p for values in (1); -create temp table p2 partition of p for values in (2); -create temp table q (a int, b int, c int) partition by list (a); -create temp table q1 partition of q for values in (1) partition by list (b); -create temp table q11 partition of q1 for values in (1) partition by list (c); -create temp table q111 partition of q11 for values in (1); -create temp table q2 partition of q for values in (2) partition by list (b); -create temp table q21 partition of q2 for values in (1); -create temp table q22 partition of q2 for values in (2); -insert into q22 values (2, 2, 3); -explain (costs off) -select * -from ( - select * from p - union all - select * from q1 - union all - select 1, 1, 1 - ) s(a, b, c) -where s.a = 1 and s.b = 1 and s.c = (select 1); - QUERY PLAN ----------------------------------------------------- - Append - InitPlan 1 (returns $0) - -> Result - -> Seq Scan on p1 p - Filter: ((a = 1) AND (b = 1) AND (c = $0)) - -> Seq Scan on q111 q1 - Filter: ((a = 1) AND (b = 1) AND (c = $0)) - -> Result - One-Time Filter: (1 = $0) -(9 rows) - -select * -from ( - select * from p - union all - select * from q1 - union all - select 1, 1, 1 - ) s(a, b, c) -where s.a = 1 and s.b = 1 and s.c = (select 1); - a | b | c ----+---+--- - 1 | 1 | 1 -(1 row) - -prepare q (int, int) as -select * -from ( - select * from p - union all - select * from q1 - union all - select 1, 1, 1 - ) s(a, b, c) -where s.a = $1 and s.b = $2 and s.c = (select 1); -explain (costs off) execute q (1, 1); - QUERY PLAN ---------------------------------------------------------------- - Append - Subplans Removed: 1 - InitPlan 1 (returns $0) - -> Result - -> Seq Scan on p1 p - Filter: ((a = $1) AND (b = $2) AND (c = $0)) - -> Seq Scan on q111 q1 - Filter: ((a = $1) AND (b = $2) AND (c = $0)) - -> Result - One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = $0)) -(10 rows) - -execute q (1, 1); - a | b | c ----+---+--- - 1 | 1 | 1 -(1 row) - -drop table p, q; --- Ensure run-time pruning works correctly when we match a partitioned table --- on the first level but find no matching partitions on the second level. -create table listp (a int, b int) partition by list (a); -create table listp1 partition of listp for values in(1); -create table listp2 partition of listp for values in(2) partition by list(b); -create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) -select * from listp where a = (select 2) and b <> 10; - QUERY PLAN --------------------------------------------------- - Seq Scan on listp1 listp (actual rows=0 loops=1) - Filter: ((b <> 10) AND (a = $0)) - InitPlan 1 (returns $0) - -> Result (never executed) -(4 rows) - --- --- check that a partition directly accessed in a query is excluded with --- constraint_exclusion = on --- --- turn off partition pruning, so that it doesn't interfere -set enable_partition_pruning to off; --- setting constraint_exclusion to 'partition' disables exclusion -set constraint_exclusion to 'partition'; -explain (costs off) select * from listp1 where a = 2; - QUERY PLAN --------------------- - Seq Scan on listp1 - Filter: (a = 2) -(2 rows) - -explain (costs off) update listp1 set a = 1 where a = 2; - QUERY PLAN --------------------------- - Update on listp1 - -> Seq Scan on listp1 - Filter: (a = 2) -(3 rows) - --- constraint exclusion enabled -set constraint_exclusion to 'on'; -explain (costs off) select * from listp1 where a = 2; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -explain (costs off) update listp1 set a = 1 where a = 2; - QUERY PLAN --------------------------------- - Update on listp1 - -> Result - One-Time Filter: false -(3 rows) - -reset constraint_exclusion; -reset enable_partition_pruning; -drop table listp; --- Ensure run-time pruning works correctly for nested Append nodes -set parallel_setup_cost to 0; -set parallel_tuple_cost to 0; -create table listp (a int) partition by list(a); -create table listp_12 partition of listp for values in(1,2) partition by list(a); -create table listp_12_1 partition of listp_12 for values in(1); -create table listp_12_2 partition of listp_12 for values in(2); --- Force the 2nd subnode of the Append to be non-parallel. This results in --- a nested Append node because the mixed parallel / non-parallel paths cannot --- be pulled into the top-level Append. -alter table listp_12_1 set (parallel_workers = 0); --- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in --- the plan as it's pulled in setref.c due to having just a single subnode). -select explain_parallel_append('select * from listp where a = (select 1);'); - explain_parallel_append ----------------------------------------------------------------------- - Gather (actual rows=N loops=N) - Workers Planned: 2 - Params Evaluated: $0 - Workers Launched: N - InitPlan 1 (returns $0) - -> Result (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) - Filter: (a = $0) - -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) - Filter: (a = $0) -(11 rows) - --- Like the above but throw some more complexity at the planner by adding --- a UNION ALL. We expect both sides of the union not to scan the --- non-required partitions. -select explain_parallel_append( -'select * from listp where a = (select 1) - union all -select * from listp where a = (select 2);'); - explain_parallel_append ------------------------------------------------------------------------------------ - Gather (actual rows=N loops=N) - Workers Planned: 2 - Workers Launched: N - -> Parallel Append (actual rows=N loops=N) - -> Parallel Append (actual rows=N loops=N) - InitPlan 2 (returns $1) - -> Result (actual rows=N loops=N) - -> Seq Scan on listp_12_1 listp_1 (never executed) - Filter: (a = $1) - -> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=N loops=N) - Filter: (a = $1) - -> Parallel Append (actual rows=N loops=N) - InitPlan 1 (returns $0) - -> Result (actual rows=N loops=N) - -> Seq Scan on listp_12_1 listp_4 (actual rows=N loops=N) - Filter: (a = $0) - -> Parallel Seq Scan on listp_12_2 listp_5 (never executed) - Filter: (a = $0) -(18 rows) - -drop table listp; -reset parallel_tuple_cost; -reset parallel_setup_cost; --- Test case for run-time pruning with a nested Merge Append -set enable_sort to 0; -create table rangep (a int, b int) partition by range (a); -create table rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b); --- We need 3 sub-partitions. 1 to validate pruning worked and another two --- because a single remaining partition would be pulled up to the main Append. -create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1); -create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2); -create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); -create table rangep_100_to_200 partition of rangep for values from (100) to (200); -create index on rangep (a); --- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) -select * from rangep where b IN((select 1),(select 2)) order by a; - QUERY PLAN ------------------------------------------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - InitPlan 1 (returns $0) - -> Result (actual rows=1 loops=1) - InitPlan 2 (returns $1) - -> Result (actual rows=1 loops=1) - -> Merge Append (actual rows=0 loops=1) - Sort Key: rangep_2.a - -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1) - Filter: (b = ANY (ARRAY[$0, $1])) - -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1) - Filter: (b = ANY (ARRAY[$0, $1])) - -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed) - Filter: (b = ANY (ARRAY[$0, $1])) - -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1) - Filter: (b = ANY (ARRAY[$0, $1])) -(15 rows) - -reset enable_sort; -drop table rangep; --- --- Check that gen_prune_steps_from_opexps() works well for various cases of --- clauses for different partition keys --- -create table rp_prefix_test1 (a int, b varchar) partition by range(a, b); -create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b'); -create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b'); --- Don't call get_steps_using_prefix() with the last partition key b plus --- an empty prefix -explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a'; - QUERY PLAN --------------------------------------------------- - Seq Scan on rp_prefix_test1_p1 rp_prefix_test1 - Filter: ((a <= 1) AND ((b)::text = 'a'::text)) -(2 rows) - -create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c); -create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10); -create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10); --- Don't call get_steps_using_prefix() with the last partition key c plus --- an invalid prefix (ie, b = 1) -explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0; - QUERY PLAN ------------------------------------------------- - Seq Scan on rp_prefix_test2_p1 rp_prefix_test2 - Filter: ((a <= 1) AND (c >= 0) AND (b = 1)) -(2 rows) - -create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d); -create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10); -create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10); --- Test that get_steps_using_prefix() handles a prefix that contains multiple --- clauses for the partition key b (ie, b >= 1 and b >= 2) -explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0; - QUERY PLAN --------------------------------------------------------------------------- - Seq Scan on rp_prefix_test3_p2 rp_prefix_test3 - Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0)) -(2 rows) - --- Test that get_steps_using_prefix() handles a prefix that contains multiple --- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests --- that the caller arranges clauses in that prefix in the required order) -explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0; - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on rp_prefix_test3_p2 rp_prefix_test3 - Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2)) -(2 rows) - -drop table rp_prefix_test1; -drop table rp_prefix_test2; -drop table rp_prefix_test3; --- --- Test that get_steps_using_prefix() handles IS NULL clauses correctly --- -create table hp_prefix_test (a int, b int, c int, d int) - partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops); --- create 8 partitions -select 'create table hp_prefix_test_p' || x::text || ' partition of hp_prefix_test for values with (modulus 8, remainder ' || x::text || ');' -from generate_Series(0,7) x; - ?column? ------------------------------------------------------------------------------------------------------- - create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0); - create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1); - create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2); - create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3); - create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4); - create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5); - create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6); - create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7); -(8 rows) - -\gexec -create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0); -create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1); -create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2); -create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3); -create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4); -create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5); -create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6); -create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7); --- insert 16 rows, one row for each test to perform. -insert into hp_prefix_test -select - case a when 0 then null else 1 end, - case b when 0 then null else 2 end, - case c when 0 then null else 3 end, - case d when 0 then null else 4 end -from - generate_series(0,1) a, - generate_series(0,1) b, - generate_Series(0,1) c, - generate_Series(0,1) d; --- Ensure partition pruning works correctly for each combination of IS NULL --- and equality quals. This may seem a little excessive, but there have been --- a number of bugs in this area over the years. We make use of row only --- output to reduce the size of the expected results. -\t on -select - 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where ' || - string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos) -from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s) -group by g.s -order by g.s; - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 - explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 - -\gexec -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null - Seq Scan on hp_prefix_test_p0 hp_prefix_test - Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d IS NULL)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null - Seq Scan on hp_prefix_test_p1 hp_prefix_test - Filter: ((b IS NULL) AND (c IS NULL) AND (d IS NULL) AND (a = 1)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null - Seq Scan on hp_prefix_test_p2 hp_prefix_test - Filter: ((a IS NULL) AND (c IS NULL) AND (d IS NULL) AND (b = 2)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null - Seq Scan on hp_prefix_test_p4 hp_prefix_test - Filter: ((c IS NULL) AND (d IS NULL) AND (a = 1) AND (b = 2)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null - Seq Scan on hp_prefix_test_p3 hp_prefix_test - Filter: ((a IS NULL) AND (b IS NULL) AND (d IS NULL) AND (c = 3)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null - Seq Scan on hp_prefix_test_p7 hp_prefix_test - Filter: ((b IS NULL) AND (d IS NULL) AND (a = 1) AND (c = 3)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null - Seq Scan on hp_prefix_test_p4 hp_prefix_test - Filter: ((a IS NULL) AND (d IS NULL) AND (b = 2) AND (c = 3)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null - Seq Scan on hp_prefix_test_p5 hp_prefix_test - Filter: ((d IS NULL) AND (a = 1) AND (b = 2) AND (c = 3)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 - Seq Scan on hp_prefix_test_p4 hp_prefix_test - Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 - Seq Scan on hp_prefix_test_p6 hp_prefix_test - Filter: ((b IS NULL) AND (c IS NULL) AND (a = 1) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 - Seq Scan on hp_prefix_test_p5 hp_prefix_test - Filter: ((a IS NULL) AND (c IS NULL) AND (b = 2) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 - Seq Scan on hp_prefix_test_p6 hp_prefix_test - Filter: ((c IS NULL) AND (a = 1) AND (b = 2) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 - Seq Scan on hp_prefix_test_p4 hp_prefix_test - Filter: ((a IS NULL) AND (b IS NULL) AND (c = 3) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 - Seq Scan on hp_prefix_test_p5 hp_prefix_test - Filter: ((b IS NULL) AND (a = 1) AND (c = 3) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 - Seq Scan on hp_prefix_test_p6 hp_prefix_test - Filter: ((a IS NULL) AND (b = 2) AND (c = 3) AND (d = 4)) - -explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 - Seq Scan on hp_prefix_test_p4 hp_prefix_test - Filter: ((a = 1) AND (b = 2) AND (c = 3) AND (d = 4)) - --- And ensure we get exactly 1 row from each. Again, all 16 possible combinations. -select - 'select tableoid::regclass,* from hp_prefix_test where ' || - string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos) -from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s) -group by g.s -order by g.s; - select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null - select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null - select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null - select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null - select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null - select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null - select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null - select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null - select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 - select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 - select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 - select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 - select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 - select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 - select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 - select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 - -\gexec -select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null - hp_prefix_test_p0 | | | | - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null - hp_prefix_test_p1 | 1 | | | - -select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null - hp_prefix_test_p2 | | 2 | | - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null - hp_prefix_test_p4 | 1 | 2 | | - -select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null - hp_prefix_test_p3 | | | 3 | - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null - hp_prefix_test_p7 | 1 | | 3 | - -select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null - hp_prefix_test_p4 | | 2 | 3 | - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null - hp_prefix_test_p5 | 1 | 2 | 3 | - -select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4 - hp_prefix_test_p4 | | | | 4 - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4 - hp_prefix_test_p6 | 1 | | | 4 - -select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4 - hp_prefix_test_p5 | | 2 | | 4 - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4 - hp_prefix_test_p6 | 1 | 2 | | 4 - -select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4 - hp_prefix_test_p4 | | | 3 | 4 - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4 - hp_prefix_test_p5 | 1 | | 3 | 4 - -select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4 - hp_prefix_test_p6 | | 2 | 3 | 4 - -select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4 - hp_prefix_test_p4 | 1 | 2 | 3 | 4 - -\t off -drop table hp_prefix_test; --- --- Check that gen_partprune_steps() detects self-contradiction from clauses --- regardless of the order of the clauses (Here we use a custom operator to --- prevent the equivclass.c machinery from reordering the clauses) --- -create operator === ( - leftarg = int4, - rightarg = int4, - procedure = int4eq, - commutator = ===, - hashes -); -create operator class part_test_int4_ops2 -for type int4 -using hash as -operator 1 ===, -function 2 part_hashint4_noop(int4, int8); -create table hp_contradict_test (a int, b int) partition by hash (a part_test_int4_ops2, b part_test_int4_ops2); -create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0); -create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1); -explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null; - QUERY PLAN --------------------------- - Result - One-Time Filter: false -(2 rows) - -drop table hp_contradict_test; -drop operator class part_test_int4_ops2 using hash; -drop operator ===(int4, int4); +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -w -U3 C:/cirrus/src/test/regress/expected/partition_aggregate.out C:/cirrus/build/testrun/regress/regress/results/partition_aggregate.out --- C:/cirrus/src/test/regress/expected/partition_aggregate.out 2024-03-11 18:11:56.520666000 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/partition_aggregate.out 2024-03-11 18:15:28.938126800 +0000 @@ -339,1182 +339,7 @@ -- ROLLUP, partitionwise aggregation does not apply EXPLAIN (COSTS OFF) SELECT c, sum(a) FROM pagg_tab GROUP BY rollup(c) ORDER BY 1, 2; - QUERY PLAN ------------------------------------------------------- - Sort - Sort Key: pagg_tab.c, (sum(pagg_tab.a)) - -> MixedAggregate - Hash Key: pagg_tab.c - Group Key: () - -> Append - -> Seq Scan on pagg_tab_p1 pagg_tab_1 - -> Seq Scan on pagg_tab_p2 pagg_tab_2 - -> Seq Scan on pagg_tab_p3 pagg_tab_3 -(9 rows) - --- ORDERED SET within the aggregate. --- Full aggregation; since all the rows that belong to the same group come --- from the same partition, having an ORDER BY within the aggregate doesn't --- make any difference. -EXPLAIN (COSTS OFF) -SELECT c, sum(b order by a) FROM pagg_tab GROUP BY c ORDER BY 1, 2; - QUERY PLAN ---------------------------------------------------------------- - Sort - Sort Key: pagg_tab.c, (sum(pagg_tab.b ORDER BY pagg_tab.a)) - -> Append - -> GroupAggregate - Group Key: pagg_tab.c - -> Sort - Sort Key: pagg_tab.c, pagg_tab.a - -> Seq Scan on pagg_tab_p1 pagg_tab - -> GroupAggregate - Group Key: pagg_tab_1.c - -> Sort - Sort Key: pagg_tab_1.c, pagg_tab_1.a - -> Seq Scan on pagg_tab_p2 pagg_tab_1 - -> GroupAggregate - Group Key: pagg_tab_2.c - -> Sort - Sort Key: pagg_tab_2.c, pagg_tab_2.a - -> Seq Scan on pagg_tab_p3 pagg_tab_2 -(18 rows) - --- Since GROUP BY clause does not match with PARTITION KEY; we need to do --- partial aggregation. However, ORDERED SET are not partial safe and thus --- partitionwise aggregation plan is not generated. -EXPLAIN (COSTS OFF) -SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a ORDER BY 1, 2; - QUERY PLAN ---------------------------------------------------------------- - Sort - Sort Key: pagg_tab.a, (sum(pagg_tab.b ORDER BY pagg_tab.a)) - -> GroupAggregate - Group Key: pagg_tab.a - -> Sort - Sort Key: pagg_tab.a - -> Append - -> Seq Scan on pagg_tab_p1 pagg_tab_1 - -> Seq Scan on pagg_tab_p2 pagg_tab_2 - -> Seq Scan on pagg_tab_p3 pagg_tab_3 -(10 rows) - --- JOIN query -CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x); -CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10); -CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20); -CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30); -CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y); -CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10); -CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20); -CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30); -INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i; -INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i; -ANALYZE pagg_tab1; -ANALYZE pagg_tab2; --- When GROUP BY clause matches; full aggregation is performed for each partition. -EXPLAIN (COSTS OFF) -SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------- - Sort - Sort Key: t1.x, (sum(t1.y)), (count(*)) - -> Append - -> HashAggregate - Group Key: t1.x - -> Hash Join - Hash Cond: (t1.x = t2.y) - -> Seq Scan on pagg_tab1_p1 t1 - -> Hash - -> Seq Scan on pagg_tab2_p1 t2 - -> HashAggregate - Group Key: t1_1.x - -> Hash Join - Hash Cond: (t1_1.x = t2_1.y) - -> Seq Scan on pagg_tab1_p2 t1_1 - -> Hash - -> Seq Scan on pagg_tab2_p2 t2_1 - -> HashAggregate - Group Key: t1_2.x - -> Hash Join - Hash Cond: (t2_2.y = t1_2.x) - -> Seq Scan on pagg_tab2_p3 t2_2 - -> Hash - -> Seq Scan on pagg_tab1_p3 t1_2 -(24 rows) - -SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; - x | sum | count -----+------+------- - 0 | 500 | 100 - 6 | 1100 | 100 - 12 | 700 | 100 - 18 | 1300 | 100 - 24 | 900 | 100 -(5 rows) - --- Check with whole-row reference; partitionwise aggregation does not apply -EXPLAIN (COSTS OFF) -SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------- - Sort - Sort Key: t1.x, (sum(t1.y)), (count(((t1.*)::pagg_tab1))) - -> HashAggregate - Group Key: t1.x - -> Hash Join - Hash Cond: (t1.x = t2.y) - -> Append - -> Seq Scan on pagg_tab1_p1 t1_1 - -> Seq Scan on pagg_tab1_p2 t1_2 - -> Seq Scan on pagg_tab1_p3 t1_3 - -> Hash - -> Append - -> Seq Scan on pagg_tab2_p1 t2_1 - -> Seq Scan on pagg_tab2_p2 t2_2 - -> Seq Scan on pagg_tab2_p3 t2_3 -(15 rows) - -SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; - x | sum | count -----+------+------- - 0 | 500 | 100 - 6 | 1100 | 100 - 12 | 700 | 100 - 18 | 1300 | 100 - 24 | 900 | 100 -(5 rows) - --- GROUP BY having other matching key -EXPLAIN (COSTS OFF) -SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t2.y ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------- - Sort - Sort Key: t2.y, (sum(t1.y)), (count(*)) - -> Append - -> HashAggregate - Group Key: t2.y - -> Hash Join - Hash Cond: (t1.x = t2.y) - -> Seq Scan on pagg_tab1_p1 t1 - -> Hash - -> Seq Scan on pagg_tab2_p1 t2 - -> HashAggregate - Group Key: t2_1.y - -> Hash Join - Hash Cond: (t1_1.x = t2_1.y) - -> Seq Scan on pagg_tab1_p2 t1_1 - -> Hash - -> Seq Scan on pagg_tab2_p2 t2_1 - -> HashAggregate - Group Key: t2_2.y - -> Hash Join - Hash Cond: (t2_2.y = t1_2.x) - -> Seq Scan on pagg_tab2_p3 t2_2 - -> Hash - -> Seq Scan on pagg_tab1_p3 t1_2 -(24 rows) - --- When GROUP BY clause does not match; partial aggregation is performed for each partition. --- Also test GroupAggregate paths by disabling hash aggregates. -SET enable_hashagg TO false; -EXPLAIN (COSTS OFF) -SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------------------- - Sort - Sort Key: t1.y, (sum(t1.x)), (count(*)) - -> Finalize GroupAggregate - Group Key: t1.y - Filter: (avg(t1.x) > '10'::numeric) - -> Merge Append - Sort Key: t1.y - -> Partial GroupAggregate - Group Key: t1.y - -> Sort - Sort Key: t1.y - -> Hash Join - Hash Cond: (t1.x = t2.y) - -> Seq Scan on pagg_tab1_p1 t1 - -> Hash - -> Seq Scan on pagg_tab2_p1 t2 - -> Partial GroupAggregate - Group Key: t1_1.y - -> Sort - Sort Key: t1_1.y - -> Hash Join - Hash Cond: (t1_1.x = t2_1.y) - -> Seq Scan on pagg_tab1_p2 t1_1 - -> Hash - -> Seq Scan on pagg_tab2_p2 t2_1 - -> Partial GroupAggregate - Group Key: t1_2.y - -> Sort - Sort Key: t1_2.y - -> Hash Join - Hash Cond: (t2_2.y = t1_2.x) - -> Seq Scan on pagg_tab2_p3 t2_2 - -> Hash - -> Seq Scan on pagg_tab1_p3 t1_2 -(34 rows) - -SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; - y | sum | count -----+------+------- - 2 | 600 | 50 - 4 | 1200 | 50 - 8 | 900 | 50 - 12 | 600 | 50 - 14 | 1200 | 50 - 18 | 900 | 50 -(6 rows) - -RESET enable_hashagg; --- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for --- aggregation --- LEFT JOIN, should produce partial partitionwise aggregation plan as --- GROUP BY is on nullable column -EXPLAIN (COSTS OFF) -SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; - QUERY PLAN ------------------------------------------------------------------- - Finalize GroupAggregate - Group Key: b.y - -> Sort - Sort Key: b.y - -> Append - -> Partial HashAggregate - Group Key: b.y - -> Hash Left Join - Hash Cond: (a.x = b.y) - -> Seq Scan on pagg_tab1_p1 a - -> Hash - -> Seq Scan on pagg_tab2_p1 b - -> Partial HashAggregate - Group Key: b_1.y - -> Hash Left Join - Hash Cond: (a_1.x = b_1.y) - -> Seq Scan on pagg_tab1_p2 a_1 - -> Hash - -> Seq Scan on pagg_tab2_p2 b_1 - -> Partial HashAggregate - Group Key: b_2.y - -> Hash Right Join - Hash Cond: (b_2.y = a_2.x) - -> Seq Scan on pagg_tab2_p3 b_2 - -> Hash - -> Seq Scan on pagg_tab1_p3 a_2 -(26 rows) - -SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; - y | sum -----+------ - 0 | 500 - 6 | 1100 - 12 | 700 - 18 | 1300 - 24 | 900 - | 900 -(6 rows) - --- RIGHT JOIN, should produce full partitionwise aggregation plan as --- GROUP BY is on non-nullable column -EXPLAIN (COSTS OFF) -SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; - QUERY PLAN ------------------------------------------------------------- - Sort - Sort Key: b.y - -> Append - -> HashAggregate - Group Key: b.y - -> Hash Right Join - Hash Cond: (a.x = b.y) - -> Seq Scan on pagg_tab1_p1 a - -> Hash - -> Seq Scan on pagg_tab2_p1 b - -> HashAggregate - Group Key: b_1.y - -> Hash Right Join - Hash Cond: (a_1.x = b_1.y) - -> Seq Scan on pagg_tab1_p2 a_1 - -> Hash - -> Seq Scan on pagg_tab2_p2 b_1 - -> HashAggregate - Group Key: b_2.y - -> Hash Left Join - Hash Cond: (b_2.y = a_2.x) - -> Seq Scan on pagg_tab2_p3 b_2 - -> Hash - -> Seq Scan on pagg_tab1_p3 a_2 -(24 rows) - -SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; - y | sum -----+------ - 0 | 500 - 3 | - 6 | 1100 - 9 | - 12 | 700 - 15 | - 18 | 1300 - 21 | - 24 | 900 - 27 | -(10 rows) - --- FULL JOIN, should produce partial partitionwise aggregation plan as --- GROUP BY is on nullable column -EXPLAIN (COSTS OFF) -SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST; - QUERY PLAN ------------------------------------------------------------------- - Finalize GroupAggregate - Group Key: a.x - -> Sort - Sort Key: a.x - -> Append - -> Partial HashAggregate - Group Key: a.x - -> Hash Full Join - Hash Cond: (a.x = b.y) - -> Seq Scan on pagg_tab1_p1 a - -> Hash - -> Seq Scan on pagg_tab2_p1 b - -> Partial HashAggregate - Group Key: a_1.x - -> Hash Full Join - Hash Cond: (a_1.x = b_1.y) - -> Seq Scan on pagg_tab1_p2 a_1 - -> Hash - -> Seq Scan on pagg_tab2_p2 b_1 - -> Partial HashAggregate - Group Key: a_2.x - -> Hash Full Join - Hash Cond: (b_2.y = a_2.x) - -> Seq Scan on pagg_tab2_p3 b_2 - -> Hash - -> Seq Scan on pagg_tab1_p3 a_2 -(26 rows) - -SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST; - x | sum -----+------ - 0 | 500 - 2 | - 4 | - 6 | 1100 - 8 | - 10 | - 12 | 700 - 14 | - 16 | - 18 | 1300 - 20 | - 22 | - 24 | 900 - 26 | - 28 | - | 500 -(16 rows) - --- LEFT JOIN, with dummy relation on right side, ideally --- should produce full partitionwise aggregation plan as GROUP BY is on --- non-nullable columns. --- But right now we are unable to do partitionwise join in this case. -EXPLAIN (COSTS OFF) -SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; - QUERY PLAN --------------------------------------------------------------------- - Sort - Sort Key: pagg_tab1.x, pagg_tab2.y - -> HashAggregate - Group Key: pagg_tab1.x, pagg_tab2.y - -> Hash Left Join - Hash Cond: (pagg_tab1.x = pagg_tab2.y) - Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20)) - -> Append - -> Seq Scan on pagg_tab1_p1 pagg_tab1_1 - Filter: (x < 20) - -> Seq Scan on pagg_tab1_p2 pagg_tab1_2 - Filter: (x < 20) - -> Hash - -> Append - -> Seq Scan on pagg_tab2_p2 pagg_tab2_1 - Filter: (y > 10) - -> Seq Scan on pagg_tab2_p3 pagg_tab2_2 - Filter: (y > 10) -(18 rows) - -SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; - x | y | count -----+----+------- - 6 | | 10 - 8 | | 10 - 10 | | 10 - 12 | 12 | 100 - 14 | | 10 - 16 | | 10 - 18 | 18 | 100 -(7 rows) - --- FULL JOIN, with dummy relations on both sides, ideally --- should produce partial partitionwise aggregation plan as GROUP BY is on --- nullable columns. --- But right now we are unable to do partitionwise join in this case. -EXPLAIN (COSTS OFF) -SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; - QUERY PLAN --------------------------------------------------------------------- - Sort - Sort Key: pagg_tab1.x, pagg_tab2.y - -> HashAggregate - Group Key: pagg_tab1.x, pagg_tab2.y - -> Hash Full Join - Hash Cond: (pagg_tab1.x = pagg_tab2.y) - Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20)) - -> Append - -> Seq Scan on pagg_tab1_p1 pagg_tab1_1 - Filter: (x < 20) - -> Seq Scan on pagg_tab1_p2 pagg_tab1_2 - Filter: (x < 20) - -> Hash - -> Append - -> Seq Scan on pagg_tab2_p2 pagg_tab2_1 - Filter: (y > 10) - -> Seq Scan on pagg_tab2_p3 pagg_tab2_2 - Filter: (y > 10) -(18 rows) - -SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; - x | y | count -----+----+------- - 6 | | 10 - 8 | | 10 - 10 | | 10 - 12 | 12 | 100 - 14 | | 10 - 16 | | 10 - 18 | 18 | 100 - | 15 | 10 -(8 rows) - --- Empty join relation because of empty outer side, no partitionwise agg plan -EXPLAIN (COSTS OFF) -SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; - QUERY PLAN --------------------------------------- - GroupAggregate - Group Key: pagg_tab1.y - -> Sort - Sort Key: pagg_tab1.y - -> Result - One-Time Filter: false -(6 rows) - -SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; - x | y | count ----+---+------- -(0 rows) - --- Partition by multiple columns -CREATE TABLE pagg_tab_m (a int, b int, c int) PARTITION BY RANGE(a, ((a+b)/2)); -CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (12, 12); -CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (12, 12) TO (22, 22); -CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (22, 22) TO (30, 30); -INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series(0, 2999) i; -ANALYZE pagg_tab_m; --- Partial aggregation as GROUP BY clause does not match with PARTITION KEY -EXPLAIN (COSTS OFF) -SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_m.a, (sum(pagg_tab_m.b)), (avg(pagg_tab_m.c)) - -> Finalize HashAggregate - Group Key: pagg_tab_m.a - Filter: (avg(pagg_tab_m.c) < '22'::numeric) - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_m.a - -> Seq Scan on pagg_tab_m_p1 pagg_tab_m - -> Partial HashAggregate - Group Key: pagg_tab_m_1.a - -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 - -> Partial HashAggregate - Group Key: pagg_tab_m_2.a - -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 -(15 rows) - -SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3; - a | sum | avg | count -----+------+---------------------+------- - 0 | 1500 | 20.0000000000000000 | 100 - 1 | 1600 | 21.0000000000000000 | 100 - 10 | 1500 | 20.0000000000000000 | 100 - 11 | 1600 | 21.0000000000000000 | 100 - 20 | 1500 | 20.0000000000000000 | 100 - 21 | 1600 | 21.0000000000000000 | 100 -(6 rows) - --- Full aggregation as GROUP BY clause matches with PARTITION KEY -EXPLAIN (COSTS OFF) -SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_m.a, (sum(pagg_tab_m.b)), (avg(pagg_tab_m.c)) - -> Append - -> HashAggregate - Group Key: pagg_tab_m.a, ((pagg_tab_m.a + pagg_tab_m.b) / 2) - Filter: (sum(pagg_tab_m.b) < 50) - -> Seq Scan on pagg_tab_m_p1 pagg_tab_m - -> HashAggregate - Group Key: pagg_tab_m_1.a, ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2) - Filter: (sum(pagg_tab_m_1.b) < 50) - -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 - -> HashAggregate - Group Key: pagg_tab_m_2.a, ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2) - Filter: (sum(pagg_tab_m_2.b) < 50) - -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 -(15 rows) - -SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; - a | sum | avg | count -----+-----+---------------------+------- - 0 | 0 | 20.0000000000000000 | 25 - 1 | 25 | 21.0000000000000000 | 25 - 10 | 0 | 20.0000000000000000 | 25 - 11 | 25 | 21.0000000000000000 | 25 - 20 | 0 | 20.0000000000000000 | 25 - 21 | 25 | 21.0000000000000000 | 25 -(6 rows) - --- Full aggregation as PARTITION KEY is part of GROUP BY clause -EXPLAIN (COSTS OFF) -SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_m.a, pagg_tab_m.c, (sum(pagg_tab_m.b)) - -> Append - -> HashAggregate - Group Key: ((pagg_tab_m.a + pagg_tab_m.b) / 2), pagg_tab_m.c, pagg_tab_m.a - Filter: ((sum(pagg_tab_m.b) = 50) AND (avg(pagg_tab_m.c) > '25'::numeric)) - -> Seq Scan on pagg_tab_m_p1 pagg_tab_m - -> HashAggregate - Group Key: ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2), pagg_tab_m_1.c, pagg_tab_m_1.a - Filter: ((sum(pagg_tab_m_1.b) = 50) AND (avg(pagg_tab_m_1.c) > '25'::numeric)) - -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 - -> HashAggregate - Group Key: ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2), pagg_tab_m_2.c, pagg_tab_m_2.a - Filter: ((sum(pagg_tab_m_2.b) = 50) AND (avg(pagg_tab_m_2.c) > '25'::numeric)) - -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 -(15 rows) - -SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; - a | c | sum | avg | count -----+----+-----+---------------------+------- - 0 | 30 | 50 | 30.0000000000000000 | 5 - 0 | 40 | 50 | 40.0000000000000000 | 5 - 10 | 30 | 50 | 30.0000000000000000 | 5 - 10 | 40 | 50 | 40.0000000000000000 | 5 - 20 | 30 | 50 | 30.0000000000000000 | 5 - 20 | 40 | 50 | 40.0000000000000000 | 5 -(6 rows) - --- Test with multi-level partitioning scheme -CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a); -CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (12); -CREATE TABLE pagg_tab_ml_p2 PARTITION OF pagg_tab_ml FOR VALUES FROM (12) TO (20) PARTITION BY LIST (c); -CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001', '0002'); -CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0003'); --- This level of partitioning has different column positions than the parent -CREATE TABLE pagg_tab_ml_p3(b int, c text, a int) PARTITION BY RANGE (b); -CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int); -CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (7) TO (10); -ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (7); -ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30); -INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i; -ANALYZE pagg_tab_ml; --- For Parallel Append -SET max_parallel_workers_per_gather TO 2; -SET parallel_setup_cost = 0; --- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY --- for level 1 only. For subpartitions, GROUP BY clause does not match with --- PARTITION KEY, but still we do not see a partial aggregation as array_agg() --- is not partial agg safe. -EXPLAIN (COSTS OFF) -SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (array_agg(DISTINCT pagg_tab_ml.c)) - -> Gather - Workers Planned: 2 - -> Parallel Append - -> GroupAggregate - Group Key: pagg_tab_ml.a - Filter: (avg(pagg_tab_ml.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml.a, pagg_tab_ml.c - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> GroupAggregate - Group Key: pagg_tab_ml_5.a - Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_5.a, pagg_tab_ml_5.c - -> Append - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5 - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6 - -> GroupAggregate - Group Key: pagg_tab_ml_2.a - Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_2.a, pagg_tab_ml_2.c - -> Append - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2 - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3 -(27 rows) - -SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - a | sum | array_agg | count -----+------+-------------+------- - 0 | 0 | {0000,0002} | 1000 - 1 | 1000 | {0001,0003} | 1000 - 2 | 2000 | {0000,0002} | 1000 - 10 | 0 | {0000,0002} | 1000 - 11 | 1000 | {0001,0003} | 1000 - 12 | 2000 | {0000,0002} | 1000 - 20 | 0 | {0000,0002} | 1000 - 21 | 1000 | {0001,0003} | 1000 - 22 | 2000 | {0000,0002} | 1000 -(9 rows) - --- Without ORDER BY clause, to test Gather at top-most path -EXPLAIN (COSTS OFF) -SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3; - QUERY PLAN ---------------------------------------------------------------------------- - Gather - Workers Planned: 2 - -> Parallel Append - -> GroupAggregate - Group Key: pagg_tab_ml.a - Filter: (avg(pagg_tab_ml.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml.a, pagg_tab_ml.c - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> GroupAggregate - Group Key: pagg_tab_ml_5.a - Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_5.a, pagg_tab_ml_5.c - -> Append - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5 - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6 - -> GroupAggregate - Group Key: pagg_tab_ml_2.a - Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_2.a, pagg_tab_ml_2.c - -> Append - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2 - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3 -(25 rows) - -RESET parallel_setup_cost; --- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY --- for level 1 only. For subpartitions, GROUP BY clause does not match with --- PARTITION KEY, thus we will have a partial aggregation for them. -EXPLAIN (COSTS OFF) -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - QUERY PLAN ---------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) - -> Append - -> HashAggregate - Group Key: pagg_tab_ml.a - Filter: (avg(pagg_tab_ml.b) < '3'::numeric) - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_2.a - Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_2.a - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_2.a - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2 - -> Partial HashAggregate - Group Key: pagg_tab_ml_3.a - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3 - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_5.a - Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric) - -> Sort - Sort Key: pagg_tab_ml_5.a - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_5.a - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5 - -> Partial HashAggregate - Group Key: pagg_tab_ml_6.a - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6 -(31 rows) - -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 0 | 0 | 1000 - 1 | 1000 | 1000 - 2 | 2000 | 1000 - 10 | 0 | 1000 - 11 | 1000 | 1000 - 12 | 2000 | 1000 - 20 | 0 | 1000 - 21 | 1000 | 1000 - 22 | 2000 | 1000 -(9 rows) - --- Partial aggregation at all levels as GROUP BY clause does not match with --- PARTITION KEY -EXPLAIN (COSTS OFF) -SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; - QUERY PLAN ---------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.b, (sum(pagg_tab_ml.a)), (count(*)) - -> Finalize GroupAggregate - Group Key: pagg_tab_ml.b - -> Sort - Sort Key: pagg_tab_ml.b - -> Append - -> Partial HashAggregate - Group Key: pagg_tab_ml.b - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> Partial HashAggregate - Group Key: pagg_tab_ml_1.b - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_2.b - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 - -> Partial HashAggregate - Group Key: pagg_tab_ml_3.b - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 - -> Partial HashAggregate - Group Key: pagg_tab_ml_4.b - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 -(22 rows) - -SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; - b | sum | count ----+-------+------- - 0 | 30000 | 3000 - 1 | 33000 | 3000 - 2 | 36000 | 3000 - 3 | 39000 | 3000 - 4 | 42000 | 3000 -(5 rows) - --- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY -EXPLAIN (COSTS OFF) -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) - -> Append - -> HashAggregate - Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c - Filter: (avg(pagg_tab_ml.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> HashAggregate - Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c - Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 - -> HashAggregate - Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c - Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 - -> HashAggregate - Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c - Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 - -> HashAggregate - Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c - Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 -(23 rows) - -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 8 | 4000 | 500 - 8 | 4000 | 500 - 9 | 4500 | 500 - 9 | 4500 | 500 - 18 | 4000 | 500 - 18 | 4000 | 500 - 19 | 4500 | 500 - 19 | 4500 | 500 - 28 | 4000 | 500 - 28 | 4000 | 500 - 29 | 4500 | 500 - 29 | 4500 | 500 -(12 rows) - --- Parallelism within partitionwise aggregates -SET min_parallel_table_scan_size TO '8kB'; -SET parallel_setup_cost TO 0; --- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY --- for level 1 only. For subpartitions, GROUP BY clause does not match with --- PARTITION KEY, thus we will have a partial aggregation for them. -EXPLAIN (COSTS OFF) -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - QUERY PLAN ------------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) - -> Append - -> Finalize GroupAggregate - Group Key: pagg_tab_ml.a - Filter: (avg(pagg_tab_ml.b) < '3'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml.a - -> Partial HashAggregate - Group Key: pagg_tab_ml.a - -> Parallel Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_2.a - Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_2.a - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_2.a - -> Parallel Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2 - -> Partial HashAggregate - Group Key: pagg_tab_ml_3.a - -> Parallel Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3 - -> Finalize GroupAggregate - Group Key: pagg_tab_ml_5.a - Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml_5.a - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_ml_5.a - -> Parallel Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5 - -> Partial HashAggregate - Group Key: pagg_tab_ml_6.a - -> Parallel Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6 -(41 rows) - -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 0 | 0 | 1000 - 1 | 1000 | 1000 - 2 | 2000 | 1000 - 10 | 0 | 1000 - 11 | 1000 | 1000 - 12 | 2000 | 1000 - 20 | 0 | 1000 - 21 | 1000 | 1000 - 22 | 2000 | 1000 -(9 rows) - --- Partial aggregation at all levels as GROUP BY clause does not match with --- PARTITION KEY -EXPLAIN (COSTS OFF) -SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; - QUERY PLAN ------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.b, (sum(pagg_tab_ml.a)), (count(*)) - -> Finalize GroupAggregate - Group Key: pagg_tab_ml.b - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml.b - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_ml.b - -> Parallel Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> Partial HashAggregate - Group Key: pagg_tab_ml_3.b - -> Parallel Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 - -> Partial HashAggregate - Group Key: pagg_tab_ml_1.b - -> Parallel Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 - -> Partial HashAggregate - Group Key: pagg_tab_ml_4.b - -> Parallel Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 - -> Partial HashAggregate - Group Key: pagg_tab_ml_2.b - -> Parallel Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 -(24 rows) - -SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; - b | sum | count ----+-------+------- - 0 | 30000 | 3000 - 1 | 33000 | 3000 - 2 | 36000 | 3000 - 3 | 39000 | 3000 - 4 | 42000 | 3000 -(5 rows) - --- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY -EXPLAIN (COSTS OFF) -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------- - Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) - -> Parallel Append - -> HashAggregate - Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c - Filter: (avg(pagg_tab_ml.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> HashAggregate - Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c - Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 - -> HashAggregate - Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c - Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 - -> HashAggregate - Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c - Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 - -> HashAggregate - Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c - Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 -(25 rows) - -SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - a | sum | count -----+------+------- - 8 | 4000 | 500 - 8 | 4000 | 500 - 9 | 4500 | 500 - 9 | 4500 | 500 - 18 | 4000 | 500 - 18 | 4000 | 500 - 19 | 4500 | 500 - 19 | 4500 | 500 - 28 | 4000 | 500 - 28 | 4000 | 500 - 29 | 4500 | 500 - 29 | 4500 | 500 -(12 rows) - --- Parallelism within partitionwise aggregates (single level) --- Add few parallel setup cost, so that we will see a plan which gathers --- partially created paths even for full aggregation and sticks a single Gather --- followed by finalization step. --- Without this, the cost of doing partial aggregation + Gather + finalization --- for each partition and then Append over it turns out to be same and this --- wins as we add it first. This parallel_setup_cost plays a vital role in --- costing such plans. -SET parallel_setup_cost TO 10; -CREATE TABLE pagg_tab_para(x int, y int) PARTITION BY RANGE(x); -CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (12); -CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (12) TO (22); -CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (22) TO (30); -INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 29999) i; -ANALYZE pagg_tab_para; --- When GROUP BY clause matches; full aggregation is performed for each partition. -EXPLAIN (COSTS OFF) -SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para.x - Filter: (avg(pagg_tab_para.y) < '7'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para.x - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_para.x - -> Parallel Seq Scan on pagg_tab_para_p1 pagg_tab_para - -> Partial HashAggregate - Group Key: pagg_tab_para_1.x - -> Parallel Seq Scan on pagg_tab_para_p2 pagg_tab_para_1 - -> Partial HashAggregate - Group Key: pagg_tab_para_2.x - -> Parallel Seq Scan on pagg_tab_para_p3 pagg_tab_para_2 -(19 rows) - -SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | count -----+------+--------------------+------- - 0 | 5000 | 5.0000000000000000 | 1000 - 1 | 6000 | 6.0000000000000000 | 1000 - 10 | 5000 | 5.0000000000000000 | 1000 - 11 | 6000 | 6.0000000000000000 | 1000 - 20 | 5000 | 5.0000000000000000 | 1000 - 21 | 6000 | 6.0000000000000000 | 1000 -(6 rows) - --- When GROUP BY clause does not match; partial aggregation is performed for each partition. -EXPLAIN (COSTS OFF) -SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para.y, (sum(pagg_tab_para.x)), (avg(pagg_tab_para.x)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para.y - Filter: (avg(pagg_tab_para.x) < '12'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para.y - -> Parallel Append - -> Partial HashAggregate - Group Key: pagg_tab_para.y - -> Parallel Seq Scan on pagg_tab_para_p1 pagg_tab_para - -> Partial HashAggregate - Group Key: pagg_tab_para_1.y - -> Parallel Seq Scan on pagg_tab_para_p2 pagg_tab_para_1 - -> Partial HashAggregate - Group Key: pagg_tab_para_2.y - -> Parallel Seq Scan on pagg_tab_para_p3 pagg_tab_para_2 -(19 rows) - -SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; - y | sum | avg | count -----+-------+---------------------+------- - 0 | 15000 | 10.0000000000000000 | 1500 - 1 | 16500 | 11.0000000000000000 | 1500 - 10 | 15000 | 10.0000000000000000 | 1500 - 11 | 16500 | 11.0000000000000000 | 1500 -(4 rows) - --- Test when parent can produce parallel paths but not any (or some) of its children --- (Use one more aggregate to tilt the cost estimates for the plan we want) -ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0); -ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0); -ANALYZE pagg_tab_para; -EXPLAIN (COSTS OFF) -SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN -------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para.x - Filter: (avg(pagg_tab_para.y) < '7'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para.x - -> Partial HashAggregate - Group Key: pagg_tab_para.x - -> Parallel Append - -> Seq Scan on pagg_tab_para_p1 pagg_tab_para_1 - -> Seq Scan on pagg_tab_para_p3 pagg_tab_para_3 - -> Parallel Seq Scan on pagg_tab_para_p2 pagg_tab_para_2 -(15 rows) - -SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | sum | count -----+------+--------------------+-------+------- - 0 | 5000 | 5.0000000000000000 | 5000 | 1000 - 1 | 6000 | 6.0000000000000000 | 7000 | 1000 - 10 | 5000 | 5.0000000000000000 | 15000 | 1000 - 11 | 6000 | 6.0000000000000000 | 17000 | 1000 - 20 | 5000 | 5.0000000000000000 | 25000 | 1000 - 21 | 6000 | 6.0000000000000000 | 27000 | 1000 -(6 rows) - -ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0); -ANALYZE pagg_tab_para; -EXPLAIN (COSTS OFF) -SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y)) - -> Finalize GroupAggregate - Group Key: pagg_tab_para.x - Filter: (avg(pagg_tab_para.y) < '7'::numeric) - -> Gather Merge - Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_para.x - -> Partial HashAggregate - Group Key: pagg_tab_para.x - -> Parallel Append - -> Seq Scan on pagg_tab_para_p1 pagg_tab_para_1 - -> Seq Scan on pagg_tab_para_p2 pagg_tab_para_2 - -> Seq Scan on pagg_tab_para_p3 pagg_tab_para_3 -(15 rows) - -SELECT x, sum(y), avg(y), sum(x+y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | sum | count -----+------+--------------------+-------+------- - 0 | 5000 | 5.0000000000000000 | 5000 | 1000 - 1 | 6000 | 6.0000000000000000 | 7000 | 1000 - 10 | 5000 | 5.0000000000000000 | 15000 | 1000 - 11 | 6000 | 6.0000000000000000 | 17000 | 1000 - 20 | 5000 | 5.0000000000000000 | 25000 | 1000 - 21 | 6000 | 6.0000000000000000 | 27000 | 1000 -(6 rows) - --- Reset parallelism parameters to get partitionwise aggregation plan. -RESET min_parallel_table_scan_size; -RESET parallel_setup_cost; -EXPLAIN (COSTS OFF) -SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - QUERY PLAN ------------------------------------------------------------------------------ - Sort - Sort Key: pagg_tab_para.x, (sum(pagg_tab_para.y)), (avg(pagg_tab_para.y)) - -> Append - -> HashAggregate - Group Key: pagg_tab_para.x - Filter: (avg(pagg_tab_para.y) < '7'::numeric) - -> Seq Scan on pagg_tab_para_p1 pagg_tab_para - -> HashAggregate - Group Key: pagg_tab_para_1.x - Filter: (avg(pagg_tab_para_1.y) < '7'::numeric) - -> Seq Scan on pagg_tab_para_p2 pagg_tab_para_1 - -> HashAggregate - Group Key: pagg_tab_para_2.x - Filter: (avg(pagg_tab_para_2.y) < '7'::numeric) - -> Seq Scan on pagg_tab_para_p3 pagg_tab_para_2 -(15 rows) - -SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; - x | sum | avg | count -----+------+--------------------+------- - 0 | 5000 | 5.0000000000000000 | 1000 - 1 | 6000 | 6.0000000000000000 | 1000 - 10 | 5000 | 5.0000000000000000 | 1000 - 11 | 6000 | 6.0000000000000000 | 1000 - 20 | 5000 | 5.0000000000000000 | 1000 - 21 | 6000 | 6.0000000000000000 | 1000 -(6 rows) - +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -w -U3 C:/cirrus/src/test/regress/expected/stats.out C:/cirrus/build/testrun/regress/regress/results/stats.out --- C:/cirrus/src/test/regress/expected/stats.out 2024-03-11 18:11:56.559878300 +0000 +++ C:/cirrus/build/testrun/regress/regress/results/stats.out 2024-03-11 18:15:28.984982400 +0000 @@ -1203,447 +1203,10 @@ (1 row) REINDEX index CONCURRENTLY stats_test_idx1; --- false for previous oid -SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); - pg_stat_have_stats --------------------- - f -(1 row) - --- true for new oid -SELECT 'stats_test_idx1'::regclass::oid AS stats_test_idx1_oid \gset -SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); - pg_stat_have_stats --------------------- - t -(1 row) - --- pg_stat_have_stats returns true for a rolled back drop index with stats -BEGIN; -SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); - pg_stat_have_stats --------------------- - t -(1 row) - -DROP index stats_test_idx1; -ROLLBACK; -SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); - pg_stat_have_stats --------------------- - t -(1 row) - --- put enable_seqscan back to on -SET enable_seqscan TO on; --- ensure that stats accessors handle NULL input correctly -SELECT pg_stat_get_replication_slot(NULL); - pg_stat_get_replication_slot ------------------------------- - -(1 row) - -SELECT pg_stat_get_subscription_stats(NULL); - pg_stat_get_subscription_stats --------------------------------- - -(1 row) - --- Test that the following operations are tracked in pg_stat_io: --- - reads of target blocks into shared buffers --- - writes of shared buffers to permanent storage --- - extends of relations using shared buffers --- - fsyncs done to ensure the durability of data dirtying shared buffers --- - shared buffer hits --- There is no test for blocks evicted from shared buffers, because we cannot --- be sure of the state of shared buffers at the point the test is run. --- Create a regular table and insert some data to generate IOCONTEXT_NORMAL --- extends. -SELECT sum(extends) AS io_sum_shared_before_extends - FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset -SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs - FROM pg_stat_io - WHERE object = 'relation' \gset io_sum_shared_before_ -CREATE TABLE test_io_shared(a int); -INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i; -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(extends) AS io_sum_shared_after_extends - FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset -SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends; - ?column? ----------- - t -(1 row) - --- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes --- and fsyncs. --- See comment above for rationale for two explicit CHECKPOINTs. -CHECKPOINT; -CHECKPOINT; -SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs - FROM pg_stat_io - WHERE object = 'relation' \gset io_sum_shared_after_ -SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes; - ?column? ----------- - t -(1 row) - -SELECT current_setting('fsync') = 'off' - OR :io_sum_shared_after_fsyncs > :io_sum_shared_before_fsyncs; - ?column? ----------- - t -(1 row) - --- Change the tablespace so that the table is rewritten directly, then SELECT --- from it to cause it to be read back into shared buffers. -SELECT sum(reads) AS io_sum_shared_before_reads - FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset --- Do this in a transaction to prevent spurious failures due to concurrent accesses to our newly --- rewritten table, e.g. by autovacuum. -BEGIN; -ALTER TABLE test_io_shared SET TABLESPACE regress_tblspace; --- SELECT from the table so that the data is read into shared buffers and --- context 'normal', object 'relation' reads are counted. -SELECT COUNT(*) FROM test_io_shared; - count -------- - 100 -(1 row) - -COMMIT; -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(reads) AS io_sum_shared_after_reads - FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset -SELECT :io_sum_shared_after_reads > :io_sum_shared_before_reads; - ?column? ----------- - t -(1 row) - -SELECT sum(hits) AS io_sum_shared_before_hits - FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset --- Select from the table again to count hits. --- Ensure we generate hits by forcing a nested loop self-join with no --- materialize node. The outer side's buffer will stay pinned, preventing its --- eviction, while we loop through the inner side and generate hits. -BEGIN; -SET LOCAL enable_nestloop TO on; SET LOCAL enable_mergejoin TO off; -SET LOCAL enable_hashjoin TO off; SET LOCAL enable_material TO off; --- ensure plan stays as we expect it to -EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM test_io_shared t1 INNER JOIN test_io_shared t2 USING (a); - QUERY PLAN -------------------------------------------- - Aggregate - -> Nested Loop - Join Filter: (t1.a = t2.a) - -> Seq Scan on test_io_shared t1 - -> Seq Scan on test_io_shared t2 -(5 rows) - -SELECT COUNT(*) FROM test_io_shared t1 INNER JOIN test_io_shared t2 USING (a); - count -------- - 100 -(1 row) - -COMMIT; -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(hits) AS io_sum_shared_after_hits - FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset -SELECT :io_sum_shared_after_hits > :io_sum_shared_before_hits; - ?column? ----------- - t -(1 row) - -DROP TABLE test_io_shared; --- Test that the follow IOCONTEXT_LOCAL IOOps are tracked in pg_stat_io: --- - eviction of local buffers in order to reuse them --- - reads of temporary table blocks into local buffers --- - writes of local buffers to permanent storage --- - extends of temporary tables --- Set temp_buffers to its minimum so that we can trigger writes with fewer --- inserted tuples. Do so in a new session in case temporary tables have been --- accessed by previous tests in this session. -\c -SET temp_buffers TO 100; -CREATE TEMPORARY TABLE test_io_local(a int, b TEXT); -SELECT sum(extends) AS extends, sum(evictions) AS evictions, sum(writes) AS writes - FROM pg_stat_io - WHERE context = 'normal' AND object = 'temp relation' \gset io_sum_local_before_ --- Insert tuples into the temporary table, generating extends in the stats. --- Insert enough values that we need to reuse and write out dirty local --- buffers, generating evictions and writes. -INSERT INTO test_io_local SELECT generate_series(1, 5000) as id, repeat('a', 200); --- Ensure the table is large enough to exceed our temp_buffers setting. -SELECT pg_relation_size('test_io_local') / current_setting('block_size')::int8 > 100; - ?column? ----------- - t -(1 row) - -SELECT sum(reads) AS io_sum_local_before_reads - FROM pg_stat_io WHERE context = 'normal' AND object = 'temp relation' \gset --- Read in evicted buffers, generating reads. -SELECT COUNT(*) FROM test_io_local; - count -------- - 5000 -(1 row) - -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(evictions) AS evictions, - sum(reads) AS reads, - sum(writes) AS writes, - sum(extends) AS extends - FROM pg_stat_io - WHERE context = 'normal' AND object = 'temp relation' \gset io_sum_local_after_ -SELECT :io_sum_local_after_evictions > :io_sum_local_before_evictions, - :io_sum_local_after_reads > :io_sum_local_before_reads, - :io_sum_local_after_writes > :io_sum_local_before_writes, - :io_sum_local_after_extends > :io_sum_local_before_extends; - ?column? | ?column? | ?column? | ?column? -----------+----------+----------+---------- - t | t | t | t -(1 row) - --- Change the tablespaces so that the temporary table is rewritten to other --- local buffers, exercising a different codepath than standard local buffer --- writes. -ALTER TABLE test_io_local SET TABLESPACE regress_tblspace; -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(writes) AS io_sum_local_new_tblspc_writes - FROM pg_stat_io WHERE context = 'normal' AND object = 'temp relation' \gset -SELECT :io_sum_local_new_tblspc_writes > :io_sum_local_after_writes; - ?column? ----------- - t -(1 row) - -RESET temp_buffers; --- Test that reuse of strategy buffers and reads of blocks into these reused --- buffers while VACUUMing are tracked in pg_stat_io. If there is sufficient --- demand for shared buffers from concurrent queries, some buffers may be --- pinned by other backends before they can be reused. In such cases, the --- backend will evict a buffer from outside the ring and add it to the --- ring. This is considered an eviction and not a reuse. --- Set wal_skip_threshold smaller than the expected size of --- test_io_vac_strategy so that, even if wal_level is minimal, VACUUM FULL will --- fsync the newly rewritten test_io_vac_strategy instead of writing it to WAL. --- Writing it to WAL will result in the newly written relation pages being in --- shared buffers -- preventing us from testing BAS_VACUUM BufferAccessStrategy --- reads. -SET wal_skip_threshold = '1 kB'; -SELECT sum(reuses) AS reuses, sum(reads) AS reads, sum(evictions) AS evictions - FROM pg_stat_io WHERE context = 'vacuum' \gset io_sum_vac_strategy_before_ -CREATE TABLE test_io_vac_strategy(a int, b int) WITH (autovacuum_enabled = 'false'); -INSERT INTO test_io_vac_strategy SELECT i, i from generate_series(1, 4500)i; --- Ensure that the next VACUUM will need to perform IO by rewriting the table --- first with VACUUM (FULL). -VACUUM (FULL) test_io_vac_strategy; --- Use the minimum BUFFER_USAGE_LIMIT to cause reuses or evictions with the --- smallest table possible. -VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) test_io_vac_strategy; -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(reuses) AS reuses, sum(reads) AS reads, sum(evictions) AS evictions - FROM pg_stat_io WHERE context = 'vacuum' \gset io_sum_vac_strategy_after_ -SELECT :io_sum_vac_strategy_after_reads > :io_sum_vac_strategy_before_reads; - ?column? ----------- - t -(1 row) - -SELECT (:io_sum_vac_strategy_after_reuses + :io_sum_vac_strategy_after_evictions) > - (:io_sum_vac_strategy_before_reuses + :io_sum_vac_strategy_before_evictions); - ?column? ----------- - t -(1 row) - -RESET wal_skip_threshold; --- Test that extends done by a CTAS, which uses a BAS_BULKWRITE --- BufferAccessStrategy, are tracked in pg_stat_io. -SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_before - FROM pg_stat_io WHERE context = 'bulkwrite' \gset -CREATE TABLE test_io_bulkwrite_strategy AS SELECT i FROM generate_series(1,100)i; -SELECT pg_stat_force_next_flush(); - pg_stat_force_next_flush --------------------------- - -(1 row) - -SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_after - FROM pg_stat_io WHERE context = 'bulkwrite' \gset -SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_extends_before; - ?column? ----------- - t -(1 row) - --- Test IO stats reset -SELECT pg_stat_have_stats('io', 0, 0); - pg_stat_have_stats --------------------- - t -(1 row) - -SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset - FROM pg_stat_io \gset -SELECT pg_stat_reset_shared('io'); - pg_stat_reset_shared ----------------------- - -(1 row) - -SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset - FROM pg_stat_io \gset -SELECT :io_stats_post_reset < :io_stats_pre_reset; - ?column? ----------- - t -(1 row) - --- test BRIN index doesn't block HOT update -CREATE TABLE brin_hot ( - id integer PRIMARY KEY, - val integer NOT NULL -) WITH (autovacuum_enabled = off, fillfactor = 70); -INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235); -CREATE INDEX val_brin ON brin_hot using brin(val); -CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$ -DECLARE - start_time timestamptz := clock_timestamp(); - updated bool; -BEGIN - -- we don't want to wait forever; loop will exit after 30 seconds - FOR i IN 1 .. 300 LOOP - SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated; - EXIT WHEN updated; - - -- wait a little - PERFORM pg_sleep_for('100 milliseconds'); - -- reset stats snapshot so we can test again - PERFORM pg_stat_clear_snapshot(); - END LOOP; - -- report time waited in postmaster log (where it won't change test output) - RAISE log 'wait_for_hot_stats delayed % seconds', - EXTRACT(epoch FROM clock_timestamp() - start_time); -END -$$ LANGUAGE plpgsql; -UPDATE brin_hot SET val = -3 WHERE id = 42; --- We can't just call wait_for_hot_stats() at this point, because we only --- transmit stats when the session goes idle, and we probably didn't --- transmit the last couple of counts yet thanks to the rate-limiting logic --- in pgstat_report_stat(). But instead of waiting for the rate limiter's --- timeout to elapse, let's just start a new session. The old one will --- then send its stats before dying. -\c - -SELECT wait_for_hot_stats(); - wait_for_hot_stats --------------------- - -(1 row) - -SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid); - pg_stat_get_tuples_hot_updated --------------------------------- - 1 -(1 row) - -DROP TABLE brin_hot; -DROP FUNCTION wait_for_hot_stats(); --- Test handling of index predicates - updating attributes in precicates --- should not block HOT when summarizing indexes are involved. We update --- a row that was not indexed due to the index predicate, and becomes --- indexable - the HOT-updated tuple is forwarded to the BRIN index. -CREATE TABLE brin_hot_2 (a int, b int); -INSERT INTO brin_hot_2 VALUES (1, 100); -CREATE INDEX ON brin_hot_2 USING brin (b) WHERE a = 2; -UPDATE brin_hot_2 SET a = 2; -EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100; - QUERY PLAN ------------------------------------ - Seq Scan on brin_hot_2 - Filter: ((a = 2) AND (b = 100)) -(2 rows) - -SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100; - count -------- - 1 -(1 row) - -SET enable_seqscan = off; -EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100; - QUERY PLAN ---------------------------------------------- - Bitmap Heap Scan on brin_hot_2 - Recheck Cond: ((b = 100) AND (a = 2)) - -> Bitmap Index Scan on brin_hot_2_b_idx - Index Cond: (b = 100) -(4 rows) - -SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100; - count -------- - 1 -(1 row) - -DROP TABLE brin_hot_2; --- Test that updates to indexed columns are still propagated to the --- BRIN column. --- https://postgr.es/m/05ebcb44-f383-86e3-4f31-0a97a55634cf@enterprisedb.com -CREATE TABLE brin_hot_3 (a int, filler text) WITH (fillfactor = 10); -INSERT INTO brin_hot_3 SELECT 1, repeat(' ', 500) FROM generate_series(1, 20); -CREATE INDEX ON brin_hot_3 USING brin (a) WITH (pages_per_range = 1); -UPDATE brin_hot_3 SET a = 2; -EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_3 WHERE a = 2; - QUERY PLAN ---------------------------------------------- - Bitmap Heap Scan on brin_hot_3 - Recheck Cond: (a = 2) - -> Bitmap Index Scan on brin_hot_3_a_idx - Index Cond: (a = 2) -(4 rows) - -SELECT COUNT(*) FROM brin_hot_3 WHERE a = 2; - count -------- - 20 -(1 row) - -DROP TABLE brin_hot_3; -SET enable_seqscan = on; --- End of Stats Test +WARNING: terminating connection because of crash of another server process +DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. +HINT: In a moment you should be able to reconnect to the database and repeat your command. +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost