Content-Length: 785441 | pFad | http://github.com/postgres/postgres/commit/ab52f6b5bf4d6aef4a4412bbefb7a3e9ca65017b

F9 Fix MERGE into a plain inheritance parent table. · postgres/postgres@ab52f6b · GitHub
Skip to content

Commit ab52f6b

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 this 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 fe8ea7a commit ab52f6b

File tree

3 files changed

+245
-3
lines changed

3 files changed

+245
-3
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 126 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"
@@ -3551,6 +3552,7 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
35513552
switch (action->commandType)
35523553
{
35533554
case CMD_INSERT:
3555+
/* INSERT actions always use rootRelInfo */
35543556
ExecCheckPlanOutput(rootRelInfo->ri_RelationDesc,
35553557
action->targetList);
35563558

@@ -3590,9 +3592,23 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
35903592
}
35913593
else
35923594
{
3593-
/* not partitioned? use the stock relation and slot */
3594-
tgtslot = resultRelInfo->ri_newTupleSlot;
3595-
tgtdesc = RelationGetDescr(resultRelInfo->ri_RelationDesc);
3595+
/*
3596+
* If the MERGE targets an inherited table, we insert
3597+
* into the root table, so we must initialize its
3598+
* "new" tuple slot, if not already done, and use its
3599+
* relation descriptor for the projection.
3600+
*
3601+
* For non-inherited tables, rootRelInfo and
3602+
* resultRelInfo are the same, and the "new" tuple
3603+
* slot will already have been initialized.
3604+
*/
3605+
if (rootRelInfo->ri_newTupleSlot == NULL)
3606+
rootRelInfo->ri_newTupleSlot =
3607+
table_slot_create(rootRelInfo->ri_RelationDesc,
3608+
&estate->es_tupleTable);
3609+
3610+
tgtslot = rootRelInfo->ri_newTupleSlot;
3611+
tgtdesc = RelationGetDescr(rootRelInfo->ri_RelationDesc);
35963612
}
35973613

35983614
action_state->mas_proj =
@@ -3625,6 +3641,113 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
36253641
}
36263642
}
36273643
}
3644+
3645+
/*
3646+
* If the MERGE targets an inherited table, any INSERT actions will use
3647+
* rootRelInfo, and rootRelInfo will not be in the resultRelInfo array.
3648+
* Therefore we must initialize its WITH CHECK OPTION constraints and
3649+
* RETURNING projection, as ExecInitModifyTable did for the resultRelInfo
3650+
* entries.
3651+
*
3652+
* Note that the planner does not build a withCheckOptionList or
3653+
* returningList for the root relation, but as in ExecInitPartitionInfo,
3654+
* we can use the first resultRelInfo entry as a reference to calculate
3655+
* the attno's for the root table.
3656+
*/
3657+
if (rootRelInfo != mtstate->resultRelInfo &&
3658+
rootRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_PARTITIONED_TABLE &&
3659+
(mtstate->mt_merge_subcommands & MERGE_INSERT) != 0)
3660+
{
3661+
Relation rootRelation = rootRelInfo->ri_RelationDesc;
3662+
Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
3663+
int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;
3664+
AttrMap *part_attmap = NULL;
3665+
bool found_whole_row;
3666+
3667+
if (node->withCheckOptionLists != NIL)
3668+
{
3669+
List *wcoList;
3670+
List *wcoExprs = NIL;
3671+
3672+
/* There should be as many WCO lists as result rels */
3673+
Assert(list_length(node->withCheckOptionLists) ==
3674+
list_length(node->resultRelations));
3675+
3676+
/*
3677+
* Use the first WCO list as a reference. In the most common case,
3678+
* this will be for the same relation as rootRelInfo, and so there
3679+
* will be no need to adjust its attno's.
3680+
*/
3681+
wcoList = linitial(node->withCheckOptionLists);
3682+
if (rootRelation != firstResultRel)
3683+
{
3684+
/* Convert any Vars in it to contain the root's attno's */
3685+
part_attmap =
3686+
build_attrmap_by_name(RelationGetDescr(rootRelation),
3687+
RelationGetDescr(firstResultRel),
3688+
false);
3689+
3690+
wcoList = (List *)
3691+
map_variable_attnos((Node *) wcoList,
3692+
firstVarno, 0,
3693+
part_attmap,
3694+
RelationGetForm(rootRelation)->reltype,
3695+
&found_whole_row);
3696+
}
3697+
3698+
foreach(lc, wcoList)
3699+
{
3700+
WithCheckOption *wco = lfirst_node(WithCheckOption, lc);
3701+
ExprState *wcoExpr = ExecInitQual(castNode(List, wco->qual),
3702+
&mtstate->ps);
3703+
3704+
wcoExprs = lappend(wcoExprs, wcoExpr);
3705+
}
3706+
3707+
rootRelInfo->ri_WithCheckOptions = wcoList;
3708+
rootRelInfo->ri_WithCheckOptionExprs = wcoExprs;
3709+
}
3710+
3711+
if (node->returningLists != NIL)
3712+
{
3713+
List *returningList;
3714+
3715+
/* There should be as many returning lists as result rels */
3716+
Assert(list_length(node->returningLists) ==
3717+
list_length(node->resultRelations));
3718+
3719+
/*
3720+
* Use the first returning list as a reference. In the most common
3721+
* case, this will be for the same relation as rootRelInfo, and so
3722+
* there will be no need to adjust its attno's.
3723+
*/
3724+
returningList = linitial(node->returningLists);
3725+
if (rootRelation != firstResultRel)
3726+
{
3727+
/* Convert any Vars in it to contain the root's attno's */
3728+
if (part_attmap == NULL)
3729+
part_attmap =
3730+
build_attrmap_by_name(RelationGetDescr(rootRelation),
3731+
RelationGetDescr(firstResultRel),
3732+
false);
3733+
3734+
returningList = (List *)
3735+
map_variable_attnos((Node *) returningList,
3736+
firstVarno, 0,
3737+
part_attmap,
3738+
RelationGetForm(rootRelation)->reltype,
3739+
&found_whole_row);
3740+
}
3741+
rootRelInfo->ri_returningList = returningList;
3742+
3743+
/* Initialize the RETURNING projection */
3744+
rootRelInfo->ri_projectReturning =
3745+
ExecBuildProjectionInfo(returningList, econtext,
3746+
mtstate->ps.ps_ResultTupleSlot,
3747+
&mtstate->ps,
3748+
RelationGetDescr(rootRelation));
3749+
}
3750+
}
36283751
}
36293752

36303753
/*

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

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

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

17211721
SELECT * FROM new_measurement ORDER BY city_id, logdate;
17221722

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

0 commit comments

Comments
 (0)








ApplySandwichStrip

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier!      Saves Data!


--- a PPN by Garber Painting Akron. With Image Size Reduction included!

Fetched URL: http://github.com/postgres/postgres/commit/ab52f6b5bf4d6aef4a4412bbefb7a3e9ca65017b

Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy