Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit82d4a2a

Browse files
committed
Enable hash partitioning of text arrays
hash_array_extended() needs to pass PG_GET_COLLATION() to the hashfunction of the element type. Otherwise, the hash function of acollation-aware data type such as text will error out, since theintroduction of nondeterministic collation made hash functions requirea collation, too.The consequence of this is that before this change, hash partitioningusing 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 parent7d39586 commit82d4a2a

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
@@ -4050,7 +4050,7 @@ hash_array_extended(PG_FUNCTION_ARGS)
40504050
typalign=typentry->typalign;
40514051

40524052
InitFunctionCallInfoData(*locfcinfo,&typentry->hash_extended_proc_finfo,2,
4053-
InvalidOid,NULL,NULL);
4053+
PG_GET_COLLATION(),NULL,NULL);
40544054

40554055
/* Loop over source data */
40564056
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
@@ -1215,6 +1215,30 @@ SELECT * FROM test6 WHERE b = 'äbc' COLLATE ctest_nondet;
12151215
2 | äbc
12161216
(2 rows)
12171217

1218+
-- same with arrays
1219+
CREATE TABLE test6a (a int, b text[]);
1220+
INSERT INTO test6a VALUES (1, ARRAY[U&'\00E4bc']);
1221+
INSERT INTO test6a VALUES (2, ARRAY[U&'\0061\0308bc']);
1222+
SELECT * FROM test6a;
1223+
a | b
1224+
---+-------
1225+
1 | {äbc}
1226+
2 | {äbc}
1227+
(2 rows)
1228+
1229+
SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_det;
1230+
a | b
1231+
---+-------
1232+
1 | {äbc}
1233+
(1 row)
1234+
1235+
SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet;
1236+
a | b
1237+
---+-------
1238+
1 | {äbc}
1239+
2 | {äbc}
1240+
(2 rows)
1241+
12181242
CREATE COLLATION case_sensitive (provider = icu, locale = '');
12191243
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
12201244
SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
@@ -1842,6 +1866,19 @@ SELECT (SELECT count(*) FROM test22_0) = (SELECT count(*) FROM test22_1);
18421866
t
18431867
(1 row)
18441868

1869+
-- same with arrays
1870+
CREATE TABLE test22a (a int, b text[] COLLATE case_sensitive) PARTITION BY HASH (b);
1871+
CREATE TABLE test22a_0 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 0);
1872+
CREATE TABLE test22a_1 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 1);
1873+
INSERT INTO test22a VALUES (1, ARRAY['def']);
1874+
INSERT INTO test22a VALUES (2, ARRAY['DEF']);
1875+
-- they end up in different partitions
1876+
SELECT (SELECT count(*) FROM test22a_0) = (SELECT count(*) FROM test22a_1);
1877+
?column?
1878+
----------
1879+
t
1880+
(1 row)
1881+
18451882
CREATE TABLE test23 (a int, b text COLLATE case_insensitive) PARTITION BY HASH (b);
18461883
CREATE TABLE test23_0 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
18471884
CREATE TABLE test23_1 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
@@ -1854,6 +1891,19 @@ SELECT (SELECT count(*) FROM test23_0) <> (SELECT count(*) FROM test23_1);
18541891
t
18551892
(1 row)
18561893

1894+
-- same with arrays
1895+
CREATE TABLE test23a (a int, b text[] COLLATE case_insensitive) PARTITION BY HASH (b);
1896+
CREATE TABLE test23a_0 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 0);
1897+
CREATE TABLE test23a_1 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 1);
1898+
INSERT INTO test23a VALUES (1, ARRAY['def']);
1899+
INSERT INTO test23a VALUES (2, ARRAY['DEF']);
1900+
-- they end up in the same partition (but it's platform-dependent which one)
1901+
SELECT (SELECT count(*) FROM test23a_0) <> (SELECT count(*) FROM test23a_1);
1902+
?column?
1903+
----------
1904+
t
1905+
(1 row)
1906+
18571907
CREATE TABLE test30 (a int, b char(3) COLLATE case_insensitive) PARTITION BY LIST (b);
18581908
CREATE TABLE test30_1 PARTITION OF test30 FOR VALUES IN ('abc');
18591909
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
@@ -483,6 +483,14 @@ SELECT * FROM test6;
483483
SELECT*FROM test6WHERE b='äbc' COLLATE ctest_det;
484484
SELECT*FROM test6WHERE b='äbc' COLLATE ctest_nondet;
485485

486+
-- same with arrays
487+
CREATETABLEtest6a (aint, btext[]);
488+
INSERT INTO test6aVALUES (1, ARRAY[U&'\00E4bc']);
489+
INSERT INTO test6aVALUES (2, ARRAY[U&'\0061\0308bc']);
490+
SELECT*FROM test6a;
491+
SELECT*FROM test6aWHERE b= ARRAY['äbc'] COLLATE ctest_det;
492+
SELECT*FROM test6aWHERE b= ARRAY['äbc'] COLLATE ctest_nondet;
493+
486494
CREATE COLLATION case_sensitive (provider= icu, locale='');
487495
CREATE COLLATION case_insensitive (provider= icu, locale='@colStrength=secondary', deterministic= false);
488496

@@ -685,6 +693,15 @@ INSERT INTO test22 VALUES (2, 'DEF');
685693
-- they end up in different partitions
686694
SELECT (SELECTcount(*)FROM test22_0)= (SELECTcount(*)FROM test22_1);
687695

696+
-- same with arrays
697+
CREATETABLEtest22a (aint, btext[] COLLATE case_sensitive) PARTITION BY HASH (b);
698+
CREATETABLEtest22a_0 PARTITION OF test22a FORVALUES WITH (MODULUS2, REMAINDER0);
699+
CREATETABLEtest22a_1 PARTITION OF test22a FORVALUES WITH (MODULUS2, REMAINDER1);
700+
INSERT INTO test22aVALUES (1, ARRAY['def']);
701+
INSERT INTO test22aVALUES (2, ARRAY['DEF']);
702+
-- they end up in different partitions
703+
SELECT (SELECTcount(*)FROM test22a_0)= (SELECTcount(*)FROM test22a_1);
704+
688705
CREATETABLEtest23 (aint, btext COLLATE case_insensitive) PARTITION BY HASH (b);
689706
CREATETABLEtest23_0 PARTITION OF test23 FORVALUES WITH (MODULUS2, REMAINDER0);
690707
CREATETABLEtest23_1 PARTITION OF test23 FORVALUES WITH (MODULUS2, REMAINDER1);
@@ -693,6 +710,15 @@ INSERT INTO test23 VALUES (2, 'DEF');
693710
-- they end up in the same partition (but it's platform-dependent which one)
694711
SELECT (SELECTcount(*)FROM test23_0)<> (SELECTcount(*)FROM test23_1);
695712

713+
-- same with arrays
714+
CREATETABLEtest23a (aint, btext[] COLLATE case_insensitive) PARTITION BY HASH (b);
715+
CREATETABLEtest23a_0 PARTITION OF test23a FORVALUES WITH (MODULUS2, REMAINDER0);
716+
CREATETABLEtest23a_1 PARTITION OF test23a FORVALUES WITH (MODULUS2, REMAINDER1);
717+
INSERT INTO test23aVALUES (1, ARRAY['def']);
718+
INSERT INTO test23aVALUES (2, ARRAY['DEF']);
719+
-- they end up in the same partition (but it's platform-dependent which one)
720+
SELECT (SELECTcount(*)FROM test23a_0)<> (SELECTcount(*)FROM test23a_1);
721+
696722
CREATETABLEtest30 (aint, bchar(3) COLLATE case_insensitive) PARTITION BY LIST (b);
697723
CREATETABLEtest30_1 PARTITION OF test30 FORVALUESIN ('abc');
698724
INSERT INTO test30VALUES (1,'abc');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp