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

Commitbb0203f

Browse files
committed
Fix bug around assignment expressions containing indirections.
Handling of assigned-to expressions with indirection (e.g. set f1[1] =3) was broken for ON CONFLICT DO UPDATE. The problem was thatParseState was consulted to determine if an INSERT-appropriate orUPDATE-appropriate behavior should be used when transforming expressionswith indirections. When the wrong path was taken the old row wassubstituted with NULL, leading to wrong results..To fix remove p_is_update and only use p_is_insert to decide how totransform the assignment expression, and uset p_is_insert while parsingthe on conflict statement. This isn't particularly pretty, but it's notany worse than before.Author: Peter Geoghegan, slightly edited by meDiscussion: CAM3SWZS8RPvA=KFxADZWw3wAHnnbxMxDzkEC6fNaFc7zSm411w@mail.gmail.comBackpatch: 9.5, where the feature was introduced
1 parentfbf8dc2 commitbb0203f

File tree

4 files changed

+41
-2
lines changed

4 files changed

+41
-2
lines changed

‎src/backend/parser/analyze.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -891,6 +891,12 @@ transformOnConflictClause(ParseState *pstate,
891891
/* Process DO UPDATE */
892892
if (onConflictClause->action==ONCONFLICT_UPDATE)
893893
{
894+
/*
895+
* All INSERT expressions have been parsed, get ready for potentially
896+
* existing SET statements that need to be processed like an UPDATE.
897+
*/
898+
pstate->p_is_insert= false;
899+
894900
exclRte=addRangeTableEntryForRelation(pstate,
895901
pstate->p_target_relation,
896902
makeAlias("excluded",NIL),
@@ -1999,7 +2005,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
19992005
Node*qual;
20002006

20012007
qry->commandType=CMD_UPDATE;
2002-
pstate->p_is_update=true;
2008+
pstate->p_is_insert=false;
20032009

20042010
/* process the WITH clause independently of all else */
20052011
if (stmt->withClause)

‎src/include/parser/parse_node.h

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -152,7 +152,6 @@ struct ParseState
152152
boolp_hasSubLinks;
153153
boolp_hasModifyingCTE;
154154
boolp_is_insert;
155-
boolp_is_update;
156155
boolp_locked_from_parent;
157156
Relationp_target_relation;
158157
RangeTblEntry*p_target_rangetblentry;

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1116,6 +1116,27 @@ select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}';
11161116
{1,2,10}
11171117
(2 rows)
11181118

1119+
-- test ON CONFLICT DO UPDATE with arrays
1120+
create temp table arr_pk_tbl (pk int4 primary key, f1 int[]);
1121+
insert into arr_pk_tbl values (1, '{1,2,3}');
1122+
insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk)
1123+
do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3]
1124+
returning pk, f1;
1125+
pk | f1
1126+
----+---------
1127+
1 | {3,2,5}
1128+
(1 row)
1129+
1130+
insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk)
1131+
do update set f1[1] = excluded.f1[1],
1132+
f1[2] = excluded.f1[2],
1133+
f1[3] = excluded.f1[3]
1134+
returning pk, f1;
1135+
pk | f1
1136+
----+------------
1137+
1 | {6,7,NULL}
1138+
(1 row)
1139+
11191140
-- note: if above selects don't produce the expected tuple order,
11201141
-- then you didn't get an indexscan plan, and something is busted.
11211142
reset enable_seqscan;

‎src/test/regress/sql/arrays.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -306,6 +306,19 @@ set enable_seqscan to off;
306306
set enable_bitmapscan to off;
307307
select*from arr_tblwhere f1>'{1,2,3}'and f1<='{1,5,3}';
308308
select*from arr_tblwhere f1>='{1,2,3}'and f1<'{1,5,3}';
309+
310+
-- test ON CONFLICT DO UPDATE with arrays
311+
create temp table arr_pk_tbl (pk int4primary key, f1int[]);
312+
insert into arr_pk_tblvalues (1,'{1,2,3}');
313+
insert into arr_pk_tblvalues (1,'{3,4,5}')on conflict (pk)
314+
doupdateset f1[1]=excluded.f1[1], f1[3]=excluded.f1[3]
315+
returning pk, f1;
316+
insert into arr_pk_tbl(pk, f1[1:2])values (1,'{6,7,8}')on conflict (pk)
317+
doupdateset f1[1]=excluded.f1[1],
318+
f1[2]=excluded.f1[2],
319+
f1[3]=excluded.f1[3]
320+
returning pk, f1;
321+
309322
-- note: if above selects don't produce the expected tuple order,
310323
-- then you didn't get an indexscan plan, and something is busted.
311324
reset enable_seqscan;

0 commit comments

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