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

Commitf6a124d

Browse files
committed
Fix INSERT ON CONFLICT UPDATE through a view that isn't just SELECT *.
When expanding an updatable view that is an INSERT's target, the rewriterfailed to rewrite Vars in the ON CONFLICT UPDATE clause. This accidentallyworked if the view was just "SELECT * FROM ...", as the transformationwould be a no-op in that case. With more complicated view targetlists,this omission would often lead to "attribute ... has the wrong type" errorsor even crashes, as reported by Mario De Frutos Dieguez.Fix by adding code to rewriteTargetView to fix up the data structurecorrectly. The easiest way to update the exclRelTlist list is to rebuildit from scratch looking at the new target relation, so factor the codefor that out of transformOnConflictClause to make it sharable.In passing, avoid duplicate permissions checks against the EXCLUDEDpseudo-relation, and prevent useless view expansion of that relation'sdummy RTE. The latter is only known to happen (after this patch) in caseswhere the query would fail later due to not having any INSTEAD OF triggersfor the view. But by exactly that token, it would create an unintendedand very poorly tested state of the query data structure, so it seems likea good idea to prevent it from happening at all.This has been broken since ON CONFLICT was introduced, so back-patchto 9.5.Dean Rasheed, based on an earlier patch by Amit Langote;comment-kibitzing and back-patching by meDiscussion:https://postgr.es/m/CAFYwGJ0xfzy8jaK80hVN2eUWr6huce0RU8AgU04MGD00igqkTg@mail.gmail.com
1 parent7124e64 commitf6a124d

File tree

5 files changed

+514
-57
lines changed

5 files changed

+514
-57
lines changed

‎src/backend/parser/analyze.c

Lines changed: 81 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -1021,9 +1021,6 @@ transformOnConflictClause(ParseState *pstate,
10211021
if (onConflictClause->action==ONCONFLICT_UPDATE)
10221022
{
10231023
Relationtargetrel=pstate->p_target_relation;
1024-
Var*var;
1025-
TargetEntry*te;
1026-
intattno;
10271024

10281025
/*
10291026
* All INSERT expressions have been parsed, get ready for potentially
@@ -1032,75 +1029,36 @@ transformOnConflictClause(ParseState *pstate,
10321029
pstate->p_is_insert= false;
10331030

10341031
/*
1035-
* Add range table entry for the EXCLUDED pseudo relation; relkind is
1032+
* Add range table entry for the EXCLUDED pseudo relation. relkind is
10361033
* set to composite to signal that we're not dealing with an actual
1037-
* relation.
1034+
* relation, and no permission checks are required on it. (We'll
1035+
* check the actual target relation, instead.)
10381036
*/
10391037
exclRte=addRangeTableEntryForRelation(pstate,
10401038
targetrel,
10411039
makeAlias("excluded",NIL),
10421040
false, false);
10431041
exclRte->relkind=RELKIND_COMPOSITE_TYPE;
1044-
exclRelIndex=list_length(pstate->p_rtable);
1045-
1046-
/*
1047-
* Build a targetlist representing the columns of the EXCLUDED pseudo
1048-
* relation. Have to be careful to use resnos that correspond to
1049-
* attnos of the underlying relation.
1050-
*/
1051-
for (attno=0;attno<targetrel->rd_rel->relnatts;attno++)
1052-
{
1053-
Form_pg_attributeattr=targetrel->rd_att->attrs[attno];
1054-
char*name;
1055-
1056-
if (attr->attisdropped)
1057-
{
1058-
/*
1059-
* can't use atttypid here, but it doesn't really matter what
1060-
* type the Const claims to be.
1061-
*/
1062-
var= (Var*)makeNullConst(INT4OID,-1,InvalidOid);
1063-
name="";
1064-
}
1065-
else
1066-
{
1067-
var=makeVar(exclRelIndex,attno+1,
1068-
attr->atttypid,attr->atttypmod,
1069-
attr->attcollation,
1070-
0);
1071-
name=pstrdup(NameStr(attr->attname));
1072-
}
1042+
exclRte->requiredPerms=0;
1043+
/* other permissions fields in exclRte are already empty */
10731044

1074-
te=makeTargetEntry((Expr*)var,
1075-
attno+1,
1076-
name,
1077-
false);
1078-
1079-
/* don't require select access yet */
1080-
exclRelTlist=lappend(exclRelTlist,te);
1081-
}
1045+
exclRelIndex=list_length(pstate->p_rtable);
10821046

1083-
/*
1084-
* Add a whole-row-Var entry to support references to "EXCLUDED.*".
1085-
* Like the other entries in exclRelTlist, its resno must match the
1086-
* Var's varattno, else the wrong things happen while resolving
1087-
* references in setrefs.c. This is against normal conventions for
1088-
* targetlists, but it's okay since we don't use this as a real tlist.
1089-
*/
1090-
var=makeVar(exclRelIndex,InvalidAttrNumber,
1091-
targetrel->rd_rel->reltype,
1092-
-1,InvalidOid,0);
1093-
te=makeTargetEntry((Expr*)var,InvalidAttrNumber,NULL, true);
1094-
exclRelTlist=lappend(exclRelTlist,te);
1047+
/* Create EXCLUDED rel's targetlist for use by EXPLAIN */
1048+
exclRelTlist=BuildOnConflictExcludedTargetlist(targetrel,
1049+
exclRelIndex);
10951050

10961051
/*
10971052
* Add EXCLUDED and the target RTE to the namespace, so that they can
1098-
* be used in the UPDATEstatement.
1053+
* be used in the UPDATEsubexpressions.
10991054
*/
11001055
addRTEtoQuery(pstate,exclRte, false, true, true);
11011056
addRTEtoQuery(pstate,pstate->p_target_rangetblentry,
11021057
false, true, true);
11031058

1059+
/*
1060+
* Now transform the UPDATE subexpressions.
1061+
*/
11041062
onConflictSet=
11051063
transformUpdateTargetList(pstate,onConflictClause->targetList);
11061064

@@ -1125,6 +1083,74 @@ transformOnConflictClause(ParseState *pstate,
11251083
}
11261084

11271085

1086+
/*
1087+
* BuildOnConflictExcludedTargetlist
1088+
*Create target list for the EXCLUDED pseudo-relation of ON CONFLICT,
1089+
*representing the columns of targetrel with varno exclRelIndex.
1090+
*
1091+
* Note: Exported for use in the rewriter.
1092+
*/
1093+
List*
1094+
BuildOnConflictExcludedTargetlist(Relationtargetrel,
1095+
IndexexclRelIndex)
1096+
{
1097+
List*result=NIL;
1098+
intattno;
1099+
Var*var;
1100+
TargetEntry*te;
1101+
1102+
/*
1103+
* Note that resnos of the tlist must correspond to attnos of the
1104+
* underlying relation, hence we need entries for dropped columns too.
1105+
*/
1106+
for (attno=0;attno<RelationGetNumberOfAttributes(targetrel);attno++)
1107+
{
1108+
Form_pg_attributeattr=TupleDescAttr(targetrel->rd_att,attno);
1109+
char*name;
1110+
1111+
if (attr->attisdropped)
1112+
{
1113+
/*
1114+
* can't use atttypid here, but it doesn't really matter what type
1115+
* the Const claims to be.
1116+
*/
1117+
var= (Var*)makeNullConst(INT4OID,-1,InvalidOid);
1118+
name="";
1119+
}
1120+
else
1121+
{
1122+
var=makeVar(exclRelIndex,attno+1,
1123+
attr->atttypid,attr->atttypmod,
1124+
attr->attcollation,
1125+
0);
1126+
name=pstrdup(NameStr(attr->attname));
1127+
}
1128+
1129+
te=makeTargetEntry((Expr*)var,
1130+
attno+1,
1131+
name,
1132+
false);
1133+
1134+
result=lappend(result,te);
1135+
}
1136+
1137+
/*
1138+
* Add a whole-row-Var entry to support references to "EXCLUDED.*". Like
1139+
* the other entries in the EXCLUDED tlist, its resno must match the Var's
1140+
* varattno, else the wrong things happen while resolving references in
1141+
* setrefs.c. This is against normal conventions for targetlists, but
1142+
* it's okay since we don't use this as a real tlist.
1143+
*/
1144+
var=makeVar(exclRelIndex,InvalidAttrNumber,
1145+
targetrel->rd_rel->reltype,
1146+
-1,InvalidOid,0);
1147+
te=makeTargetEntry((Expr*)var,InvalidAttrNumber,NULL, true);
1148+
result=lappend(result,te);
1149+
1150+
returnresult;
1151+
}
1152+
1153+
11281154
/*
11291155
* count_rowexpr_columns -
11301156
* get number of columns contained in a ROW() expression;

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 101 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include"nodes/nodeFuncs.h"
3030
#include"parser/analyze.h"
3131
#include"parser/parse_coerce.h"
32+
#include"parser/parse_relation.h"
3233
#include"parser/parsetree.h"
3334
#include"rewrite/rewriteDefine.h"
3435
#include"rewrite/rewriteHandler.h"
@@ -1764,6 +1765,17 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
17641765
if (rte->relkind==RELKIND_MATVIEW)
17651766
continue;
17661767

1768+
/*
1769+
* In INSERT ... ON CONFLICT, ignore the EXCLUDED pseudo-relation;
1770+
* even if it points to a view, we needn't expand it, and should not
1771+
* because we want the RTE to remain of RTE_RELATION type. Otherwise,
1772+
* it would get changed to RTE_SUBQUERY type, which is an
1773+
* untested/unsupported situation.
1774+
*/
1775+
if (parsetree->onConflict&&
1776+
rt_index==parsetree->onConflict->exclRelIndex)
1777+
continue;
1778+
17671779
/*
17681780
* If the table is not referenced in the query, then we ignore it.
17691781
* This prevents infinite expansion loop due to new rtable entries
@@ -2870,8 +2882,6 @@ rewriteTargetView(Query *parsetree, Relation view)
28702882
*/
28712883
base_rte->relkind=base_rel->rd_rel->relkind;
28722884

2873-
heap_close(base_rel,NoLock);
2874-
28752885
/*
28762886
* If the view query contains any sublink subqueries then we need to also
28772887
* acquire locks on any relations they refer to. We know that there won't
@@ -3029,6 +3039,93 @@ rewriteTargetView(Query *parsetree, Relation view)
30293039
}
30303040
}
30313041

3042+
/*
3043+
* For INSERT .. ON CONFLICT .. DO UPDATE, we must also update assorted
3044+
* stuff in the onConflict data structure.
3045+
*/
3046+
if (parsetree->onConflict&&
3047+
parsetree->onConflict->action==ONCONFLICT_UPDATE)
3048+
{
3049+
Indexold_exclRelIndex,
3050+
new_exclRelIndex;
3051+
RangeTblEntry*new_exclRte;
3052+
List*tmp_tlist;
3053+
3054+
/*
3055+
* Like the INSERT/UPDATE code above, update the resnos in the
3056+
* auxiliary UPDATE targetlist to refer to columns of the base
3057+
* relation.
3058+
*/
3059+
foreach(lc,parsetree->onConflict->onConflictSet)
3060+
{
3061+
TargetEntry*tle= (TargetEntry*)lfirst(lc);
3062+
TargetEntry*view_tle;
3063+
3064+
if (tle->resjunk)
3065+
continue;
3066+
3067+
view_tle=get_tle_by_resno(view_targetlist,tle->resno);
3068+
if (view_tle!=NULL&& !view_tle->resjunk&&IsA(view_tle->expr,Var))
3069+
tle->resno= ((Var*)view_tle->expr)->varattno;
3070+
else
3071+
elog(ERROR,"attribute number %d not found in view targetlist",
3072+
tle->resno);
3073+
}
3074+
3075+
/*
3076+
* Also, create a new RTE for the EXCLUDED pseudo-relation, using the
3077+
* query's new base rel (which may well have a different column list
3078+
* from the view, hence we need a new column alias list). This should
3079+
* match transformOnConflictClause. In particular, note that the
3080+
* relkind is set to composite to signal that we're not dealing with
3081+
* an actual relation, and no permissions checks are wanted.
3082+
*/
3083+
old_exclRelIndex=parsetree->onConflict->exclRelIndex;
3084+
3085+
new_exclRte=addRangeTableEntryForRelation(make_parsestate(NULL),
3086+
base_rel,
3087+
makeAlias("excluded",
3088+
NIL),
3089+
false, false);
3090+
new_exclRte->relkind=RELKIND_COMPOSITE_TYPE;
3091+
new_exclRte->requiredPerms=0;
3092+
/* other permissions fields in new_exclRte are already empty */
3093+
3094+
parsetree->rtable=lappend(parsetree->rtable,new_exclRte);
3095+
new_exclRelIndex=parsetree->onConflict->exclRelIndex=
3096+
list_length(parsetree->rtable);
3097+
3098+
/*
3099+
* Replace the targetlist for the EXCLUDED pseudo-relation with a new
3100+
* one, representing the columns from the new base relation.
3101+
*/
3102+
parsetree->onConflict->exclRelTlist=
3103+
BuildOnConflictExcludedTargetlist(base_rel,new_exclRelIndex);
3104+
3105+
/*
3106+
* Update all Vars in the ON CONFLICT clause that refer to the old
3107+
* EXCLUDED pseudo-relation. We want to use the column mappings
3108+
* defined in the view targetlist, but we need the outputs to refer to
3109+
* the new EXCLUDED pseudo-relation rather than the new target RTE.
3110+
* Also notice that "EXCLUDED.*" will be expanded using the view's
3111+
* rowtype, which seems correct.
3112+
*/
3113+
tmp_tlist=copyObject(view_targetlist);
3114+
3115+
ChangeVarNodes((Node*)tmp_tlist,new_rt_index,
3116+
new_exclRelIndex,0);
3117+
3118+
parsetree->onConflict= (OnConflictExpr*)
3119+
ReplaceVarsFromTargetList((Node*)parsetree->onConflict,
3120+
old_exclRelIndex,
3121+
0,
3122+
view_rte,
3123+
tmp_tlist,
3124+
REPLACEVARS_REPORT_ERROR,
3125+
0,
3126+
&parsetree->hasSubLinks);
3127+
}
3128+
30323129
/*
30333130
* For UPDATE/DELETE, pull up any WHERE quals from the view. We know that
30343131
* any Vars in the quals must reference the one base relation, so we need
@@ -3156,6 +3253,8 @@ rewriteTargetView(Query *parsetree, Relation view)
31563253
}
31573254
}
31583255

3256+
heap_close(base_rel,NoLock);
3257+
31593258
returnparsetree;
31603259
}
31613260

‎src/include/parser/analyze.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,4 +43,7 @@ extern void applyLockingClause(Query *qry, Index rtindex,
4343
LockClauseStrengthstrength,
4444
LockWaitPolicywaitPolicy,boolpushedDown);
4545

46+
externList*BuildOnConflictExcludedTargetlist(Relationtargetrel,
47+
IndexexclRelIndex);
48+
4649
#endif/* ANALYZE_H */

0 commit comments

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