Skip to content

Commit 7c644b7

Browse files
alvherreAmit Langote
alvherre
and
Amit Langote
committed
Better handle pseudotypes as partition keys
We fail to handle polymorphic types properly when they are used as partition keys: we were unnecessarily adding a RelabelType node on top, which confuses code examining the nodes. In particular, this makes predtest.c-based partition pruning not to work, and ruleutils.c to emit expressions that are uglier than needed. Fix it by not adding RelabelType when not needed. In master/11 the new pruning code is separate so it doesn't suffer from this problem, since we already fixed it (in essentially the same way) in e5dcbb8, which also added a few tests; back-patch those tests to pg10 also. But since UPDATE/DELETE still uses predtest.c in pg11, this change improves partitioning for those cases too. Add tests for this. The ruleutils.c behavior change is relevant in pg11/master too. Co-authored-by: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Discussion: https://postgr.es/m/54745d13-7ed4-54ac-97d8-ea1eec95ae25@lab.ntt.co.jp
1 parent 7f0911f commit 7c644b7

File tree

4 files changed

+180
-30
lines changed

4 files changed

+180
-30
lines changed

‎src/backend/catalog/partition.c

Lines changed: 18 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1167,7 +1167,10 @@ RelationGetPartitionDispatchInfo(Relation rel,
11671167
* get_partition_operator
11681168
*
11691169
* Return oid of the operator of given strategy for a given partition key
1170-
* column.
1170+
* column. It is assumed that the partitioning key is of the same type as the
1171+
* chosen partitioning opclass, or at least binary-compatible. In the latter
1172+
* case, *need_relabel is set to true if the opclass is not of a polymorphic
1173+
* type, otherwise false.
11711174
*/
11721175
static Oid
11731176
get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
@@ -1176,40 +1179,26 @@ get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
11761179
Oid operoid;
11771180

11781181
/*
1179-
* First check if there exists an operator of the given strategy, with
1180-
* this column's type as both its lefttype and righttype, in the
1181-
* partitioning operator family specified for the column.
1182+
* Get the operator in the partitioning opfamily using the opclass'
1183+
* declared input type as both left- and righttype.
11821184
*/
11831185
operoid = get_opfamily_member(key->partopfamily[col],
1184-
key->parttypid[col],
1185-
key->parttypid[col],
1186+
key->partopcintype[col],
1187+
key->partopcintype[col],
11861188
strategy);
1189+
if (!OidIsValid(operoid))
1190+
elog(ERROR, "missing operator %d(%u,%u) in partition opfamily %u",
1191+
strategy, key->partopcintype[col], key->partopcintype[col],
1192+
key->partopfamily[col]);
11871193

11881194
/*
1189-
* If one doesn't exist, we must resort to using an operator in the same
1190-
* operator family but with the operator class declared input type. It is
1191-
* OK to do so, because the column's type is known to be binary-coercible
1192-
* with the operator class input type (otherwise, the operator class in
1193-
* question would not have been accepted as the partitioning operator
1194-
* class). We must however inform the caller to wrap the non-Const
1195-
* expression with a RelabelType node to denote the implicit coercion. It
1196-
* ensures that the resulting expression structurally matches similarly
1197-
* processed expressions within the optimizer.
1195+
* If the partition key column is not of the same type as the operator
1196+
* class and not polymorphic, tell caller to wrap the non-Const expression
1197+
* in a RelabelType. This matches what parse_coerce.c does.
11981198
*/
1199-
if (!OidIsValid(operoid))
1200-
{
1201-
operoid = get_opfamily_member(key->partopfamily[col],
1202-
key->partopcintype[col],
1203-
key->partopcintype[col],
1204-
strategy);
1205-
if (!OidIsValid(operoid))
1206-
elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
1207-
strategy, key->partopcintype[col], key->partopcintype[col],
1208-
key->partopfamily[col]);
1209-
*need_relabel = true;
1210-
}
1211-
else
1212-
*need_relabel = false;
1199+
*need_relabel = (key->parttypid[col] != key->partopcintype[col] &&
1200+
key->partopcintype[col] != RECORDOID &&
1201+
!IsPolymorphicType(key->partopcintype[col]));
12131202

12141203
return operoid;
12151204
}

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -808,7 +808,7 @@ CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
808808
--------+-----------+-----------+----------+---------+----------+--------------+-------------
809809
a | integer[] | | | | extended | |
810810
Partition of: arrlp FOR VALUES IN ('{1}', '{2}')
811-
Partition constraint: ((a IS NOT NULL) AND (((a)::anyarray OPERATOR(pg_catalog.=) '{1}'::integer[]) OR ((a)::anyarray OPERATOR(pg_catalog.=) '{2}'::integer[])))
811+
Partition constraint: ((a IS NOT NULL) AND ((a = '{1}'::integer[]) OR (a = '{2}'::integer[])))
812812

813813
DROP TABLE arrlp;
814814
-- partition on boolean column

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

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1973,3 +1973,119 @@ select min(a), max(a) from parted_minmax where b = '12345';
19731973
(1 row)
19741974

19751975
drop table parted_minmax;
1976+
--
1977+
-- check that pruning works properly when the partition key is of a
1978+
-- pseudotype
1979+
--
1980+
-- array type list partition key
1981+
create table pp_arrpart (a int[]) partition by list (a);
1982+
create table pp_arrpart1 partition of pp_arrpart for values in ('{1}');
1983+
create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}');
1984+
explain (costs off) select * from pp_arrpart where a = '{1}';
1985+
QUERY PLAN
1986+
----------------------------------------
1987+
Append
1988+
-> Seq Scan on pp_arrpart1
1989+
Filter: (a = '{1}'::integer[])
1990+
(3 rows)
1991+
1992+
explain (costs off) select * from pp_arrpart where a = '{1, 2}';
1993+
QUERY PLAN
1994+
--------------------------
1995+
Result
1996+
One-Time Filter: false
1997+
(2 rows)
1998+
1999+
explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
2000+
QUERY PLAN
2001+
----------------------------------------------------------------------
2002+
Append
2003+
-> Seq Scan on pp_arrpart1
2004+
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
2005+
-> Seq Scan on pp_arrpart2
2006+
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
2007+
(5 rows)
2008+
2009+
explain (costs off) update pp_arrpart set a = a where a = '{1}';
2010+
QUERY PLAN
2011+
----------------------------------------
2012+
Update on pp_arrpart
2013+
Update on pp_arrpart1
2014+
-> Seq Scan on pp_arrpart1
2015+
Filter: (a = '{1}'::integer[])
2016+
(4 rows)
2017+
2018+
explain (costs off) delete from pp_arrpart where a = '{1}';
2019+
QUERY PLAN
2020+
----------------------------------------
2021+
Delete on pp_arrpart
2022+
Delete on pp_arrpart1
2023+
-> Seq Scan on pp_arrpart1
2024+
Filter: (a = '{1}'::integer[])
2025+
(4 rows)
2026+
2027+
drop table pp_arrpart;
2028+
-- enum type list partition key
2029+
create type pp_colors as enum ('green', 'blue', 'black');
2030+
create table pp_enumpart (a pp_colors) partition by list (a);
2031+
create table pp_enumpart_green partition of pp_enumpart for values in ('green');
2032+
create table pp_enumpart_blue partition of pp_enumpart for values in ('blue');
2033+
explain (costs off) select * from pp_enumpart where a = 'blue';
2034+
QUERY PLAN
2035+
-----------------------------------------
2036+
Append
2037+
-> Seq Scan on pp_enumpart_blue
2038+
Filter: (a = 'blue'::pp_colors)
2039+
(3 rows)
2040+
2041+
explain (costs off) select * from pp_enumpart where a = 'black';
2042+
QUERY PLAN
2043+
--------------------------
2044+
Result
2045+
One-Time Filter: false
2046+
(2 rows)
2047+
2048+
drop table pp_enumpart;
2049+
drop type pp_colors;
2050+
-- record type as partition key
2051+
create type pp_rectype as (a int, b int);
2052+
create table pp_recpart (a pp_rectype) partition by list (a);
2053+
create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)');
2054+
create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)');
2055+
explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
2056+
QUERY PLAN
2057+
-------------------------------------------
2058+
Append
2059+
-> Seq Scan on pp_recpart_11
2060+
Filter: (a = '(1,1)'::pp_rectype)
2061+
(3 rows)
2062+
2063+
explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
2064+
QUERY PLAN
2065+
--------------------------
2066+
Result
2067+
One-Time Filter: false
2068+
(2 rows)
2069+
2070+
drop table pp_recpart;
2071+
drop type pp_rectype;
2072+
-- range type partition key
2073+
create table pp_intrangepart (a int4range) partition by list (a);
2074+
create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]');
2075+
create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)');
2076+
explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
2077+
QUERY PLAN
2078+
------------------------------------------
2079+
Append
2080+
-> Seq Scan on pp_intrangepart12
2081+
Filter: (a = '[1,3)'::int4range)
2082+
(3 rows)
2083+
2084+
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
2085+
QUERY PLAN
2086+
--------------------------
2087+
Result
2088+
One-Time Filter: false
2089+
(2 rows)
2090+
2091+
drop table pp_intrangepart;

‎src/test/regress/sql/inherit.sql

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -683,3 +683,48 @@ insert into parted_minmax values (1,'12345');
683683
explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
684684
select min(a), max(a) from parted_minmax where b = '12345';
685685
drop table parted_minmax;
686+
687+
688+
--
689+
-- check that pruning works properly when the partition key is of a
690+
-- pseudotype
691+
--
692+
693+
-- array type list partition key
694+
create table pp_arrpart (a int[]) partition by list (a);
695+
create table pp_arrpart1 partition of pp_arrpart for values in ('{1}');
696+
create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}');
697+
explain (costs off) select * from pp_arrpart where a = '{1}';
698+
explain (costs off) select * from pp_arrpart where a = '{1, 2}';
699+
explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
700+
explain (costs off) update pp_arrpart set a = a where a = '{1}';
701+
explain (costs off) delete from pp_arrpart where a = '{1}';
702+
drop table pp_arrpart;
703+
704+
-- enum type list partition key
705+
create type pp_colors as enum ('green', 'blue', 'black');
706+
create table pp_enumpart (a pp_colors) partition by list (a);
707+
create table pp_enumpart_green partition of pp_enumpart for values in ('green');
708+
create table pp_enumpart_blue partition of pp_enumpart for values in ('blue');
709+
explain (costs off) select * from pp_enumpart where a = 'blue';
710+
explain (costs off) select * from pp_enumpart where a = 'black';
711+
drop table pp_enumpart;
712+
drop type pp_colors;
713+
714+
-- record type as partition key
715+
create type pp_rectype as (a int, b int);
716+
create table pp_recpart (a pp_rectype) partition by list (a);
717+
create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)');
718+
create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)');
719+
explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
720+
explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
721+
drop table pp_recpart;
722+
drop type pp_rectype;
723+
724+
-- range type partition key
725+
create table pp_intrangepart (a int4range) partition by list (a);
726+
create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]');
727+
create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)');
728+
explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
729+
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
730+
drop table pp_intrangepart;

0 commit comments

Comments
 (0)