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

Commitfe9e658

Browse files
committed
Ensure that MERGE recomputes GENERATED expressions properly.
This fixes a bug that, under some circumstances, would cause MERGE tofail to properly recompute expressions for GENERATED STORED columns.Formerly, ExecInitModifyTable() did not call ExecInitStoredGenerated()for a MERGE command, which meant that the generated expressionsinformation was not computed until later, when the first merge actionwas executed. However, if the first merge action to execute was anUPDATE, then ExecInitStoredGenerated() could decide to skip some somegenerated columns, if the columns on which they depended were notupdated, which was a problem if the MERGE also contained an INSERTaction, for which no generated columns should be skipped.So fix by having ExecInitModifyTable() call ExecInitStoredGenerated()for MERGE, and assume that it isn't safe to skip any generated columnsin a MERGE. Possibly that could be relaxed, by allowing some generatedcolumns to be skipped for a MERGE without an INSERT action, but it'snot clear that it's worth the effort.Noticed while investigating bug #17759. Back-patch to v15, where MERGEwas added.Dean Rasheed, reviewed by Tom Lane.Discussion:https://postgr.es/m/17759-e76d9bece1b5421c%40postgresql.orghttps://postgr.es/m/CAEZATCXb_ezoMCcL0tzKwRGA1x0oeE%3DawTaysRfTPq%2B3wNJn8g%40mail.gmail.com
1 parent1e8b617 commitfe9e658

File tree

3 files changed

+37
-2
lines changed

3 files changed

+37
-2
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4141,12 +4141,12 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
41414141
}
41424142

41434143
/*
4144-
* For INSERT andUPDATE, prepare to evaluate any generated columns.
4144+
* For INSERT/UPDATE/MERGE, prepare to evaluate any generated columns.
41454145
* We must do this now, even if we never insert or update any rows,
41464146
* because we have to fill resultRelInfo->ri_extraUpdatedCols for
41474147
* possible use by the trigger machinery.
41484148
*/
4149-
if (operation==CMD_INSERT||operation==CMD_UPDATE)
4149+
if (operation==CMD_INSERT||operation==CMD_UPDATE||operation==CMD_MERGE)
41504150
ExecInitStoredGenerated(resultRelInfo,estate,operation);
41514151
}
41524152

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -192,6 +192,26 @@ SELECT * FROM gtest1 ORDER BY a;
192192
3 | 6
193193
(1 row)
194194

195+
-- test MERGE
196+
CREATE TABLE gtestm (
197+
id int PRIMARY KEY,
198+
f1 int,
199+
f2 int,
200+
f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
201+
f4 int GENERATED ALWAYS AS (f2 * 2) STORED
202+
);
203+
INSERT INTO gtestm VALUES (1, 5, 100);
204+
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
205+
WHEN MATCHED THEN UPDATE SET f1 = v.f1
206+
WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
207+
SELECT * FROM gtestm ORDER BY id;
208+
id | f1 | f2 | f3 | f4
209+
----+----+-----+----+-----
210+
1 | 10 | 100 | 20 | 200
211+
2 | 20 | 200 | 40 | 400
212+
(2 rows)
213+
214+
DROP TABLE gtestm;
195215
-- views
196216
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
197217
SELECT * FROM gtest1v;

‎src/test/regress/sql/generated.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -81,6 +81,21 @@ SELECT * FROM gtest1 ORDER BY a;
8181
DELETEFROM gtest1WHERE b=2;
8282
SELECT*FROM gtest1ORDER BY a;
8383

84+
-- test MERGE
85+
CREATETABLEgtestm (
86+
idintPRIMARY KEY,
87+
f1int,
88+
f2int,
89+
f3int GENERATED ALWAYSAS (f1*2) STORED,
90+
f4int GENERATED ALWAYSAS (f2*2) STORED
91+
);
92+
INSERT INTO gtestmVALUES (1,5,100);
93+
MERGE INTO gtestm t USING (VALUES (1,10), (2,20)) v(id, f1)ONt.id=v.id
94+
WHEN MATCHED THENUPDATESET f1=v.f1
95+
WHEN NOT MATCHED THEN INSERTVALUES (v.id,v.f1,200);
96+
SELECT*FROM gtestmORDER BY id;
97+
DROPTABLE gtestm;
98+
8499
-- views
85100
CREATEVIEWgtest1vASSELECT*FROM gtest1;
86101
SELECT*FROM gtest1v;

0 commit comments

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