Skip to content

Commit b006bcd

Browse files
deanrasheeddeanrasheed
committed
Fix MERGE into a plain inheritance parent table.
When a MERGE's target table is the parent of an inheritance tree, any INSERT actions insert into the parent table using ModifyTableState's rootResultRelInfo. However, there are two bugs in the way is initialized: 1. ExecInitMerge() incorrectly uses a different ResultRelInfo entry from ModifyTableState's resultRelInfo array to build the insert projection, which may not be compatible with rootResultRelInfo. 2. ExecInitModifyTable() does not fully initialize rootResultRelInfo. Specifically, ri_WithCheckOptions, ri_WithCheckOptionExprs, ri_returningList, and ri_projectReturning are not initialized. This can lead to crashes, or incorrect query results due to failing to check WCO's or process the RETURNING list for INSERT actions. Fix both these bugs in ExecInitMerge(), noting that it is only necessary to fully initialize rootResultRelInfo if the MERGE has INSERT actions and the target table is a plain inheritance parent. Backpatch to v15, where MERGE was introduced. Reported-by: Andres Freund <andres@anarazel.de> Author: Dean Rasheed <dean.a.rasheed@gmail.com> Reviewed-by: Jian He <jian.universality@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/4rlmjfniiyffp6b3kv4pfy4jw3pciy6mq72rdgnedsnbsx7qe5@j5hlpiwdguvc Backpatch-through: 15
1 parent e050af2 commit b006bcd

File tree

3 files changed

+246
-3
lines changed

3 files changed

+246
-3
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 127 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,7 @@
6464
#include "nodes/nodeFuncs.h"
6565
#include "optimizer/optimizer.h"
6666
#include "rewrite/rewriteHandler.h"
67+
#include "rewrite/rewriteManip.h"
6768
#include "storage/lmgr.h"
6869
#include "utils/builtins.h"
6970
#include "utils/datum.h"
@@ -3735,6 +3736,7 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
37353736
switch (action->commandType)
37363737
{
37373738
case CMD_INSERT:
3739+
/* INSERT actions always use rootRelInfo */
37383740
ExecCheckPlanOutput(rootRelInfo->ri_RelationDesc,
37393741
action->targetList);
37403742

@@ -3774,9 +3776,23 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
37743776
}
37753777
else
37763778
{
3777-
/* not partitioned? use the stock relation and slot */
3778-
tgtslot = resultRelInfo->ri_newTupleSlot;
3779-
tgtdesc = RelationGetDescr(resultRelInfo->ri_RelationDesc);
3779+
/*
3780+
* If the MERGE targets an inherited table, we insert
3781+
* into the root table, so we must initialize its
3782+
* "new" tuple slot, if not already done, and use its
3783+
* relation descriptor for the projection.
3784+
*
3785+
* For non-inherited tables, rootRelInfo and
3786+
* resultRelInfo are the same, and the "new" tuple
3787+
* slot will already have been initialized.
3788+
*/
3789+
if (rootRelInfo->ri_newTupleSlot == NULL)
3790+
rootRelInfo->ri_newTupleSlot =
3791+
table_slot_create(rootRelInfo->ri_RelationDesc,
3792+
&estate->es_tupleTable);
3793+
3794+
tgtslot = rootRelInfo->ri_newTupleSlot;
3795+
tgtdesc = RelationGetDescr(rootRelInfo->ri_RelationDesc);
37803796
}
37813797

37823798
action_state->mas_proj =
@@ -3809,6 +3825,114 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
38093825
}
38103826
}
38113827
}
3828+
3829+
/*
3830+
* If the MERGE targets an inherited table, any INSERT actions will use
3831+
* rootRelInfo, and rootRelInfo will not be in the resultRelInfo array.
3832+
* Therefore we must initialize its WITH CHECK OPTION constraints and
3833+
* RETURNING projection, as ExecInitModifyTable did for the resultRelInfo
3834+
* entries.
3835+
*
3836+
* Note that the planner does not build a withCheckOptionList or
3837+
* returningList for the root relation, but as in ExecInitPartitionInfo,
3838+
* we can use the first resultRelInfo entry as a reference to calculate
3839+
* the attno's for the root table.
3840+
*/
3841+
if (rootRelInfo != mtstate->resultRelInfo &&
3842+
rootRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_PARTITIONED_TABLE &&
3843+
(mtstate->mt_merge_subcommands & MERGE_INSERT) != 0)
3844+
{
3845+
ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
3846+
Relation rootRelation = rootRelInfo->ri_RelationDesc;
3847+
Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
3848+
int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;
3849+
AttrMap *part_attmap = NULL;
3850+
bool found_whole_row;
3851+
3852+
if (node->withCheckOptionLists != NIL)
3853+
{
3854+
List *wcoList;
3855+
List *wcoExprs = NIL;
3856+
3857+
/* There should be as many WCO lists as result rels */
3858+
Assert(list_length(node->withCheckOptionLists) ==
3859+
list_length(node->resultRelations));
3860+
3861+
/*
3862+
* Use the first WCO list as a reference. In the most common case,
3863+
* this will be for the same relation as rootRelInfo, and so there
3864+
* will be no need to adjust its attno's.
3865+
*/
3866+
wcoList = linitial(node->withCheckOptionLists);
3867+
if (rootRelation != firstResultRel)
3868+
{
3869+
/* Convert any Vars in it to contain the root's attno's */
3870+
part_attmap =
3871+
build_attrmap_by_name(RelationGetDescr(rootRelation),
3872+
RelationGetDescr(firstResultRel),
3873+
false);
3874+
3875+
wcoList = (List *)
3876+
map_variable_attnos((Node *) wcoList,
3877+
firstVarno, 0,
3878+
part_attmap,
3879+
RelationGetForm(rootRelation)->reltype,
3880+
&found_whole_row);
3881+
}
3882+
3883+
foreach(lc, wcoList)
3884+
{
3885+
WithCheckOption *wco = lfirst_node(WithCheckOption, lc);
3886+
ExprState *wcoExpr = ExecInitQual(castNode(List, wco->qual),
3887+
&mtstate->ps);
3888+
3889+
wcoExprs = lappend(wcoExprs, wcoExpr);
3890+
}
3891+
3892+
rootRelInfo->ri_WithCheckOptions = wcoList;
3893+
rootRelInfo->ri_WithCheckOptionExprs = wcoExprs;
3894+
}
3895+
3896+
if (node->returningLists != NIL)
3897+
{
3898+
List *returningList;
3899+
3900+
/* There should be as many returning lists as result rels */
3901+
Assert(list_length(node->returningLists) ==
3902+
list_length(node->resultRelations));
3903+
3904+
/*
3905+
* Use the first returning list as a reference. In the most common
3906+
* case, this will be for the same relation as rootRelInfo, and so
3907+
* there will be no need to adjust its attno's.
3908+
*/
3909+
returningList = linitial(node->returningLists);
3910+
if (rootRelation != firstResultRel)
3911+
{
3912+
/* Convert any Vars in it to contain the root's attno's */
3913+
if (part_attmap == NULL)
3914+
part_attmap =
3915+
build_attrmap_by_name(RelationGetDescr(rootRelation),
3916+
RelationGetDescr(firstResultRel),
3917+
false);
3918+
3919+
returningList = (List *)
3920+
map_variable_attnos((Node *) returningList,
3921+
firstVarno, 0,
3922+
part_attmap,
3923+
RelationGetForm(rootRelation)->reltype,
3924+
&found_whole_row);
3925+
}
3926+
rootRelInfo->ri_returningList = returningList;
3927+
3928+
/* Initialize the RETURNING projection */
3929+
rootRelInfo->ri_projectReturning =
3930+
ExecBuildProjectionInfo(returningList, econtext,
3931+
mtstate->ps.ps_ResultTupleSlot,
3932+
&mtstate->ps,
3933+
RelationGetDescr(rootRelation));
3934+
}
3935+
}
38123936
}
38133937

38143938
/*

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

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2702,6 +2702,76 @@ SELECT * FROM new_measurement ORDER BY city_id, logdate;
27022702
1 | 01-17-2007 | |
27032703
(2 rows)
27042704

2705+
-- MERGE into inheritance root table
2706+
DROP TRIGGER insert_measurement_trigger ON measurement;
2707+
ALTER TABLE measurement ADD CONSTRAINT mcheck CHECK (city_id = 0) NO INHERIT;
2708+
EXPLAIN (COSTS OFF)
2709+
MERGE INTO measurement m
2710+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2711+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2712+
WHEN NOT MATCHED THEN INSERT
2713+
(city_id, logdate, peaktemp, unitsales)
2714+
VALUES (city_id - 1, logdate, 25, 100);
2715+
QUERY PLAN
2716+
--------------------------------------------------------------------------
2717+
Merge on measurement m
2718+
Merge on measurement_y2007m01 m_1
2719+
-> Nested Loop Left Join
2720+
-> Result
2721+
-> Seq Scan on measurement_y2007m01 m_1
2722+
Filter: ((city_id = 1) AND (logdate = '01-17-2007'::date))
2723+
(6 rows)
2724+
2725+
BEGIN;
2726+
MERGE INTO measurement m
2727+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2728+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2729+
WHEN NOT MATCHED THEN INSERT
2730+
(city_id, logdate, peaktemp, unitsales)
2731+
VALUES (city_id - 1, logdate, 25, 100);
2732+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
2733+
city_id | logdate | peaktemp | unitsales
2734+
---------+------------+----------+-----------
2735+
0 | 07-21-2005 | 25 | 35
2736+
0 | 01-17-2007 | 25 | 100
2737+
(2 rows)
2738+
2739+
ROLLBACK;
2740+
ALTER TABLE measurement ENABLE ROW LEVEL SECURITY;
2741+
ALTER TABLE measurement FORCE ROW LEVEL SECURITY;
2742+
CREATE POLICY measurement_p ON measurement USING (peaktemp IS NOT NULL);
2743+
MERGE INTO measurement m
2744+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2745+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2746+
WHEN NOT MATCHED THEN INSERT
2747+
(city_id, logdate, peaktemp, unitsales)
2748+
VALUES (city_id - 1, logdate, NULL, 100); -- should fail
2749+
ERROR: new row violates row-level security policy for table "measurement"
2750+
MERGE INTO measurement m
2751+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2752+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2753+
WHEN NOT MATCHED THEN INSERT
2754+
(city_id, logdate, peaktemp, unitsales)
2755+
VALUES (city_id - 1, logdate, 25, 100); -- ok
2756+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
2757+
city_id | logdate | peaktemp | unitsales
2758+
---------+------------+----------+-----------
2759+
0 | 07-21-2005 | 25 | 35
2760+
0 | 01-17-2007 | 25 | 100
2761+
(2 rows)
2762+
2763+
MERGE INTO measurement m
2764+
USING (VALUES (1, '01-18-2007'::date)) nm(city_id, logdate) ON
2765+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2766+
WHEN NOT MATCHED THEN INSERT
2767+
(city_id, logdate, peaktemp, unitsales)
2768+
VALUES (city_id - 1, logdate, 25, 200)
2769+
RETURNING merge_action(), m.*;
2770+
merge_action | city_id | logdate | peaktemp | unitsales
2771+
--------------+---------+------------+----------+-----------
2772+
INSERT | 0 | 01-18-2007 | 25 | 200
2773+
(1 row)
2774+
27052775
DROP TABLE measurement, new_measurement CASCADE;
27062776
NOTICE: drop cascades to 3 other objects
27072777
DETAIL: drop cascades to table measurement_y2006m02

‎src/test/regress/sql/merge.sql

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1722,6 +1722,55 @@ WHEN MATCHED THEN DELETE;
17221722

17231723
SELECT * FROM new_measurement ORDER BY city_id, logdate;
17241724

1725+
-- MERGE into inheritance root table
1726+
DROP TRIGGER insert_measurement_trigger ON measurement;
1727+
ALTER TABLE measurement ADD CONSTRAINT mcheck CHECK (city_id = 0) NO INHERIT;
1728+
1729+
EXPLAIN (COSTS OFF)
1730+
MERGE INTO measurement m
1731+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1732+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1733+
WHEN NOT MATCHED THEN INSERT
1734+
(city_id, logdate, peaktemp, unitsales)
1735+
VALUES (city_id - 1, logdate, 25, 100);
1736+
1737+
BEGIN;
1738+
MERGE INTO measurement m
1739+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1740+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1741+
WHEN NOT MATCHED THEN INSERT
1742+
(city_id, logdate, peaktemp, unitsales)
1743+
VALUES (city_id - 1, logdate, 25, 100);
1744+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
1745+
ROLLBACK;
1746+
1747+
ALTER TABLE measurement ENABLE ROW LEVEL SECURITY;
1748+
ALTER TABLE measurement FORCE ROW LEVEL SECURITY;
1749+
CREATE POLICY measurement_p ON measurement USING (peaktemp IS NOT NULL);
1750+
1751+
MERGE INTO measurement m
1752+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1753+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1754+
WHEN NOT MATCHED THEN INSERT
1755+
(city_id, logdate, peaktemp, unitsales)
1756+
VALUES (city_id - 1, logdate, NULL, 100); -- should fail
1757+
1758+
MERGE INTO measurement m
1759+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1760+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1761+
WHEN NOT MATCHED THEN INSERT
1762+
(city_id, logdate, peaktemp, unitsales)
1763+
VALUES (city_id - 1, logdate, 25, 100); -- ok
1764+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
1765+
1766+
MERGE INTO measurement m
1767+
USING (VALUES (1, '01-18-2007'::date)) nm(city_id, logdate) ON
1768+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1769+
WHEN NOT MATCHED THEN INSERT
1770+
(city_id, logdate, peaktemp, unitsales)
1771+
VALUES (city_id - 1, logdate, 25, 200)
1772+
RETURNING merge_action(), m.*;
1773+
17251774
DROP TABLE measurement, new_measurement CASCADE;
17261775
DROP FUNCTION measurement_insert_trigger();
17271776

0 commit comments

Comments
 (0)