1--
2-- Hash partitioning.
3--
4
5-- Use hand-rolled hash functions and operator classes to get predictable
6-- result on different machines.  See the definitions of
7-- part_part_test_int4_ops and part_test_text_ops in insert.sql.
8
9CREATE TABLE mchash (a int, b text, c jsonb)
10  PARTITION BY HASH (a part_test_int4_ops, b part_test_text_ops);
11CREATE TABLE mchash1
12  PARTITION OF mchash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
13
14-- invalid OID, no such table
15SELECT satisfies_hash_partition(0, 4, 0, NULL);
16
17-- not partitioned
18SELECT satisfies_hash_partition('tenk1'::regclass, 4, 0, NULL);
19
20-- partition rather than the parent
21SELECT satisfies_hash_partition('mchash1'::regclass, 4, 0, NULL);
22
23-- invalid modulus
24SELECT satisfies_hash_partition('mchash'::regclass, 0, 0, NULL);
25
26-- remainder too small
27SELECT satisfies_hash_partition('mchash'::regclass, 1, -1, NULL);
28
29-- remainder too large
30SELECT satisfies_hash_partition('mchash'::regclass, 1, 1, NULL);
31
32-- modulus is null
33SELECT satisfies_hash_partition('mchash'::regclass, NULL, 0, NULL);
34
35-- remainder is null
36SELECT satisfies_hash_partition('mchash'::regclass, 4, NULL, NULL);
37
38-- too many arguments
39SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, NULL::int, NULL::text, NULL::json);
40
41-- too few arguments
42SELECT satisfies_hash_partition('mchash'::regclass, 3, 1, NULL::int);
43
44-- wrong argument type
45SELECT satisfies_hash_partition('mchash'::regclass, 2, 1, NULL::int, NULL::int);
46
47-- ok, should be false
48SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 0, ''::text);
49
50-- ok, should be true
51SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 2, ''::text);
52
53-- argument via variadic syntax, should fail because not all partitioning
54-- columns are of the correct type
55SELECT satisfies_hash_partition('mchash'::regclass, 2, 1,
56								variadic array[1,2]::int[]);
57
58-- multiple partitioning columns of the same type
59CREATE TABLE mcinthash (a int, b int, c jsonb)
60  PARTITION BY HASH (a part_test_int4_ops, b part_test_int4_ops);
61
62-- now variadic should work, should be false
63SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
64								variadic array[0, 0]);
65
66-- should be true
67SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
68								variadic array[0, 1]);
69
70-- wrong length
71SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
72								variadic array[]::int[]);
73
74-- wrong type
75SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
76								variadic array[now(), now()]);
77
78-- check satisfies_hash_partition passes correct collation
79create table text_hashp (a text) partition by hash (a);
80create table text_hashp0 partition of text_hashp for values with (modulus 2, remainder 0);
81create table text_hashp1 partition of text_hashp for values with (modulus 2, remainder 1);
82-- The result here should always be true, because 'xxx' must belong to
83-- one of the two defined partitions
84select satisfies_hash_partition('text_hashp'::regclass, 2, 0, 'xxx'::text) OR
85	   satisfies_hash_partition('text_hashp'::regclass, 2, 1, 'xxx'::text) AS satisfies;
86
87-- cleanup
88DROP TABLE mchash;
89DROP TABLE mcinthash;
90DROP TABLE text_hashp;
91