On 2018/04/11 21:35, Alvaro Herrera wrote: > Here's an idea. Why don't we move the function/opclass creation lines > to insert.sql, without the DROPs, and use the same functions/opclasses > in the three tests insert.sql, alter_table.sql, hash_part.sql and > partition_prune.sql, i.e. not recreate what are essentially the same > objects three times? This also leaves them around for the pg_upgrade > test, which is not a bad thing. > > (This would require a few updates to insert.sql because the definitions > there are different, but it shouldn't be a problem coverage-wise.)
OK, I've tried doing that. Needed adjustments to hash_part.sql as well. The hash function for int4 was defined differently in insert.sql, alter_table.sql, and hash_part.sql. I went with the definition in insert.sql, which although slightly different from the one alter_table.sql, didn't affect the latter's output in any way. Since the definition in hash_part.sql was totally different, a couple of tests needed adjusting after starting to use hash opclasses defined in insert.sql. Attached updated patch. PS: git grep "partition by hash\|PARTITION BY HASH" on src/test indicates that there are hash partitioning related tests in create_table, foreign_key, and partition_join files as well. Do we want to use the custom opclass in those files as well? Thanks, Amit
From 5a01d81aa7e90ef130b245c5e38b02fe9be5e8d7 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Tue, 10 Apr 2018 16:06:33 +0900 Subject: [PATCH v4] Rewrite hash partition pruning tests to use custom opclass Relying on platform-provided hashing functions makes tests unreliable as shown by buildfarm recently. This adds adjusted tests to partition_prune.sql itself and hence partition_prune_hash.sql is deleted along with two expected output files. Discussion: https://postgr.es/m/CA%2BTgmoZ0D5kJbt8eKXtvVdvTcGGWn6ehWCRSZbWytD-uzH92mQ%40mail.gmail.com --- src/test/regress/expected/alter_table.out | 15 +- src/test/regress/expected/hash_part.out | 23 +-- src/test/regress/expected/insert.out | 32 +++- src/test/regress/expected/partition_prune.out | 191 +++++++++++++++++++++ src/test/regress/expected/partition_prune_hash.out | 189 -------------------- .../regress/expected/partition_prune_hash_1.out | 187 -------------------- src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 - src/test/regress/sql/alter_table.sql | 15 +- src/test/regress/sql/hash_part.sql | 24 +-- src/test/regress/sql/insert.sql | 36 +++- src/test/regress/sql/partition_prune.sql | 44 ++++- src/test/regress/sql/partition_prune_hash.sql | 41 ----- 13 files changed, 305 insertions(+), 495 deletions(-) delete mode 100644 src/test/regress/expected/partition_prune_hash.out delete mode 100644 src/test/regress/expected/partition_prune_hash_1.out delete mode 100644 src/test/regress/sql/partition_prune_hash.sql diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 63845910a6..50b9443e2d 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3662,20 +3662,13 @@ CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2); INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints DROP TABLE quuux; -- check validation when attaching hash partitions --- The default hash functions as they exist today aren't portable; they can --- return different results on different machines. Depending upon how the --- values are hashed, the row may map to different partitions, which result in --- regression failure. To avoid this, let's create a non-default hash function --- that just returns the input value unchanged. -CREATE OR REPLACE FUNCTION dummy_hashint4(a int4, seed int8) RETURNS int8 AS -$$ BEGIN RETURN (a + 1 + seed); END; $$ LANGUAGE 'plpgsql' IMMUTABLE; -CREATE OPERATOR CLASS custom_opclass FOR TYPE int4 USING HASH AS -OPERATOR 1 = , FUNCTION 2 dummy_hashint4(int4, int8); +-- Use hand-rolled hash functions and operator class to get predictable result +-- on different matchines. part_test_int4_ops is defined in insert.sql. -- check that the new partition won't overlap with an existing partition CREATE TABLE hash_parted ( a int, b int -) PARTITION BY HASH (a custom_opclass); +) PARTITION BY HASH (a part_test_int4_ops); CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE fail_part (LIKE hpart_1); ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4); @@ -3840,8 +3833,6 @@ SELECT * FROM list_parted; DROP TABLE list_parted, list_parted2, range_parted; DROP TABLE fail_def_part; DROP TABLE hash_parted; -DROP OPERATOR CLASS custom_opclass USING HASH; -DROP FUNCTION dummy_hashint4(a int4, seed int8); -- more tests for certain multi-level partitioning scenarios create table p (a int, b int) partition by range (a, b); create table p1 (b int, a int not null) partition by range (b); diff --git a/src/test/regress/expected/hash_part.out b/src/test/regress/expected/hash_part.out index 9e9e56f6fc..731d26fc3d 100644 --- a/src/test/regress/expected/hash_part.out +++ b/src/test/regress/expected/hash_part.out @@ -1,16 +1,11 @@ -- -- Hash partitioning. -- -CREATE OR REPLACE FUNCTION hashint4_noop(int4, int8) RETURNS int8 AS -$$SELECT coalesce($1,0)::int8$$ LANGUAGE sql IMMUTABLE; -CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING HASH AS -OPERATOR 1 = , FUNCTION 2 hashint4_noop(int4, int8); -CREATE OR REPLACE FUNCTION hashtext_length(text, int8) RETURNS int8 AS -$$SELECT length(coalesce($1,''))::int8$$ LANGUAGE sql IMMUTABLE; -CREATE OPERATOR CLASS test_text_ops FOR TYPE text USING HASH AS -OPERATOR 1 = , FUNCTION 2 hashtext_length(text, int8); +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different matchines. See the definitions of +-- part_part_test_int4_ops and part_test_text_ops in insert.sql. CREATE TABLE mchash (a int, b text, c jsonb) - PARTITION BY HASH (a test_int4_ops, b test_text_ops); + PARTITION BY HASH (a part_test_int4_ops, b part_test_text_ops); CREATE TABLE mchash1 PARTITION OF mchash FOR VALUES WITH (MODULUS 4, REMAINDER 0); -- invalid OID, no such table @@ -66,7 +61,7 @@ SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 0, ''::text); (1 row) -- ok, should be true -SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 1, ''::text); +SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 2, ''::text); satisfies_hash_partition -------------------------- t @@ -79,7 +74,7 @@ SELECT satisfies_hash_partition('mchash'::regclass, 2, 1, ERROR: column 2 of the partition key has type "text", but supplied value is of type "integer" -- multiple partitioning columns of the same type CREATE TABLE mcinthash (a int, b int, c jsonb) - PARTITION BY HASH (a test_int4_ops, b test_int4_ops); + PARTITION BY HASH (a part_test_int4_ops, b part_test_int4_ops); -- now variadic should work, should be false SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, variadic array[0, 0]); @@ -90,7 +85,7 @@ SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, -- should be true SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, - variadic array[1, 0]); + variadic array[0, 1]); satisfies_hash_partition -------------------------- t @@ -107,7 +102,3 @@ ERROR: column 1 of the partition key has type "integer", but supplied value is -- cleanup DROP TABLE mchash; DROP TABLE mcinthash; -DROP OPERATOR CLASS test_text_ops USING hash; -DROP OPERATOR CLASS test_int4_ops USING hash; -DROP FUNCTION hashint4_noop(int4, int8); -DROP FUNCTION hashtext_length(text, int8); diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 97419a744f..5edf269367 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -387,15 +387,31 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p (9 rows) -- direct partition inserts should check hash partition bound constraint --- create custom operator class and hash function, for the same reason --- explained in alter_table.sql -create or replace function dummy_hashint4(a int4, seed int8) returns int8 as -$$ begin return (a + seed); end; $$ language 'plpgsql' immutable; -create operator class custom_opclass for type int4 using hash as -operator 1 = , function 2 dummy_hashint4(int4, int8); +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different matchines. The hash function for int4 simply returns +-- the sum of the values passed to it and the one for text returns the length +-- of the non-empty string value passed to it or 0. +create or replace function part_hashint4_noop(value int4, seed int8) +returns int8 as $$ +select value + seed; +$$ language sql immutable; +create operator class part_test_int4_ops +for type int4 +using hash as +operator 1 =, +function 2 part_hashint4_noop(int4, int8); +create or replace function part_hashtext_length(value text, seed int8) +RETURNS int8 AS $$ +select length(coalesce(value, ''))::int8 +$$ language sql immutable; +create operator class part_test_text_ops +for type text +using hash as +operator 1 =, +function 2 part_hashtext_length(text, int8); create table hash_parted ( a int -) partition by hash (a custom_opclass); +) partition by hash (a part_test_int4_ops); create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0); create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1); create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2); @@ -449,8 +465,6 @@ Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'), -- cleanup drop table range_parted, list_parted; drop table hash_parted; -drop operator class custom_opclass using hash; -drop function dummy_hashint4(a int4, seed int8); -- test that a default partition added as the first partition accepts any value -- including null create table list_parted (a int) partition by list (a); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index df3fca025e..12b1e85725 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1332,6 +1332,197 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000; drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; -- +-- Test Partition pruning for HASH partitioning +-- +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different matchines. See the definitions of +-- part_part_test_int4_ops and part_test_text_ops in insert.sql. +-- +create table hp (a int, b text) partition by hash (a part_test_int4_ops, b part_test_text_ops); +create table hp0 partition of hp for values with (modulus 4, remainder 0); +create table hp3 partition of hp for values with (modulus 4, remainder 3); +create table hp1 partition of hp for values with (modulus 4, remainder 1); +create table hp2 partition of hp for values with (modulus 4, remainder 2); +insert into hp values (null, null); +insert into hp values (1, null); +insert into hp values (1, 'xxx'); +insert into hp values (null, 'xxx'); +insert into hp values (2, 'xxx'); +insert into hp values (1, 'abcde'); +select tableoid::regclass, * from hp order by 1; + tableoid | a | b +----------+---+------- + hp0 | | + hp0 | 1 | xxx + hp3 | 2 | xxx + hp1 | 1 | + hp2 | | xxx + hp2 | 1 | abcde +(6 rows) + +-- partial keys won't prune, nor would non-equality conditions +explain (costs off) select * from hp where a = 1; + QUERY PLAN +------------------------- + Append + -> Seq Scan on hp0 + Filter: (a = 1) + -> Seq Scan on hp1 + Filter: (a = 1) + -> Seq Scan on hp2 + Filter: (a = 1) + -> Seq Scan on hp3 + Filter: (a = 1) +(9 rows) + +explain (costs off) select * from hp where b = 'xxx'; + QUERY PLAN +----------------------------------- + Append + -> Seq Scan on hp0 + Filter: (b = 'xxx'::text) + -> Seq Scan on hp1 + Filter: (b = 'xxx'::text) + -> Seq Scan on hp2 + Filter: (b = 'xxx'::text) + -> Seq Scan on hp3 + Filter: (b = 'xxx'::text) +(9 rows) + +explain (costs off) select * from hp where a is null; + QUERY PLAN +----------------------------- + Append + -> Seq Scan on hp0 + Filter: (a IS NULL) + -> Seq Scan on hp1 + Filter: (a IS NULL) + -> Seq Scan on hp2 + Filter: (a IS NULL) + -> Seq Scan on hp3 + Filter: (a IS NULL) +(9 rows) + +explain (costs off) select * from hp where b is null; + QUERY PLAN +----------------------------- + Append + -> Seq Scan on hp0 + Filter: (b IS NULL) + -> Seq Scan on hp1 + Filter: (b IS NULL) + -> Seq Scan on hp2 + Filter: (b IS NULL) + -> Seq Scan on hp3 + Filter: (b IS NULL) +(9 rows) + +explain (costs off) select * from hp where a < 1 and b = 'xxx'; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on hp0 + Filter: ((a < 1) AND (b = 'xxx'::text)) + -> Seq Scan on hp1 + Filter: ((a < 1) AND (b = 'xxx'::text)) + -> Seq Scan on hp2 + Filter: ((a < 1) AND (b = 'xxx'::text)) + -> Seq Scan on hp3 + Filter: ((a < 1) AND (b = 'xxx'::text)) +(9 rows) + +explain (costs off) select * from hp where a <> 1 and b = 'yyy'; + QUERY PLAN +-------------------------------------------------- + Append + -> Seq Scan on hp0 + Filter: ((a <> 1) AND (b = 'yyy'::text)) + -> Seq Scan on hp1 + Filter: ((a <> 1) AND (b = 'yyy'::text)) + -> Seq Scan on hp2 + Filter: ((a <> 1) AND (b = 'yyy'::text)) + -> Seq Scan on hp3 + Filter: ((a <> 1) AND (b = 'yyy'::text)) +(9 rows) + +explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; + QUERY PLAN +--------------------------------------------------- + Append + -> Seq Scan on hp0 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) + -> Seq Scan on hp1 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) + -> Seq Scan on hp2 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) + -> Seq Scan on hp3 + Filter: ((a <> 1) AND (b <> 'xxx'::text)) +(9 rows) + +-- pruning should work if either a value or a IS NULL clause is provided for +-- each of the keys +explain (costs off) select * from hp where a is null and b is null; + QUERY PLAN +----------------------------------------------- + Append + -> Seq Scan on hp0 + Filter: ((a IS NULL) AND (b IS NULL)) +(3 rows) + +explain (costs off) select * from hp where a = 1 and b is null; + QUERY PLAN +------------------------------------------- + Append + -> Seq Scan on hp1 + Filter: ((b IS NULL) AND (a = 1)) +(3 rows) + +explain (costs off) select * from hp where a = 1 and b = 'xxx'; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on hp0 + Filter: ((a = 1) AND (b = 'xxx'::text)) +(3 rows) + +explain (costs off) select * from hp where a is null and b = 'xxx'; + QUERY PLAN +----------------------------------------------------- + Append + -> Seq Scan on hp2 + Filter: ((a IS NULL) AND (b = 'xxx'::text)) +(3 rows) + +explain (costs off) select * from hp where a = 2 and b = 'xxx'; + QUERY PLAN +------------------------------------------------- + Append + -> Seq Scan on hp3 + Filter: ((a = 2) AND (b = 'xxx'::text)) +(3 rows) + +explain (costs off) select * from hp where a = 1 and b = 'abcde'; + QUERY PLAN +--------------------------------------------------- + Append + -> Seq Scan on hp2 + Filter: ((a = 1) AND (b = 'abcde'::text)) +(3 rows) + +explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- + Append + -> Seq Scan on hp0 + Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) + -> Seq Scan on hp2 + Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) + -> Seq Scan on hp3 + Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) +(7 rows) + +drop table hp; +-- -- Test runtime partition pruning -- create table ab (a int not null, b int not null) partition by list (a); diff --git a/src/test/regress/expected/partition_prune_hash.out b/src/test/regress/expected/partition_prune_hash.out deleted file mode 100644 index fbba3f1ff8..0000000000 --- a/src/test/regress/expected/partition_prune_hash.out +++ /dev/null @@ -1,189 +0,0 @@ --- --- Test Partition pruning for HASH partitioning --- We keep this as a seperate test as hash functions return --- values will vary based on CPU architecture. --- -create table hp (a int, b text) partition by hash (a, b); -create table hp0 partition of hp for values with (modulus 4, remainder 0); -create table hp3 partition of hp for values with (modulus 4, remainder 3); -create table hp1 partition of hp for values with (modulus 4, remainder 1); -create table hp2 partition of hp for values with (modulus 4, remainder 2); -insert into hp values (null, null); -insert into hp values (1, null); -insert into hp values (1, 'xxx'); -insert into hp values (null, 'xxx'); -insert into hp values (10, 'xxx'); -insert into hp values (10, 'yyy'); -select tableoid::regclass, * from hp order by 1; - tableoid | a | b -----------+----+----- - hp0 | | - hp0 | 1 | - hp0 | 1 | xxx - hp3 | 10 | yyy - hp1 | | xxx - hp2 | 10 | xxx -(6 rows) - --- partial keys won't prune, nor would non-equality conditions -explain (costs off) select * from hp where a = 1; - QUERY PLAN -------------------------- - Append - -> Seq Scan on hp0 - Filter: (a = 1) - -> Seq Scan on hp1 - Filter: (a = 1) - -> Seq Scan on hp2 - Filter: (a = 1) - -> Seq Scan on hp3 - Filter: (a = 1) -(9 rows) - -explain (costs off) select * from hp where b = 'xxx'; - QUERY PLAN ------------------------------------ - Append - -> Seq Scan on hp0 - Filter: (b = 'xxx'::text) - -> Seq Scan on hp1 - Filter: (b = 'xxx'::text) - -> Seq Scan on hp2 - Filter: (b = 'xxx'::text) - -> Seq Scan on hp3 - Filter: (b = 'xxx'::text) -(9 rows) - -explain (costs off) select * from hp where a is null; - QUERY PLAN ------------------------------ - Append - -> Seq Scan on hp0 - Filter: (a IS NULL) - -> Seq Scan on hp1 - Filter: (a IS NULL) - -> Seq Scan on hp2 - Filter: (a IS NULL) - -> Seq Scan on hp3 - Filter: (a IS NULL) -(9 rows) - -explain (costs off) select * from hp where b is null; - QUERY PLAN ------------------------------ - Append - -> Seq Scan on hp0 - Filter: (b IS NULL) - -> Seq Scan on hp1 - Filter: (b IS NULL) - -> Seq Scan on hp2 - Filter: (b IS NULL) - -> Seq Scan on hp3 - Filter: (b IS NULL) -(9 rows) - -explain (costs off) select * from hp where a < 1 and b = 'xxx'; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a < 1) AND (b = 'xxx'::text)) - -> Seq Scan on hp1 - Filter: ((a < 1) AND (b = 'xxx'::text)) - -> Seq Scan on hp2 - Filter: ((a < 1) AND (b = 'xxx'::text)) - -> Seq Scan on hp3 - Filter: ((a < 1) AND (b = 'xxx'::text)) -(9 rows) - -explain (costs off) select * from hp where a <> 1 and b = 'yyy'; - QUERY PLAN --------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a <> 1) AND (b = 'yyy'::text)) - -> Seq Scan on hp1 - Filter: ((a <> 1) AND (b = 'yyy'::text)) - -> Seq Scan on hp2 - Filter: ((a <> 1) AND (b = 'yyy'::text)) - -> Seq Scan on hp3 - Filter: ((a <> 1) AND (b = 'yyy'::text)) -(9 rows) - --- pruning should work if non-null values are provided for all the keys -explain (costs off) select * from hp where a is null and b is null; - QUERY PLAN ------------------------------------------------ - Append - -> Seq Scan on hp0 - Filter: ((a IS NULL) AND (b IS NULL)) -(3 rows) - -explain (costs off) select * from hp where a = 1 and b is null; - QUERY PLAN -------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((b IS NULL) AND (a = 1)) -(3 rows) - -explain (costs off) select * from hp where a = 1 and b = 'xxx'; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a = 1) AND (b = 'xxx'::text)) -(3 rows) - -explain (costs off) select * from hp where a is null and b = 'xxx'; - QUERY PLAN ------------------------------------------------------ - Append - -> Seq Scan on hp1 - Filter: ((a IS NULL) AND (b = 'xxx'::text)) -(3 rows) - -explain (costs off) select * from hp where a = 10 and b = 'xxx'; - QUERY PLAN --------------------------------------------------- - Append - -> Seq Scan on hp2 - Filter: ((a = 10) AND (b = 'xxx'::text)) -(3 rows) - -explain (costs off) select * from hp where a = 10 and b = 'yyy'; - QUERY PLAN --------------------------------------------------- - Append - -> Seq Scan on hp3 - Filter: ((a = 10) AND (b = 'yyy'::text)) -(3 rows) - -explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10 and b = 'xxx') or (a is null and b is null); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) - -> Seq Scan on hp2 - Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) - -> Seq Scan on hp3 - Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) -(7 rows) - --- hash partitiong pruning doesn't occur with <> operator clauses -explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; - QUERY PLAN ---------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) - -> Seq Scan on hp1 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) - -> Seq Scan on hp2 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) - -> Seq Scan on hp3 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) -(9 rows) - -drop table hp; diff --git a/src/test/regress/expected/partition_prune_hash_1.out b/src/test/regress/expected/partition_prune_hash_1.out deleted file mode 100644 index 4a26a0e277..0000000000 --- a/src/test/regress/expected/partition_prune_hash_1.out +++ /dev/null @@ -1,187 +0,0 @@ --- --- Test Partition pruning for HASH partitioning --- We keep this as a seperate test as hash functions return --- values will vary based on CPU architecture. --- -create table hp (a int, b text) partition by hash (a, b); -create table hp0 partition of hp for values with (modulus 4, remainder 0); -create table hp3 partition of hp for values with (modulus 4, remainder 3); -create table hp1 partition of hp for values with (modulus 4, remainder 1); -create table hp2 partition of hp for values with (modulus 4, remainder 2); -insert into hp values (null, null); -insert into hp values (1, null); -insert into hp values (1, 'xxx'); -insert into hp values (null, 'xxx'); -insert into hp values (10, 'xxx'); -insert into hp values (10, 'yyy'); -select tableoid::regclass, * from hp order by 1; - tableoid | a | b -----------+----+----- - hp0 | | - hp0 | 1 | - hp0 | 10 | xxx - hp3 | | xxx - hp3 | 10 | yyy - hp2 | 1 | xxx -(6 rows) - --- partial keys won't prune, nor would non-equality conditions -explain (costs off) select * from hp where a = 1; - QUERY PLAN -------------------------- - Append - -> Seq Scan on hp0 - Filter: (a = 1) - -> Seq Scan on hp1 - Filter: (a = 1) - -> Seq Scan on hp2 - Filter: (a = 1) - -> Seq Scan on hp3 - Filter: (a = 1) -(9 rows) - -explain (costs off) select * from hp where b = 'xxx'; - QUERY PLAN ------------------------------------ - Append - -> Seq Scan on hp0 - Filter: (b = 'xxx'::text) - -> Seq Scan on hp1 - Filter: (b = 'xxx'::text) - -> Seq Scan on hp2 - Filter: (b = 'xxx'::text) - -> Seq Scan on hp3 - Filter: (b = 'xxx'::text) -(9 rows) - -explain (costs off) select * from hp where a is null; - QUERY PLAN ------------------------------ - Append - -> Seq Scan on hp0 - Filter: (a IS NULL) - -> Seq Scan on hp1 - Filter: (a IS NULL) - -> Seq Scan on hp2 - Filter: (a IS NULL) - -> Seq Scan on hp3 - Filter: (a IS NULL) -(9 rows) - -explain (costs off) select * from hp where b is null; - QUERY PLAN ------------------------------ - Append - -> Seq Scan on hp0 - Filter: (b IS NULL) - -> Seq Scan on hp1 - Filter: (b IS NULL) - -> Seq Scan on hp2 - Filter: (b IS NULL) - -> Seq Scan on hp3 - Filter: (b IS NULL) -(9 rows) - -explain (costs off) select * from hp where a < 1 and b = 'xxx'; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a < 1) AND (b = 'xxx'::text)) - -> Seq Scan on hp1 - Filter: ((a < 1) AND (b = 'xxx'::text)) - -> Seq Scan on hp2 - Filter: ((a < 1) AND (b = 'xxx'::text)) - -> Seq Scan on hp3 - Filter: ((a < 1) AND (b = 'xxx'::text)) -(9 rows) - -explain (costs off) select * from hp where a <> 1 and b = 'yyy'; - QUERY PLAN --------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a <> 1) AND (b = 'yyy'::text)) - -> Seq Scan on hp1 - Filter: ((a <> 1) AND (b = 'yyy'::text)) - -> Seq Scan on hp2 - Filter: ((a <> 1) AND (b = 'yyy'::text)) - -> Seq Scan on hp3 - Filter: ((a <> 1) AND (b = 'yyy'::text)) -(9 rows) - --- pruning should work if non-null values are provided for all the keys -explain (costs off) select * from hp where a is null and b is null; - QUERY PLAN ------------------------------------------------ - Append - -> Seq Scan on hp0 - Filter: ((a IS NULL) AND (b IS NULL)) -(3 rows) - -explain (costs off) select * from hp where a = 1 and b is null; - QUERY PLAN -------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((b IS NULL) AND (a = 1)) -(3 rows) - -explain (costs off) select * from hp where a = 1 and b = 'xxx'; - QUERY PLAN -------------------------------------------------- - Append - -> Seq Scan on hp2 - Filter: ((a = 1) AND (b = 'xxx'::text)) -(3 rows) - -explain (costs off) select * from hp where a is null and b = 'xxx'; - QUERY PLAN ------------------------------------------------------ - Append - -> Seq Scan on hp3 - Filter: ((a IS NULL) AND (b = 'xxx'::text)) -(3 rows) - -explain (costs off) select * from hp where a = 10 and b = 'xxx'; - QUERY PLAN --------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a = 10) AND (b = 'xxx'::text)) -(3 rows) - -explain (costs off) select * from hp where a = 10 and b = 'yyy'; - QUERY PLAN --------------------------------------------------- - Append - -> Seq Scan on hp3 - Filter: ((a = 10) AND (b = 'yyy'::text)) -(3 rows) - -explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10 and b = 'xxx') or (a is null and b is null); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) - -> Seq Scan on hp3 - Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL))) -(5 rows) - --- hash partitiong pruning doesn't occur with <> operator clauses -explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; - QUERY PLAN ---------------------------------------------------- - Append - -> Seq Scan on hp0 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) - -> Seq Scan on hp1 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) - -> Seq Scan on hp2 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) - -> Seq Scan on hp3 - Filter: ((a <> 1) AND (b <> 'xxx'::text)) -(9 rows) - -drop table hp; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 0d3a27ed41..839d8a4a4d 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c # ---------- # Another group of parallel tests # ---------- -test: identity partition_join partition_prune partition_prune_hash reloptions hash_part indexing partition_aggregate fast_default +test: identity partition_join partition_prune reloptions hash_part indexing partition_aggregate fast_default # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 20027c131c..12e10b3ce4 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -185,7 +185,6 @@ test: xml test: identity test: partition_join test: partition_prune -test: partition_prune_hash test: reloptions test: hash_part test: indexing diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 4929a3628b..d508a69456 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2367,21 +2367,14 @@ DROP TABLE quuux; -- check validation when attaching hash partitions --- The default hash functions as they exist today aren't portable; they can --- return different results on different machines. Depending upon how the --- values are hashed, the row may map to different partitions, which result in --- regression failure. To avoid this, let's create a non-default hash function --- that just returns the input value unchanged. -CREATE OR REPLACE FUNCTION dummy_hashint4(a int4, seed int8) RETURNS int8 AS -$$ BEGIN RETURN (a + 1 + seed); END; $$ LANGUAGE 'plpgsql' IMMUTABLE; -CREATE OPERATOR CLASS custom_opclass FOR TYPE int4 USING HASH AS -OPERATOR 1 = , FUNCTION 2 dummy_hashint4(int4, int8); +-- Use hand-rolled hash functions and operator class to get predictable result +-- on different matchines. part_test_int4_ops is defined in insert.sql. -- check that the new partition won't overlap with an existing partition CREATE TABLE hash_parted ( a int, b int -) PARTITION BY HASH (a custom_opclass); +) PARTITION BY HASH (a part_test_int4_ops); CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE fail_part (LIKE hpart_1); ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4); @@ -2519,8 +2512,6 @@ SELECT * FROM list_parted; DROP TABLE list_parted, list_parted2, range_parted; DROP TABLE fail_def_part; DROP TABLE hash_parted; -DROP OPERATOR CLASS custom_opclass USING HASH; -DROP FUNCTION dummy_hashint4(a int4, seed int8); -- more tests for certain multi-level partitioning scenarios create table p (a int, b int) partition by range (a, b); diff --git a/src/test/regress/sql/hash_part.sql b/src/test/regress/sql/hash_part.sql index 94c5eaab0c..f457ac344c 100644 --- a/src/test/regress/sql/hash_part.sql +++ b/src/test/regress/sql/hash_part.sql @@ -2,18 +2,12 @@ -- Hash partitioning. -- -CREATE OR REPLACE FUNCTION hashint4_noop(int4, int8) RETURNS int8 AS -$$SELECT coalesce($1,0)::int8$$ LANGUAGE sql IMMUTABLE; -CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING HASH AS -OPERATOR 1 = , FUNCTION 2 hashint4_noop(int4, int8); - -CREATE OR REPLACE FUNCTION hashtext_length(text, int8) RETURNS int8 AS -$$SELECT length(coalesce($1,''))::int8$$ LANGUAGE sql IMMUTABLE; -CREATE OPERATOR CLASS test_text_ops FOR TYPE text USING HASH AS -OPERATOR 1 = , FUNCTION 2 hashtext_length(text, int8); +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different matchines. See the definitions of +-- part_part_test_int4_ops and part_test_text_ops in insert.sql. CREATE TABLE mchash (a int, b text, c jsonb) - PARTITION BY HASH (a test_int4_ops, b test_text_ops); + PARTITION BY HASH (a part_test_int4_ops, b part_test_text_ops); CREATE TABLE mchash1 PARTITION OF mchash FOR VALUES WITH (MODULUS 4, REMAINDER 0); @@ -54,7 +48,7 @@ SELECT satisfies_hash_partition('mchash'::regclass, 2, 1, NULL::int, NULL::int); SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 0, ''::text); -- ok, should be true -SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 1, ''::text); +SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 2, ''::text); -- argument via variadic syntax, should fail because not all partitioning -- columns are of the correct type @@ -63,7 +57,7 @@ SELECT satisfies_hash_partition('mchash'::regclass, 2, 1, -- multiple partitioning columns of the same type CREATE TABLE mcinthash (a int, b int, c jsonb) - PARTITION BY HASH (a test_int4_ops, b test_int4_ops); + PARTITION BY HASH (a part_test_int4_ops, b part_test_int4_ops); -- now variadic should work, should be false SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, @@ -71,7 +65,7 @@ SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, -- should be true SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, - variadic array[1, 0]); + variadic array[0, 1]); -- wrong length SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, @@ -84,7 +78,3 @@ SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0, -- cleanup DROP TABLE mchash; DROP TABLE mcinthash; -DROP OPERATOR CLASS test_text_ops USING hash; -DROP OPERATOR CLASS test_int4_ops USING hash; -DROP FUNCTION hashint4_noop(int4, int8); -DROP FUNCTION hashtext_length(text, int8); diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index a16f2a7f89..a7f659bc2b 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -228,16 +228,36 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p -- direct partition inserts should check hash partition bound constraint --- create custom operator class and hash function, for the same reason --- explained in alter_table.sql -create or replace function dummy_hashint4(a int4, seed int8) returns int8 as -$$ begin return (a + seed); end; $$ language 'plpgsql' immutable; -create operator class custom_opclass for type int4 using hash as -operator 1 = , function 2 dummy_hashint4(int4, int8); +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different matchines. The hash function for int4 simply returns +-- the sum of the values passed to it and the one for text returns the length +-- of the non-empty string value passed to it or 0. + +create or replace function part_hashint4_noop(value int4, seed int8) +returns int8 as $$ +select value + seed; +$$ language sql immutable; + +create operator class part_test_int4_ops +for type int4 +using hash as +operator 1 =, +function 2 part_hashint4_noop(int4, int8); + +create or replace function part_hashtext_length(value text, seed int8) +RETURNS int8 AS $$ +select length(coalesce(value, ''))::int8 +$$ language sql immutable; + +create operator class part_test_text_ops +for type text +using hash as +operator 1 =, +function 2 part_hashtext_length(text, int8); create table hash_parted ( a int -) partition by hash (a custom_opclass); +) partition by hash (a part_test_int4_ops); create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0); create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1); create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2); @@ -263,8 +283,6 @@ from hash_parted order by part; -- cleanup drop table range_parted, list_parted; drop table hash_parted; -drop operator class custom_opclass using hash; -drop function dummy_hashint4(a int4, seed int8); -- test that a default partition added as the first partition accepts any value -- including null diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 7fe93bbc04..19dd381514 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -238,6 +238,48 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000; drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; +-- +-- Test Partition pruning for HASH partitioning +-- +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different matchines. See the definitions of +-- part_part_test_int4_ops and part_test_text_ops in insert.sql. +-- + +create table hp (a int, b text) partition by hash (a part_test_int4_ops, b part_test_text_ops); +create table hp0 partition of hp for values with (modulus 4, remainder 0); +create table hp3 partition of hp for values with (modulus 4, remainder 3); +create table hp1 partition of hp for values with (modulus 4, remainder 1); +create table hp2 partition of hp for values with (modulus 4, remainder 2); + +insert into hp values (null, null); +insert into hp values (1, null); +insert into hp values (1, 'xxx'); +insert into hp values (null, 'xxx'); +insert into hp values (2, 'xxx'); +insert into hp values (1, 'abcde'); +select tableoid::regclass, * from hp order by 1; + +-- partial keys won't prune, nor would non-equality conditions +explain (costs off) select * from hp where a = 1; +explain (costs off) select * from hp where b = 'xxx'; +explain (costs off) select * from hp where a is null; +explain (costs off) select * from hp where b is null; +explain (costs off) select * from hp where a < 1 and b = 'xxx'; +explain (costs off) select * from hp where a <> 1 and b = 'yyy'; +explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; + +-- pruning should work if either a value or a IS NULL clause is provided for +-- each of the keys +explain (costs off) select * from hp where a is null and b is null; +explain (costs off) select * from hp where a = 1 and b is null; +explain (costs off) select * from hp where a = 1 and b = 'xxx'; +explain (costs off) select * from hp where a is null and b = 'xxx'; +explain (costs off) select * from hp where a = 2 and b = 'xxx'; +explain (costs off) select * from hp where a = 1 and b = 'abcde'; +explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null); + +drop table hp; -- -- Test runtime partition pruning @@ -587,4 +629,4 @@ select * from boolp where a = (select value from boolvalues where not value); drop table boolp; -reset enable_indexonlyscan; \ No newline at end of file +reset enable_indexonlyscan; diff --git a/src/test/regress/sql/partition_prune_hash.sql b/src/test/regress/sql/partition_prune_hash.sql deleted file mode 100644 index fd1783bf53..0000000000 --- a/src/test/regress/sql/partition_prune_hash.sql +++ /dev/null @@ -1,41 +0,0 @@ --- --- Test Partition pruning for HASH partitioning --- We keep this as a seperate test as hash functions return --- values will vary based on CPU architecture. --- - -create table hp (a int, b text) partition by hash (a, b); -create table hp0 partition of hp for values with (modulus 4, remainder 0); -create table hp3 partition of hp for values with (modulus 4, remainder 3); -create table hp1 partition of hp for values with (modulus 4, remainder 1); -create table hp2 partition of hp for values with (modulus 4, remainder 2); - -insert into hp values (null, null); -insert into hp values (1, null); -insert into hp values (1, 'xxx'); -insert into hp values (null, 'xxx'); -insert into hp values (10, 'xxx'); -insert into hp values (10, 'yyy'); -select tableoid::regclass, * from hp order by 1; - --- partial keys won't prune, nor would non-equality conditions -explain (costs off) select * from hp where a = 1; -explain (costs off) select * from hp where b = 'xxx'; -explain (costs off) select * from hp where a is null; -explain (costs off) select * from hp where b is null; -explain (costs off) select * from hp where a < 1 and b = 'xxx'; -explain (costs off) select * from hp where a <> 1 and b = 'yyy'; - --- pruning should work if non-null values are provided for all the keys -explain (costs off) select * from hp where a is null and b is null; -explain (costs off) select * from hp where a = 1 and b is null; -explain (costs off) select * from hp where a = 1 and b = 'xxx'; -explain (costs off) select * from hp where a is null and b = 'xxx'; -explain (costs off) select * from hp where a = 10 and b = 'xxx'; -explain (costs off) select * from hp where a = 10 and b = 'yyy'; -explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10 and b = 'xxx') or (a is null and b is null); - --- hash partitiong pruning doesn't occur with <> operator clauses -explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; - -drop table hp; -- 2.11.0
