Skip to content

Commit fafec4c

Browse files
alvherrealvherre
committed
Use custom hash opclass for hash partition pruning
This custom opclass was already in use in other tests -- defined independently in every such file. Move the definition to the earliest test that uses it, and keep it around so that later tests can reuse it. Use it in the tests for pruning of hash partitioning, and since this makes the second expected file unnecessary, put those tests back in partition_prune.sql whence they sprang. Author: Amit Langote Discussion: https://postgr.es/m/CA%2BTgmoZ0D5kJbt8eKXtvVdvTcGGWn6ehWCRSZbWytD-uzH92mQ%40mail.gmail.com
1 parent 4d0f6d3 commit fafec4c

13 files changed

+305
-495
lines changed

‎src/test/regress/expected/alter_table.out

Lines changed: 3 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -3662,20 +3662,13 @@ CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);
36623662
INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints
36633663
DROP TABLE quuux;
36643664
-- check validation when attaching hash partitions
3665-
-- The default hash functions as they exist today aren't portable; they can
3666-
-- return different results on different machines. Depending upon how the
3667-
-- values are hashed, the row may map to different partitions, which result in
3668-
-- regression failure. To avoid this, let's create a non-default hash function
3669-
-- that just returns the input value unchanged.
3670-
CREATE OR REPLACE FUNCTION dummy_hashint4(a int4, seed int8) RETURNS int8 AS
3671-
$$ BEGIN RETURN (a + 1 + seed); END; $$ LANGUAGE 'plpgsql' IMMUTABLE;
3672-
CREATE OPERATOR CLASS custom_opclass FOR TYPE int4 USING HASH AS
3673-
OPERATOR 1 = , FUNCTION 2 dummy_hashint4(int4, int8);
3665+
-- Use hand-rolled hash functions and operator class to get predictable result
3666+
-- on different matchines. part_test_int4_ops is defined in insert.sql.
36743667
-- check that the new partition won't overlap with an existing partition
36753668
CREATE TABLE hash_parted (
36763669
a int,
36773670
b int
3678-
) PARTITION BY HASH (a custom_opclass);
3671+
) PARTITION BY HASH (a part_test_int4_ops);
36793672
CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0);
36803673
CREATE TABLE fail_part (LIKE hpart_1);
36813674
ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4);
@@ -3840,8 +3833,6 @@ SELECT * FROM list_parted;
38403833
DROP TABLE list_parted, list_parted2, range_parted;
38413834
DROP TABLE fail_def_part;
38423835
DROP TABLE hash_parted;
3843-
DROP OPERATOR CLASS custom_opclass USING HASH;
3844-
DROP FUNCTION dummy_hashint4(a int4, seed int8);
38453836
-- more tests for certain multi-level partitioning scenarios
38463837
create table p (a int, b int) partition by range (a, b);
38473838
create table p1 (b int, a int not null) partition by range (b);

‎src/test/regress/expected/hash_part.out

Lines changed: 7 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,16 +1,11 @@
11
--
22
-- Hash partitioning.
33
--
4-
CREATE OR REPLACE FUNCTION hashint4_noop(int4, int8) RETURNS int8 AS
5-
$$SELECT coalesce($1,0)::int8$$ LANGUAGE sql IMMUTABLE;
6-
CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING HASH AS
7-
OPERATOR 1 = , FUNCTION 2 hashint4_noop(int4, int8);
8-
CREATE OR REPLACE FUNCTION hashtext_length(text, int8) RETURNS int8 AS
9-
$$SELECT length(coalesce($1,''))::int8$$ LANGUAGE sql IMMUTABLE;
10-
CREATE OPERATOR CLASS test_text_ops FOR TYPE text USING HASH AS
11-
OPERATOR 1 = , FUNCTION 2 hashtext_length(text, int8);
4+
-- Use hand-rolled hash functions and operator classes to get predictable
5+
-- result on different matchines. See the definitions of
6+
-- part_part_test_int4_ops and part_test_text_ops in insert.sql.
127
CREATE TABLE mchash (a int, b text, c jsonb)
13-
PARTITION BY HASH (a test_int4_ops, b test_text_ops);
8+
PARTITION BY HASH (a part_test_int4_ops, b part_test_text_ops);
149
CREATE TABLE mchash1
1510
PARTITION OF mchash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1611
-- invalid OID, no such table
@@ -66,7 +61,7 @@ SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 0, ''::text);
6661
(1 row)
6762

6863
-- ok, should be true
69-
SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 1, ''::text);
64+
SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 2, ''::text);
7065
satisfies_hash_partition
7166
--------------------------
7267
t
@@ -79,7 +74,7 @@ SELECT satisfies_hash_partition('mchash'::regclass, 2, 1,
7974
ERROR: column 2 of the partition key has type "text", but supplied value is of type "integer"
8075
-- multiple partitioning columns of the same type
8176
CREATE TABLE mcinthash (a int, b int, c jsonb)
82-
PARTITION BY HASH (a test_int4_ops, b test_int4_ops);
77+
PARTITION BY HASH (a part_test_int4_ops, b part_test_int4_ops);
8378
-- now variadic should work, should be false
8479
SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
8580
variadic array[0, 0]);
@@ -90,7 +85,7 @@ SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
9085

9186
-- should be true
9287
SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
93-
variadic array[1, 0]);
88+
variadic array[0, 1]);
9489
satisfies_hash_partition
9590
--------------------------
9691
t
@@ -107,7 +102,3 @@ ERROR: column 1 of the partition key has type "integer", but supplied value is
107102
-- cleanup
108103
DROP TABLE mchash;
109104
DROP TABLE mcinthash;
110-
DROP OPERATOR CLASS test_text_ops USING hash;
111-
DROP OPERATOR CLASS test_int4_ops USING hash;
112-
DROP FUNCTION hashint4_noop(int4, int8);
113-
DROP FUNCTION hashtext_length(text, int8);

‎src/test/regress/expected/insert.out

Lines changed: 23 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -387,15 +387,31 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
387387
(9 rows)
388388

389389
-- direct partition inserts should check hash partition bound constraint
390-
-- create custom operator class and hash function, for the same reason
391-
-- explained in alter_table.sql
392-
create or replace function dummy_hashint4(a int4, seed int8) returns int8 as
393-
$$ begin return (a + seed); end; $$ language 'plpgsql' immutable;
394-
create operator class custom_opclass for type int4 using hash as
395-
operator 1 = , function 2 dummy_hashint4(int4, int8);
390+
-- Use hand-rolled hash functions and operator classes to get predictable
391+
-- result on different matchines. The hash function for int4 simply returns
392+
-- the sum of the values passed to it and the one for text returns the length
393+
-- of the non-empty string value passed to it or 0.
394+
create or replace function part_hashint4_noop(value int4, seed int8)
395+
returns int8 as $$
396+
select value + seed;
397+
$$ language sql immutable;
398+
create operator class part_test_int4_ops
399+
for type int4
400+
using hash as
401+
operator 1 =,
402+
function 2 part_hashint4_noop(int4, int8);
403+
create or replace function part_hashtext_length(value text, seed int8)
404+
RETURNS int8 AS $$
405+
select length(coalesce(value, ''))::int8
406+
$$ language sql immutable;
407+
create operator class part_test_text_ops
408+
for type text
409+
using hash as
410+
operator 1 =,
411+
function 2 part_hashtext_length(text, int8);
396412
create table hash_parted (
397413
a int
398-
) partition by hash (a custom_opclass);
414+
) partition by hash (a part_test_int4_ops);
399415
create table hpart0 partition of hash_parted for values with (modulus 4, remainder 0);
400416
create table hpart1 partition of hash_parted for values with (modulus 4, remainder 1);
401417
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'),
449465
-- cleanup
450466
drop table range_parted, list_parted;
451467
drop table hash_parted;
452-
drop operator class custom_opclass using hash;
453-
drop function dummy_hashint4(a int4, seed int8);
454468
-- test that a default partition added as the first partition accepts any value
455469
-- including null
456470
create table list_parted (a int) partition by list (a);

‎src/test/regress/expected/partition_prune.out

Lines changed: 191 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1332,6 +1332,197 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
13321332

13331333
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
13341334
--
1335+
-- Test Partition pruning for HASH partitioning
1336+
--
1337+
-- Use hand-rolled hash functions and operator classes to get predictable
1338+
-- result on different matchines. See the definitions of
1339+
-- part_part_test_int4_ops and part_test_text_ops in insert.sql.
1340+
--
1341+
create table hp (a int, b text) partition by hash (a part_test_int4_ops, b part_test_text_ops);
1342+
create table hp0 partition of hp for values with (modulus 4, remainder 0);
1343+
create table hp3 partition of hp for values with (modulus 4, remainder 3);
1344+
create table hp1 partition of hp for values with (modulus 4, remainder 1);
1345+
create table hp2 partition of hp for values with (modulus 4, remainder 2);
1346+
insert into hp values (null, null);
1347+
insert into hp values (1, null);
1348+
insert into hp values (1, 'xxx');
1349+
insert into hp values (null, 'xxx');
1350+
insert into hp values (2, 'xxx');
1351+
insert into hp values (1, 'abcde');
1352+
select tableoid::regclass, * from hp order by 1;
1353+
tableoid | a | b
1354+
----------+---+-------
1355+
hp0 | |
1356+
hp0 | 1 | xxx
1357+
hp3 | 2 | xxx
1358+
hp1 | 1 |
1359+
hp2 | | xxx
1360+
hp2 | 1 | abcde
1361+
(6 rows)
1362+
1363+
-- partial keys won't prune, nor would non-equality conditions
1364+
explain (costs off) select * from hp where a = 1;
1365+
QUERY PLAN
1366+
-------------------------
1367+
Append
1368+
-> Seq Scan on hp0
1369+
Filter: (a = 1)
1370+
-> Seq Scan on hp1
1371+
Filter: (a = 1)
1372+
-> Seq Scan on hp2
1373+
Filter: (a = 1)
1374+
-> Seq Scan on hp3
1375+
Filter: (a = 1)
1376+
(9 rows)
1377+
1378+
explain (costs off) select * from hp where b = 'xxx';
1379+
QUERY PLAN
1380+
-----------------------------------
1381+
Append
1382+
-> Seq Scan on hp0
1383+
Filter: (b = 'xxx'::text)
1384+
-> Seq Scan on hp1
1385+
Filter: (b = 'xxx'::text)
1386+
-> Seq Scan on hp2
1387+
Filter: (b = 'xxx'::text)
1388+
-> Seq Scan on hp3
1389+
Filter: (b = 'xxx'::text)
1390+
(9 rows)
1391+
1392+
explain (costs off) select * from hp where a is null;
1393+
QUERY PLAN
1394+
-----------------------------
1395+
Append
1396+
-> Seq Scan on hp0
1397+
Filter: (a IS NULL)
1398+
-> Seq Scan on hp1
1399+
Filter: (a IS NULL)
1400+
-> Seq Scan on hp2
1401+
Filter: (a IS NULL)
1402+
-> Seq Scan on hp3
1403+
Filter: (a IS NULL)
1404+
(9 rows)
1405+
1406+
explain (costs off) select * from hp where b is null;
1407+
QUERY PLAN
1408+
-----------------------------
1409+
Append
1410+
-> Seq Scan on hp0
1411+
Filter: (b IS NULL)
1412+
-> Seq Scan on hp1
1413+
Filter: (b IS NULL)
1414+
-> Seq Scan on hp2
1415+
Filter: (b IS NULL)
1416+
-> Seq Scan on hp3
1417+
Filter: (b IS NULL)
1418+
(9 rows)
1419+
1420+
explain (costs off) select * from hp where a < 1 and b = 'xxx';
1421+
QUERY PLAN
1422+
-------------------------------------------------
1423+
Append
1424+
-> Seq Scan on hp0
1425+
Filter: ((a < 1) AND (b = 'xxx'::text))
1426+
-> Seq Scan on hp1
1427+
Filter: ((a < 1) AND (b = 'xxx'::text))
1428+
-> Seq Scan on hp2
1429+
Filter: ((a < 1) AND (b = 'xxx'::text))
1430+
-> Seq Scan on hp3
1431+
Filter: ((a < 1) AND (b = 'xxx'::text))
1432+
(9 rows)
1433+
1434+
explain (costs off) select * from hp where a <> 1 and b = 'yyy';
1435+
QUERY PLAN
1436+
--------------------------------------------------
1437+
Append
1438+
-> Seq Scan on hp0
1439+
Filter: ((a <> 1) AND (b = 'yyy'::text))
1440+
-> Seq Scan on hp1
1441+
Filter: ((a <> 1) AND (b = 'yyy'::text))
1442+
-> Seq Scan on hp2
1443+
Filter: ((a <> 1) AND (b = 'yyy'::text))
1444+
-> Seq Scan on hp3
1445+
Filter: ((a <> 1) AND (b = 'yyy'::text))
1446+
(9 rows)
1447+
1448+
explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
1449+
QUERY PLAN
1450+
---------------------------------------------------
1451+
Append
1452+
-> Seq Scan on hp0
1453+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1454+
-> Seq Scan on hp1
1455+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1456+
-> Seq Scan on hp2
1457+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1458+
-> Seq Scan on hp3
1459+
Filter: ((a <> 1) AND (b <> 'xxx'::text))
1460+
(9 rows)
1461+
1462+
-- pruning should work if either a value or a IS NULL clause is provided for
1463+
-- each of the keys
1464+
explain (costs off) select * from hp where a is null and b is null;
1465+
QUERY PLAN
1466+
-----------------------------------------------
1467+
Append
1468+
-> Seq Scan on hp0
1469+
Filter: ((a IS NULL) AND (b IS NULL))
1470+
(3 rows)
1471+
1472+
explain (costs off) select * from hp where a = 1 and b is null;
1473+
QUERY PLAN
1474+
-------------------------------------------
1475+
Append
1476+
-> Seq Scan on hp1
1477+
Filter: ((b IS NULL) AND (a = 1))
1478+
(3 rows)
1479+
1480+
explain (costs off) select * from hp where a = 1 and b = 'xxx';
1481+
QUERY PLAN
1482+
-------------------------------------------------
1483+
Append
1484+
-> Seq Scan on hp0
1485+
Filter: ((a = 1) AND (b = 'xxx'::text))
1486+
(3 rows)
1487+
1488+
explain (costs off) select * from hp where a is null and b = 'xxx';
1489+
QUERY PLAN
1490+
-----------------------------------------------------
1491+
Append
1492+
-> Seq Scan on hp2
1493+
Filter: ((a IS NULL) AND (b = 'xxx'::text))
1494+
(3 rows)
1495+
1496+
explain (costs off) select * from hp where a = 2 and b = 'xxx';
1497+
QUERY PLAN
1498+
-------------------------------------------------
1499+
Append
1500+
-> Seq Scan on hp3
1501+
Filter: ((a = 2) AND (b = 'xxx'::text))
1502+
(3 rows)
1503+
1504+
explain (costs off) select * from hp where a = 1 and b = 'abcde';
1505+
QUERY PLAN
1506+
---------------------------------------------------
1507+
Append
1508+
-> Seq Scan on hp2
1509+
Filter: ((a = 1) AND (b = 'abcde'::text))
1510+
(3 rows)
1511+
1512+
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);
1513+
QUERY PLAN
1514+
-------------------------------------------------------------------------------------------------------------------------
1515+
Append
1516+
-> Seq Scan on hp0
1517+
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1518+
-> Seq Scan on hp2
1519+
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1520+
-> Seq Scan on hp3
1521+
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1522+
(7 rows)
1523+
1524+
drop table hp;
1525+
--
13351526
-- Test runtime partition pruning
13361527
--
13371528
create table ab (a int not null, b int not null) partition by list (a);

0 commit comments

Comments
 (0)