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

Commit7c644b7

Browse files
alvherreAmit Langote
and
Amit Langote
committed
Better handle pseudotypes as partition keys
We fail to handle polymorphic types properly when they are used aspartition keys: we were unnecessarily adding a RelabelType node on top,which confuses code examining the nodes. In particular, this makespredtest.c-based partition pruning not to work, and ruleutils.c to emitexpressions that are uglier than needed. Fix it by not adding RelabelTypewhen not needed.In master/11 the new pruning code is separate so it doesn't suffer fromthis problem, since we already fixed it (in essentially the same way) ine5dcbb8, which also added a few tests; back-patch those tests topg10 also. But since UPDATE/DELETE still uses predtest.c in pg11, thischange 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 parent7f0911f commit7c644b7

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
staticOid
11731176
get_partition_operator(PartitionKeykey,intcol,StrategyNumberstrategy,
@@ -1176,40 +1179,26 @@ get_partition_operator(PartitionKey key, int col, StrategyNumber strategy,
11761179
Oidoperoid;
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
returnoperoid;
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)selectmin(a),max(a)from parted_minmaxwhere b='12345';
684684
selectmin(a),max(a)from parted_minmaxwhere b='12345';
685685
droptable 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+
createtablepp_arrpart (aint[]) partition by list (a);
695+
createtablepp_arrpart1 partition of pp_arrpart forvaluesin ('{1}');
696+
createtablepp_arrpart2 partition of pp_arrpart forvaluesin ('{2, 3}','{4, 5}');
697+
explain (costs off)select*from pp_arrpartwhere a='{1}';
698+
explain (costs off)select*from pp_arrpartwhere a='{1, 2}';
699+
explain (costs off)select*from pp_arrpartwhere ain ('{4, 5}','{1}');
700+
explain (costs off)update pp_arrpartset a= awhere a='{1}';
701+
explain (costs off)deletefrom pp_arrpartwhere a='{1}';
702+
droptable pp_arrpart;
703+
704+
-- enum type list partition key
705+
createtypepp_colorsas enum ('green','blue','black');
706+
createtablepp_enumpart (a pp_colors) partition by list (a);
707+
createtablepp_enumpart_green partition of pp_enumpart forvaluesin ('green');
708+
createtablepp_enumpart_blue partition of pp_enumpart forvaluesin ('blue');
709+
explain (costs off)select*from pp_enumpartwhere a='blue';
710+
explain (costs off)select*from pp_enumpartwhere a='black';
711+
droptable pp_enumpart;
712+
droptype pp_colors;
713+
714+
-- record type as partition key
715+
createtypepp_rectypeas (aint, bint);
716+
createtablepp_recpart (a pp_rectype) partition by list (a);
717+
createtablepp_recpart_11 partition of pp_recpart forvaluesin ('(1,1)');
718+
createtablepp_recpart_23 partition of pp_recpart forvaluesin ('(2,3)');
719+
explain (costs off)select*from pp_recpartwhere a='(1,1)'::pp_rectype;
720+
explain (costs off)select*from pp_recpartwhere a='(1,2)'::pp_rectype;
721+
droptable pp_recpart;
722+
droptype pp_rectype;
723+
724+
-- range type partition key
725+
createtablepp_intrangepart (a int4range) partition by list (a);
726+
createtablepp_intrangepart12 partition of pp_intrangepart forvaluesin ('[1,2]');
727+
createtablepp_intrangepart2inf partition of pp_intrangepart forvaluesin ('[2,)');
728+
explain (costs off)select*from pp_intrangepartwhere a='[1,2]'::int4range;
729+
explain (costs off)select*from pp_intrangepartwhere a='(1,2)'::int4range;
730+
droptable pp_intrangepart;

0 commit comments

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