Skip to content

Commit ea90879

Browse files
peterepetere
committed
Enable hash partitioning of text arrays
hash_array_extended() needs to pass PG_GET_COLLATION() to the hash function of the element type. Otherwise, the hash function of a collation-aware data type such as text will error out, since the introduction of nondeterministic collation made hash functions require a collation, too. The consequence of this is that before this change, hash partitioning using an array over text in the partition key would not work. Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Michael Paquier <michael@paquier.xyz> Discussion: https://www.postgresql.org/message-id/flat/32c1fdae-95c6-5dc6-058a-a90330a3b621%40enterprisedb.com
1 parent 55416b2 commit ea90879

File tree

3 files changed

+77
-1
lines changed

3 files changed

+77
-1
lines changed

‎src/backend/utils/adt/arrayfuncs.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4046,7 +4046,7 @@ hash_array_extended(PG_FUNCTION_ARGS)
40464046
typalign = typentry->typalign;
40474047

40484048
InitFunctionCallInfoData(*locfcinfo, &typentry->hash_extended_proc_finfo, 2,
4049-
InvalidOid, NULL, NULL);
4049+
PG_GET_COLLATION(), NULL, NULL);
40504050

40514051
/* Loop over source data */
40524052
nitems = ArrayGetNItems(ndims, dims);

‎src/test/regress/expected/collate.icu.utf8.out

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1208,6 +1208,30 @@ SELECT * FROM test6 WHERE b = 'äbc' COLLATE ctest_nondet;
12081208
2 | äbc
12091209
(2 rows)
12101210

1211+
-- same with arrays
1212+
CREATE TABLE test6a (a int, b text[]);
1213+
INSERT INTO test6a VALUES (1, ARRAY[U&'\00E4bc']);
1214+
INSERT INTO test6a VALUES (2, ARRAY[U&'\0061\0308bc']);
1215+
SELECT * FROM test6a;
1216+
a | b
1217+
---+-------
1218+
1 | {äbc}
1219+
2 | {äbc}
1220+
(2 rows)
1221+
1222+
SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_det;
1223+
a | b
1224+
---+-------
1225+
1 | {äbc}
1226+
(1 row)
1227+
1228+
SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet;
1229+
a | b
1230+
---+-------
1231+
1 | {äbc}
1232+
2 | {äbc}
1233+
(2 rows)
1234+
12111235
CREATE COLLATION case_sensitive (provider = icu, locale = '');
12121236
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
12131237
SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
@@ -1835,6 +1859,19 @@ SELECT (SELECT count(*) FROM test22_0) = (SELECT count(*) FROM test22_1);
18351859
t
18361860
(1 row)
18371861

1862+
-- same with arrays
1863+
CREATE TABLE test22a (a int, b text[] COLLATE case_sensitive) PARTITION BY HASH (b);
1864+
CREATE TABLE test22a_0 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 0);
1865+
CREATE TABLE test22a_1 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 1);
1866+
INSERT INTO test22a VALUES (1, ARRAY['def']);
1867+
INSERT INTO test22a VALUES (2, ARRAY['DEF']);
1868+
-- they end up in different partitions
1869+
SELECT (SELECT count(*) FROM test22a_0) = (SELECT count(*) FROM test22a_1);
1870+
?column?
1871+
----------
1872+
t
1873+
(1 row)
1874+
18381875
CREATE TABLE test23 (a int, b text COLLATE case_insensitive) PARTITION BY HASH (b);
18391876
CREATE TABLE test23_0 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
18401877
CREATE TABLE test23_1 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
@@ -1847,6 +1884,19 @@ SELECT (SELECT count(*) FROM test23_0) <> (SELECT count(*) FROM test23_1);
18471884
t
18481885
(1 row)
18491886

1887+
-- same with arrays
1888+
CREATE TABLE test23a (a int, b text[] COLLATE case_insensitive) PARTITION BY HASH (b);
1889+
CREATE TABLE test23a_0 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 0);
1890+
CREATE TABLE test23a_1 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 1);
1891+
INSERT INTO test23a VALUES (1, ARRAY['def']);
1892+
INSERT INTO test23a VALUES (2, ARRAY['DEF']);
1893+
-- they end up in the same partition (but it's platform-dependent which one)
1894+
SELECT (SELECT count(*) FROM test23a_0) <> (SELECT count(*) FROM test23a_1);
1895+
?column?
1896+
----------
1897+
t
1898+
(1 row)
1899+
18501900
CREATE TABLE test30 (a int, b char(3) COLLATE case_insensitive) PARTITION BY LIST (b);
18511901
CREATE TABLE test30_1 PARTITION OF test30 FOR VALUES IN ('abc');
18521902
INSERT INTO test30 VALUES (1, 'abc');

‎src/test/regress/sql/collate.icu.utf8.sql

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -475,6 +475,14 @@ SELECT * FROM test6;
475475
SELECT * FROM test6 WHERE b = 'äbc' COLLATE ctest_det;
476476
SELECT * FROM test6 WHERE b = 'äbc' COLLATE ctest_nondet;
477477

478+
-- same with arrays
479+
CREATE TABLE test6a (a int, b text[]);
480+
INSERT INTO test6a VALUES (1, ARRAY[U&'\00E4bc']);
481+
INSERT INTO test6a VALUES (2, ARRAY[U&'\0061\0308bc']);
482+
SELECT * FROM test6a;
483+
SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_det;
484+
SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet;
485+
478486
CREATE COLLATION case_sensitive (provider = icu, locale = '');
479487
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
480488

@@ -677,6 +685,15 @@ INSERT INTO test22 VALUES (2, 'DEF');
677685
-- they end up in different partitions
678686
SELECT (SELECT count(*) FROM test22_0) = (SELECT count(*) FROM test22_1);
679687

688+
-- same with arrays
689+
CREATE TABLE test22a (a int, b text[] COLLATE case_sensitive) PARTITION BY HASH (b);
690+
CREATE TABLE test22a_0 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 0);
691+
CREATE TABLE test22a_1 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 1);
692+
INSERT INTO test22a VALUES (1, ARRAY['def']);
693+
INSERT INTO test22a VALUES (2, ARRAY['DEF']);
694+
-- they end up in different partitions
695+
SELECT (SELECT count(*) FROM test22a_0) = (SELECT count(*) FROM test22a_1);
696+
680697
CREATE TABLE test23 (a int, b text COLLATE case_insensitive) PARTITION BY HASH (b);
681698
CREATE TABLE test23_0 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
682699
CREATE TABLE test23_1 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
@@ -685,6 +702,15 @@ INSERT INTO test23 VALUES (2, 'DEF');
685702
-- they end up in the same partition (but it's platform-dependent which one)
686703
SELECT (SELECT count(*) FROM test23_0) <> (SELECT count(*) FROM test23_1);
687704

705+
-- same with arrays
706+
CREATE TABLE test23a (a int, b text[] COLLATE case_insensitive) PARTITION BY HASH (b);
707+
CREATE TABLE test23a_0 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 0);
708+
CREATE TABLE test23a_1 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 1);
709+
INSERT INTO test23a VALUES (1, ARRAY['def']);
710+
INSERT INTO test23a VALUES (2, ARRAY['DEF']);
711+
-- they end up in the same partition (but it's platform-dependent which one)
712+
SELECT (SELECT count(*) FROM test23a_0) <> (SELECT count(*) FROM test23a_1);
713+
688714
CREATE TABLE test30 (a int, b char(3) COLLATE case_insensitive) PARTITION BY LIST (b);
689715
CREATE TABLE test30_1 PARTITION OF test30 FOR VALUES IN ('abc');
690716
INSERT INTO test30 VALUES (1, 'abc');

0 commit comments

Comments
 (0)