Skip to content

Commit 3ccdc6f

Browse files
robertmhaasrobertmhaas
committed
Fix list partition constraints for partition keys of array type.
The old code generated always generated a constraint of the form col = ANY(ARRAY[val1, val2, ...]), but that's invalid when col is an array type. Instead, generate col = val when there's only one value, col = val1 OR col = val2 OR ... when there are multiple values and col is of array type, and the old form when there are multiple values and col is not of an array type. As a side benefit, this makes constraint exclusion able to prune a list partition declared to accept a single Boolean value, which didn't work before. Amit Langote, reviewed by Etsuro Fujita Discussion: http://postgr.es/m/97267195-e235-89d1-a41a-c110198dfce9@lab.ntt.co.jp
1 parent 0ff5bd7 commit 3ccdc6f

File tree

5 files changed

+93
-43
lines changed

5 files changed

+93
-43
lines changed

‎src/backend/catalog/partition.c

Lines changed: 67 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1625,18 +1625,60 @@ make_partition_op_expr(PartitionKey key, int keynum,
16251625
{
16261626
case PARTITION_STRATEGY_LIST:
16271627
{
1628-
ScalarArrayOpExpr *saopexpr;
1629-
1630-
/* Build leftop = ANY (rightop) */
1631-
saopexpr = makeNode(ScalarArrayOpExpr);
1632-
saopexpr->opno = operoid;
1633-
saopexpr->opfuncid = get_opcode(operoid);
1634-
saopexpr->useOr = true;
1635-
saopexpr->inputcollid = key->partcollation[keynum];
1636-
saopexpr->args = list_make2(arg1, arg2);
1637-
saopexpr->location = -1;
1638-
1639-
result = (Expr *) saopexpr;
1628+
List *elems = (List *) arg2;
1629+
int nelems = list_length(elems);
1630+
1631+
Assert(nelems >= 1);
1632+
Assert(keynum == 0);
1633+
1634+
if (nelems > 1 &&
1635+
!type_is_array(key->parttypid[keynum]))
1636+
{
1637+
ArrayExpr *arrexpr;
1638+
ScalarArrayOpExpr *saopexpr;
1639+
1640+
/* Construct an ArrayExpr for the right-hand inputs */
1641+
arrexpr = makeNode(ArrayExpr);
1642+
arrexpr->array_typeid =
1643+
get_array_type(key->parttypid[keynum]);
1644+
arrexpr->array_collid = key->parttypcoll[keynum];
1645+
arrexpr->element_typeid = key->parttypid[keynum];
1646+
arrexpr->elements = elems;
1647+
arrexpr->multidims = false;
1648+
arrexpr->location = -1;
1649+
1650+
/* Build leftop = ANY (rightop) */
1651+
saopexpr = makeNode(ScalarArrayOpExpr);
1652+
saopexpr->opno = operoid;
1653+
saopexpr->opfuncid = get_opcode(operoid);
1654+
saopexpr->useOr = true;
1655+
saopexpr->inputcollid = key->partcollation[keynum];
1656+
saopexpr->args = list_make2(arg1, arrexpr);
1657+
saopexpr->location = -1;
1658+
1659+
result = (Expr *) saopexpr;
1660+
}
1661+
else
1662+
{
1663+
List *elemops = NIL;
1664+
ListCell *lc;
1665+
1666+
foreach (lc, elems)
1667+
{
1668+
Expr *elem = lfirst(lc),
1669+
*elemop;
1670+
1671+
elemop = make_opclause(operoid,
1672+
BOOLOID,
1673+
false,
1674+
arg1, elem,
1675+
InvalidOid,
1676+
key->partcollation[keynum]);
1677+
elemops = lappend(elemops, elemop);
1678+
}
1679+
1680+
result = nelems > 1 ? makeBoolExpr(OR_EXPR, elemops, -1) : linitial(elemops);
1681+
}
16401682
break;
16411683
}
16421684

@@ -1758,11 +1800,10 @@ get_qual_for_list(Relation parent, PartitionBoundSpec *spec)
17581800
PartitionKey key = RelationGetPartitionKey(parent);
17591801
List *result;
17601802
Expr *keyCol;
1761-
ArrayExpr *arr;
17621803
Expr *opexpr;
17631804
NullTest *nulltest;
17641805
ListCell *cell;
1765-
List *arrelems = NIL;
1806+
List *elems = NIL;
17661807
bool list_has_null = false;
17671808

17681809
/*
@@ -1828,7 +1869,7 @@ get_qual_for_list(Relation parent, PartitionBoundSpec *spec)
18281869
false, /* isnull */
18291870
key->parttypbyval[0]);
18301871

1831-
arrelems = lappend(arrelems, val);
1872+
elems = lappend(elems, val);
18321873
}
18331874
}
18341875
else
@@ -1843,30 +1884,25 @@ get_qual_for_list(Relation parent, PartitionBoundSpec *spec)
18431884
if (val->constisnull)
18441885
list_has_null = true;
18451886
else
1846-
arrelems = lappend(arrelems, copyObject(val));
1887+
elems = lappend(elems, copyObject(val));
18471888
}
18481889
}
18491890

1850-
if (arrelems)
1891+
if (elems)
18511892
{
1852-
/* Construct an ArrayExpr for the non-null partition values */
1853-
arr = makeNode(ArrayExpr);
1854-
arr->array_typeid = !type_is_array(key->parttypid[0])
1855-
? get_array_type(key->parttypid[0])
1856-
: key->parttypid[0];
1857-
arr->array_collid = key->parttypcoll[0];
1858-
arr->element_typeid = key->parttypid[0];
1859-
arr->elements = arrelems;
1860-
arr->multidims = false;
1861-
arr->location = -1;
1862-
1863-
/* Generate the main expression, i.e., keyCol = ANY (arr) */
1893+
/*
1894+
* Generate the operator expression from the non-null partition
1895+
* values.
1896+
*/
18641897
opexpr = make_partition_op_expr(key, 0, BTEqualStrategyNumber,
1865-
keyCol, (Expr *) arr);
1898+
keyCol, (Expr *) elems);
18661899
}
18671900
else
18681901
{
1869-
/* If there are no partition values, we don't need an = ANY expr */
1902+
/*
1903+
* If there are no partition values, we don't need an operator
1904+
* expression.
1905+
*/
18701906
opexpr = NULL;
18711907
}
18721908

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

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -742,7 +742,7 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
742742
a | text | | | | extended | |
743743
b | integer | | not null | 1 | plain | |
744744
Partition of: parted FOR VALUES IN ('b')
745-
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text])))
745+
Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
746746
Check constraints:
747747
"check_a" CHECK (length(a) > 0)
748748
"part_b_b_check" CHECK (b >= 0)
@@ -755,7 +755,7 @@ Check constraints:
755755
a | text | | | | extended | |
756756
b | integer | | not null | 0 | plain | |
757757
Partition of: parted FOR VALUES IN ('c')
758-
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])))
758+
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
759759
Partition key: RANGE (b)
760760
Check constraints:
761761
"check_a" CHECK (length(a) > 0)
@@ -769,7 +769,7 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
769769
a | text | | | | extended | |
770770
b | integer | | not null | 0 | plain | |
771771
Partition of: part_c FOR VALUES FROM (1) TO (10)
772-
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
772+
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
773773
Check constraints:
774774
"check_a" CHECK (length(a) > 0)
775775

@@ -868,3 +868,15 @@ Partition key: LIST (a)
868868
Number of partitions: 0
869869

870870
DROP TABLE parted_col_comment;
871+
-- list partitioning on array type column
872+
CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
873+
CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
874+
\d+ arrlp12
875+
Table "public.arrlp12"
876+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
877+
--------+-----------+-----------+----------+---------+----------+--------------+-------------
878+
a | integer[] | | | | extended | |
879+
Partition of: arrlp FOR VALUES IN ('{1}', '{2}')
880+
Partition constraint: ((a IS NOT NULL) AND (((a)::anyarray OPERATOR(pg_catalog.=) '{1}'::integer[]) OR ((a)::anyarray OPERATOR(pg_catalog.=) '{2}'::integer[])))
881+
882+
DROP TABLE arrlp;

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1863,7 +1863,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
18631863
c2 | text | | | | | extended | |
18641864
c3 | date | | | | | plain | |
18651865
Partition of: pt2 FOR VALUES IN (1)
1866-
Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))
1866+
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
18671867
Server: s0
18681868
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
18691869

@@ -1935,7 +1935,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
19351935
c2 | text | | | | | extended | |
19361936
c3 | date | | | | | plain | |
19371937
Partition of: pt2 FOR VALUES IN (1)
1938-
Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))
1938+
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
19391939
Server: s0
19401940
FDW options: (delimiter ',', quote '"', "be quoted" 'value')
19411941

@@ -1963,7 +1963,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
19631963
c2 | text | | | | | extended | |
19641964
c3 | date | | not null | | | plain | |
19651965
Partition of: pt2 FOR VALUES IN (1)
1966-
Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))
1966+
Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
19671967
Check constraints:
19681968
"p21chk" CHECK (c2 <> ''::text)
19691969
Server: s0

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

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1014,23 +1014,19 @@ explain (costs off) select * from boolpart where a = false;
10141014
Append
10151015
-> Seq Scan on boolpart_f
10161016
Filter: (NOT a)
1017-
-> Seq Scan on boolpart_t
1018-
Filter: (NOT a)
10191017
-> Seq Scan on boolpart_default
10201018
Filter: (NOT a)
1021-
(7 rows)
1019+
(5 rows)
10221020

10231021
explain (costs off) select * from boolpart where not a = false;
10241022
QUERY PLAN
10251023
------------------------------------
10261024
Append
1027-
-> Seq Scan on boolpart_f
1028-
Filter: a
10291025
-> Seq Scan on boolpart_t
10301026
Filter: a
10311027
-> Seq Scan on boolpart_default
10321028
Filter: a
1033-
(7 rows)
1029+
(5 rows)
10341030

10351031
explain (costs off) select * from boolpart where a is true or a is not true;
10361032
QUERY PLAN

‎src/test/regress/sql/create_table.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -711,3 +711,9 @@ COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
711711
SELECT obj_description('parted_col_comment'::regclass);
712712
\d+ parted_col_comment
713713
DROP TABLE parted_col_comment;
714+
715+
-- list partitioning on array type column
716+
CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
717+
CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
718+
\d+ arrlp12
719+
DROP TABLE arrlp;

0 commit comments

Comments
 (0)