1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
--
-- Hash partitioning.
--
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different machines. 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 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
SELECT satisfies_hash_partition(0, 4, 0, NULL);
ERROR: could not open relation with OID 0
-- not partitioned
SELECT satisfies_hash_partition('tenk1'::regclass, 4, 0, NULL);
ERROR: "tenk1" is not a hash partitioned table
-- partition rather than the parent
SELECT satisfies_hash_partition('mchash1'::regclass, 4, 0, NULL);
ERROR: "mchash1" is not a hash partitioned table
-- invalid modulus
SELECT satisfies_hash_partition('mchash'::regclass, 0, 0, NULL);
ERROR: modulus for hash partition must be an integer value greater than zero
-- remainder too small
SELECT satisfies_hash_partition('mchash'::regclass, 1, -1, NULL);
ERROR: remainder for hash partition must be an integer value greater than or equal to zero
-- remainder too large
SELECT satisfies_hash_partition('mchash'::regclass, 1, 1, NULL);
ERROR: remainder for hash partition must be less than modulus
-- modulus is null
SELECT satisfies_hash_partition('mchash'::regclass, NULL, 0, NULL);
satisfies_hash_partition
--------------------------
f
(1 row)
-- remainder is null
SELECT satisfies_hash_partition('mchash'::regclass, 4, NULL, NULL);
satisfies_hash_partition
--------------------------
f
(1 row)
-- too many arguments
SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, NULL::int, NULL::text, NULL::json);
ERROR: number of partitioning columns (2) does not match number of partition keys provided (3)
-- too few arguments
SELECT satisfies_hash_partition('mchash'::regclass, 3, 1, NULL::int);
ERROR: number of partitioning columns (2) does not match number of partition keys provided (1)
-- wrong argument type
SELECT satisfies_hash_partition('mchash'::regclass, 2, 1, NULL::int, NULL::int);
ERROR: column 2 of the partition key has type text, but supplied value is of type integer
-- ok, should be false
SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 0, ''::text);
satisfies_hash_partition
--------------------------
f
(1 row)
-- ok, should be true
SELECT satisfies_hash_partition('mchash'::regclass, 4, 0, 2, ''::text);
satisfies_hash_partition
--------------------------
t
(1 row)
-- argument via variadic syntax, should fail because not all partitioning
-- columns are of the correct type
SELECT satisfies_hash_partition('mchash'::regclass, 2, 1,
variadic array[1,2]::int[]);
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 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]);
satisfies_hash_partition
--------------------------
f
(1 row)
-- should be true
SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
variadic array[0, 1]);
satisfies_hash_partition
--------------------------
t
(1 row)
-- wrong length
SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
variadic array[]::int[]);
ERROR: number of partitioning columns (2) does not match number of partition keys provided (0)
-- wrong type
SELECT satisfies_hash_partition('mcinthash'::regclass, 4, 0,
variadic array[now(), now()]);
ERROR: column 1 of the partition key has type "integer", but supplied value is of type "timestamp with time zone"
-- check satisfies_hash_partition passes correct collation
create table text_hashp (a text) partition by hash (a);
create table text_hashp0 partition of text_hashp for values with (modulus 2, remainder 0);
create table text_hashp1 partition of text_hashp for values with (modulus 2, remainder 1);
-- The result here should always be true, because 'xxx' must belong to
-- one of the two defined partitions
select satisfies_hash_partition('text_hashp'::regclass, 2, 0, 'xxx'::text) OR
satisfies_hash_partition('text_hashp'::regclass, 2, 1, 'xxx'::text) AS satisfies;
satisfies
-----------
t
(1 row)
-- cleanup
DROP TABLE mchash;
DROP TABLE mcinthash;
DROP TABLE text_hashp;
|
