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

Commit6c0373a

Browse files
committed
Allow table-qualified variable names in ON CONFLICT ... WHERE.
Previously you could only use unqualified variable names here.While that's not a functional deficiency, since only the targettable can be referenced, it's a surprising inconsistency with therules for partial-index predicates, on which this syntax issupposedly modeled.The fix for that is no harder than passing addToRelNameSpace = trueto addNSItemToQuery. However, it's really pretty bogus fortransformOnConflictArbiter and transformOnConflictClause to bemessing with the namespace item for the target table at all.It's not theirs to manage, it results in duplicative creations ofnamespace items, and transformOnConflictClause wasn't even doingit quite correctly (that coding resulted in two nsitems for thetarget table, since it hadn't cleaned out the existing one).Hence, make transformInsertStmt responsible for setting up thetarget nsitem once for both these clauses and RETURNING.Also, arrange for ON CONFLICT ... UPDATE's "excluded" pseudo-relationto be added to the rangetable before we run transformOnConflictArbiter.This produces a more helpful HINT if someone writes "excluded.col"in the arbiter expression.Per bug #16958 from Lukas Eder. Although I agree this is a bug,the consequences are hardly severe, so no back-patch.Discussion:https://postgr.es/m/16958-963f638020de271c@postgresql.org
1 parente8c435a commit6c0373a

File tree

4 files changed

+52
-48
lines changed

4 files changed

+52
-48
lines changed

‎src/backend/parser/analyze.c

Lines changed: 49 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -869,25 +869,27 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
869869
attr_num-FirstLowInvalidHeapAttributeNumber);
870870
}
871871

872-
/* Process ON CONFLICT, if any. */
873-
if (stmt->onConflictClause)
874-
qry->onConflict=transformOnConflictClause(pstate,
875-
stmt->onConflictClause);
876-
877872
/*
878-
* If we have a RETURNING clause, we need to add the target relation to
879-
* the query namespace before processing it, so that Var references in
880-
* RETURNING will work. Also, remove any namespace entries added in a
873+
* If we have any clauses yet to process, set the query namespace to
874+
* contain only the target relation, removing any entries added in a
881875
* sub-SELECT or VALUES list.
882876
*/
883-
if (stmt->returningList)
877+
if (stmt->onConflictClause||stmt->returningList)
884878
{
885879
pstate->p_namespace=NIL;
886880
addNSItemToQuery(pstate,pstate->p_target_nsitem,
887881
false, true, true);
882+
}
883+
884+
/* Process ON CONFLICT, if any. */
885+
if (stmt->onConflictClause)
886+
qry->onConflict=transformOnConflictClause(pstate,
887+
stmt->onConflictClause);
888+
889+
/* Process RETURNING, if any. */
890+
if (stmt->returningList)
888891
qry->returningList=transformReturningList(pstate,
889892
stmt->returningList);
890-
}
891893

892894
/* done building the range table and jointree */
893895
qry->rtable=pstate->p_rtable;
@@ -1014,6 +1016,7 @@ static OnConflictExpr *
10141016
transformOnConflictClause(ParseState*pstate,
10151017
OnConflictClause*onConflictClause)
10161018
{
1019+
ParseNamespaceItem*exclNSItem=NULL;
10171020
List*arbiterElems;
10181021
Node*arbiterWhere;
10191022
OidarbiterConstraint;
@@ -1023,29 +1026,17 @@ transformOnConflictClause(ParseState *pstate,
10231026
List*exclRelTlist=NIL;
10241027
OnConflictExpr*result;
10251028

1026-
/* Process the arbiter clause, ON CONFLICT ON (...) */
1027-
transformOnConflictArbiter(pstate,onConflictClause,&arbiterElems,
1028-
&arbiterWhere,&arbiterConstraint);
1029-
1030-
/* Process DO UPDATE */
1029+
/*
1030+
* If this is ON CONFLICT ... UPDATE, first create the range table entry
1031+
* for the EXCLUDED pseudo relation, so that that will be present while
1032+
* processing arbiter expressions. (You can't actually reference it from
1033+
* there, but this provides a useful error message if you try.)
1034+
*/
10311035
if (onConflictClause->action==ONCONFLICT_UPDATE)
10321036
{
10331037
Relationtargetrel=pstate->p_target_relation;
1034-
ParseNamespaceItem*exclNSItem;
10351038
RangeTblEntry*exclRte;
10361039

1037-
/*
1038-
* All INSERT expressions have been parsed, get ready for potentially
1039-
* existing SET statements that need to be processed like an UPDATE.
1040-
*/
1041-
pstate->p_is_insert= false;
1042-
1043-
/*
1044-
* Add range table entry for the EXCLUDED pseudo relation. relkind is
1045-
* set to composite to signal that we're not dealing with an actual
1046-
* relation, and no permission checks are required on it. (We'll
1047-
* check the actual target relation, instead.)
1048-
*/
10491040
exclNSItem=addRangeTableEntryForRelation(pstate,
10501041
targetrel,
10511042
RowExclusiveLock,
@@ -1054,21 +1045,39 @@ transformOnConflictClause(ParseState *pstate,
10541045
exclRte=exclNSItem->p_rte;
10551046
exclRelIndex=exclNSItem->p_rtindex;
10561047

1048+
/*
1049+
* relkind is set to composite to signal that we're not dealing with
1050+
* an actual relation, and no permission checks are required on it.
1051+
* (We'll check the actual target relation, instead.)
1052+
*/
10571053
exclRte->relkind=RELKIND_COMPOSITE_TYPE;
10581054
exclRte->requiredPerms=0;
10591055
/* other permissions fields in exclRte are already empty */
10601056

10611057
/* Create EXCLUDED rel's targetlist for use by EXPLAIN */
10621058
exclRelTlist=BuildOnConflictExcludedTargetlist(targetrel,
10631059
exclRelIndex);
1060+
}
1061+
1062+
/* Process the arbiter clause, ON CONFLICT ON (...) */
1063+
transformOnConflictArbiter(pstate,onConflictClause,&arbiterElems,
1064+
&arbiterWhere,&arbiterConstraint);
1065+
1066+
/* Process DO UPDATE */
1067+
if (onConflictClause->action==ONCONFLICT_UPDATE)
1068+
{
1069+
/*
1070+
* Expressions in the UPDATE targetlist need to be handled like UPDATE
1071+
* not INSERT. We don't need to save/restore this because all INSERT
1072+
* expressions have been parsed already.
1073+
*/
1074+
pstate->p_is_insert= false;
10641075

10651076
/*
1066-
* AddEXCLUDED andthetarget RTEto the namespace,so that they can
1067-
*be used in the UPDATE subexpressions.
1077+
* Add theEXCLUDED pseudo relationto thequerynamespace,making it
1078+
*available in the UPDATE subexpressions.
10681079
*/
10691080
addNSItemToQuery(pstate,exclNSItem, false, true, true);
1070-
addNSItemToQuery(pstate,pstate->p_target_nsitem,
1071-
false, true, true);
10721081

10731082
/*
10741083
* Now transform the UPDATE subexpressions.
@@ -1079,6 +1088,14 @@ transformOnConflictClause(ParseState *pstate,
10791088
onConflictWhere=transformWhereClause(pstate,
10801089
onConflictClause->whereClause,
10811090
EXPR_KIND_WHERE,"WHERE");
1091+
1092+
/*
1093+
* Remove the EXCLUDED pseudo relation from the query namespace, since
1094+
* it's not supposed to be available in RETURNING. (Maybe someday we
1095+
* could allow that, and drop this step.)
1096+
*/
1097+
Assert((ParseNamespaceItem*)llast(pstate->p_namespace)==exclNSItem);
1098+
pstate->p_namespace=list_delete_last(pstate->p_namespace);
10821099
}
10831100

10841101
/* Finally, build ON CONFLICT DO [NOTHING | UPDATE] expression */

‎src/backend/parser/parse_clause.c

Lines changed: 0 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -3222,17 +3222,6 @@ transformOnConflictArbiter(ParseState *pstate,
32223222
/* ON CONFLICT DO NOTHING does not require an inference clause */
32233223
if (infer)
32243224
{
3225-
List*save_namespace;
3226-
3227-
/*
3228-
* While we process the arbiter expressions, accept only non-qualified
3229-
* references to the target table. Hide any other relations.
3230-
*/
3231-
save_namespace=pstate->p_namespace;
3232-
pstate->p_namespace=NIL;
3233-
addNSItemToQuery(pstate,pstate->p_target_nsitem,
3234-
false, false, true);
3235-
32363225
if (infer->indexElems)
32373226
*arbiterExpr=resolve_unique_index_expr(pstate,infer,
32383227
pstate->p_target_relation);
@@ -3245,8 +3234,6 @@ transformOnConflictArbiter(ParseState *pstate,
32453234
*arbiterWhere=transformExpr(pstate,infer->whereClause,
32463235
EXPR_KIND_INDEX_PREDICATE);
32473236

3248-
pstate->p_namespace=save_namespace;
3249-
32503237
/*
32513238
* If the arbiter is specified by constraint name, get the constraint
32523239
* OID and mark the constrained columns as requiring SELECT privilege,

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -248,7 +248,7 @@ insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update
248248
ERROR: column "keyy" does not exist
249249
LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ...
250250
^
251-
HINT: Perhaps you meant to reference the column "insertconflicttest.key".
251+
HINT: Perhaps you meant to reference the column "insertconflicttest.key" or the column "excluded.key".
252252
-- Have useful HINT for EXCLUDED.* RTE within UPDATE:
253253
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt;
254254
ERROR: column excluded.fruitt does not exist
@@ -373,7 +373,7 @@ drop index fruit_index;
373373
create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry';
374374
-- Succeeds
375375
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit;
376-
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing;
376+
insert into insertconflicttestas tvalues (23, 'Blackberry') on conflict (key) where fruit like '%berry' andt.fruit = 'inconsequential' do nothing;
377377
-- fails
378378
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit;
379379
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -214,7 +214,7 @@ create unique index partial_key_index on insertconflicttest(key) where fruit lik
214214

215215
-- Succeeds
216216
insert into insertconflicttestvalues (23,'Blackberry')on conflict (key)where fruitlike'%berry' doupdateset fruit=excluded.fruit;
217-
insert into insertconflicttestvalues (23,'Blackberry')on conflict (key)where fruitlike'%berry'and fruit='inconsequential' do nothing;
217+
insert into insertconflicttestas tvalues (23,'Blackberry')on conflict (key)where fruitlike'%berry'andt.fruit='inconsequential' do nothing;
218218

219219
-- fails
220220
insert into insertconflicttestvalues (23,'Blackberry')on conflict (key) doupdateset fruit=excluded.fruit;

0 commit comments

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