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

Commit159cff5

Browse files
committed
Check the relevant index element in ON CONFLICT unique index inference.
ON CONFLICT unique index inference had a thinko that could affect caseswhere the user-supplied inference clause required that an attributematch a particular (user specified) collation and/or opclass.infer_collation_opclass_match() has to check for opclass and/orcollation matches and that the attribute is in the list of attributes orexpressions known to be in the definition of the index underconsideration. The bug was that these two conditions weren't necessarilyevaluated for the same index attribute.Author: Peter GeogheganDiscussion: CAM3SWZR4uug=WvmGk7UgsqHn2MkEzy9YU-+8jKGO4JPhesyeWg@mail.gmail.comBackpatch: 9.5, where ON CONFLICT was introduced
1 parentfaab14e commit159cff5

File tree

3 files changed

+60
-16
lines changed

3 files changed

+60
-16
lines changed

‎src/backend/optimizer/util/plancat.c

Lines changed: 30 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@ get_relation_info_hook_type get_relation_info_hook = NULL;
5252

5353

5454
staticboolinfer_collation_opclass_match(InferenceElem*elem,RelationidxRel,
55-
Bitmapset*inferAttrs,List*idxExprs);
55+
List*idxExprs);
5656
staticint32get_rel_data_width(Relationrel,int32*attr_widths);
5757
staticList*get_relation_constraints(PlannerInfo*root,
5858
OidrelationObjectId,RelOptInfo*rel,
@@ -616,8 +616,7 @@ infer_arbiter_indexes(PlannerInfo *root)
616616
* this for both expressions and ordinary (non-expression)
617617
* attributes appearing as inference elements.
618618
*/
619-
if (!infer_collation_opclass_match(elem,idxRel,inferAttrs,
620-
idxExprs))
619+
if (!infer_collation_opclass_match(elem,idxRel,idxExprs))
621620
gotonext;
622621

623622
/*
@@ -682,11 +681,10 @@ infer_arbiter_indexes(PlannerInfo *root)
682681
* infer_collation_opclass_match - ensure infer element opclass/collation match
683682
*
684683
* Given unique index inference element from inference specification, if
685-
* collation was specified, or if opclass (represented here as opfamily +
686-
* opcintype) was specified, verify that there is at least one matching
687-
* indexed attribute (occasionally, there may be more). Skip this in the
688-
* common case where inference specification does not include collation or
689-
* opclass (instead matching everything, regardless of cataloged
684+
* collation was specified, or if opclass was specified, verify that there is
685+
* at least one matching indexed attribute (occasionally, there may be more).
686+
* Skip this in the common case where inference specification does not include
687+
* collation or opclass (instead matching everything, regardless of cataloged
690688
* collation/opclass of indexed attribute).
691689
*
692690
* At least historically, Postgres has not offered collations or opclasses
@@ -708,11 +706,12 @@ infer_arbiter_indexes(PlannerInfo *root)
708706
*/
709707
staticbool
710708
infer_collation_opclass_match(InferenceElem*elem,RelationidxRel,
711-
Bitmapset*inferAttrs,List*idxExprs)
709+
List*idxExprs)
712710
{
713711
AttrNumbernatt;
714-
Oidinferopfamily=InvalidOid;/* OID of att opfamily */
715-
Oidinferopcinputtype=InvalidOid;/* OID of att opfamily */
712+
Oidinferopfamily=InvalidOid;/* OID of opclass opfamily */
713+
Oidinferopcinputtype=InvalidOid;/* OID of opclass input type */
714+
intnplain=0;/* # plain attrs observed */
716715

717716
/*
718717
* If inference specification element lacks collation/opclass, then no
@@ -735,6 +734,10 @@ infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
735734
Oidopfamily=idxRel->rd_opfamily[natt-1];
736735
Oidopcinputtype=idxRel->rd_opcintype[natt-1];
737736
Oidcollation=idxRel->rd_indcollation[natt-1];
737+
intattno=idxRel->rd_index->indkey.values[natt-1];
738+
739+
if (attno!=0)
740+
nplain++;
738741

739742
if (elem->inferopclass!=InvalidOid&&
740743
(inferopfamily!=opfamily||inferopcinputtype!=opcinputtype))
@@ -750,12 +753,23 @@ infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
750753
continue;
751754
}
752755

753-
if ((IsA(elem->expr,Var)&&
754-
bms_is_member(((Var*)elem->expr)->varattno,inferAttrs))||
755-
list_member(idxExprs,elem->expr))
756+
/* If one matching index att found, good enough -- return true */
757+
if (IsA(elem->expr,Var))
756758
{
757-
/* Found one match - good enough */
758-
return true;
759+
if (((Var*)elem->expr)->varattno==attno)
760+
return true;
761+
}
762+
elseif (attno==0)
763+
{
764+
Node*nattExpr=list_nth(idxExprs, (natt-1)-nplain);
765+
766+
/*
767+
* Note that unlike routines like match_index_to_operand() we
768+
* don't need to care about RelabelType. Neither the index
769+
* definition nor the inference clause should contain them.
770+
*/
771+
if (equal(elem->expr,nattExpr))
772+
return true;
759773
}
760774
}
761775

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -141,6 +141,24 @@ drop index collation_index_key;
141141
drop index both_index_key;
142142
drop index both_index_expr_key;
143143
--
144+
-- Make sure that cross matching of attribute opclass/collation does not occur
145+
--
146+
create unique index cross_match on insertconflicttest(lower(fruit) collate "C", upper(fruit) text_pattern_ops);
147+
-- fails:
148+
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) text_pattern_ops, upper(fruit) collate "C") do nothing;
149+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
150+
-- works:
151+
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", upper(fruit) text_pattern_ops) do nothing;
152+
QUERY PLAN
153+
-----------------------------------------
154+
Insert on insertconflicttest
155+
Conflict Resolution: NOTHING
156+
Conflict Arbiter Indexes: cross_match
157+
-> Result
158+
(4 rows)
159+
160+
drop index cross_match;
161+
--
144162
-- Single key tests
145163
--
146164
create unique index key_index on insertconflicttest(key);

‎src/test/regress/sql/insert_conflict.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,18 @@ drop index collation_index_key;
5757
dropindex both_index_key;
5858
dropindex both_index_expr_key;
5959

60+
--
61+
-- Make sure that cross matching of attribute opclass/collation does not occur
62+
--
63+
createunique indexcross_matchon insertconflicttest(lower(fruit) collate"C",upper(fruit) text_pattern_ops);
64+
65+
-- fails:
66+
explain (costs off)insert into insertconflicttestvalues(0,'Crowberry')on conflict (lower(fruit) text_pattern_ops,upper(fruit) collate"C") do nothing;
67+
-- works:
68+
explain (costs off)insert into insertconflicttestvalues(0,'Crowberry')on conflict (lower(fruit) collate"C",upper(fruit) text_pattern_ops) do nothing;
69+
70+
dropindex cross_match;
71+
6072
--
6173
-- Single key tests
6274
--

0 commit comments

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