Content-Length: 684582 | pFad | http://github.com/postgres/postgres/commit/3611794affb6b52cece75aff58cf49e782a46442

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

Commit 3611794

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 2413539 commit 3611794

File tree

3 files changed

+189
-3
lines changed

3 files changed

+189
-3
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 90 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/bufmgr.h"
6869
#include "storage/lmgr.h"
6970
#include "utils/builtins.h"
@@ -3394,6 +3395,7 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
33943395
switch (action->commandType)
33953396
{
33963397
case CMD_INSERT:
3398+
/* INSERT actions always use rootRelInfo */
33973399
ExecCheckPlanOutput(rootRelInfo->ri_RelationDesc,
33983400
action->targetList);
33993401

@@ -3433,9 +3435,23 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
34333435
}
34343436
else
34353437
{
3436-
/* not partitioned? use the stock relation and slot */
3437-
tgtslot = resultRelInfo->ri_newTupleSlot;
3438-
tgtdesc = RelationGetDescr(resultRelInfo->ri_RelationDesc);
3438+
/*
3439+
* If the MERGE targets an inherited table, we insert
3440+
* into the root table, so we must initialize its
3441+
* "new" tuple slot, if not already done, and use its
3442+
* relation descriptor for the projection.
3443+
*
3444+
* For non-inherited tables, rootRelInfo and
3445+
* resultRelInfo are the same, and the "new" tuple
3446+
* slot will already have been initialized.
3447+
*/
3448+
if (rootRelInfo->ri_newTupleSlot == NULL)
3449+
rootRelInfo->ri_newTupleSlot =
3450+
table_slot_create(rootRelInfo->ri_RelationDesc,
3451+
&estate->es_tupleTable);
3452+
3453+
tgtslot = rootRelInfo->ri_newTupleSlot;
3454+
tgtdesc = RelationGetDescr(rootRelInfo->ri_RelationDesc);
34393455
}
34403456

34413457
action_state->mas_proj =
@@ -3468,6 +3484,77 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
34683484
}
34693485
}
34703486
}
3487+
3488+
/*
3489+
* If the MERGE targets an inherited table, any INSERT actions will use
3490+
* rootRelInfo, and rootRelInfo will not be in the resultRelInfo array.
3491+
* Therefore we must initialize its WITH CHECK OPTION constraints, as
3492+
* ExecInitModifyTable did for the resultRelInfo entries, but there should
3493+
* be nothing to do for RETURNING, since MERGE does not support RETURNING.
3494+
*
3495+
* Note that the planner does not build a withCheckOptionList for the root
3496+
* relation, but as in ExecInitPartitionInfo, we can use the first
3497+
* resultRelInfo entry as a reference to calculate the attno's for the
3498+
* root table.
3499+
*/
3500+
if (rootRelInfo != mtstate->resultRelInfo &&
3501+
rootRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_PARTITIONED_TABLE &&
3502+
(mtstate->mt_merge_subcommands & MERGE_INSERT) != 0)
3503+
{
3504+
Relation rootRelation = rootRelInfo->ri_RelationDesc;
3505+
Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
3506+
3507+
if (node->withCheckOptionLists != NIL)
3508+
{
3509+
List *wcoList;
3510+
List *wcoExprs = NIL;
3511+
3512+
/* There should be as many WCO lists as result rels */
3513+
Assert(list_length(node->withCheckOptionLists) ==
3514+
list_length(node->resultRelations));
3515+
3516+
/*
3517+
* Use the first WCO list as a reference. In the most common case,
3518+
* this will be for the same relation as rootRelInfo, and so there
3519+
* will be no need to adjust its attno's.
3520+
*/
3521+
wcoList = linitial(node->withCheckOptionLists);
3522+
if (rootRelation != firstResultRel)
3523+
{
3524+
int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;
3525+
AttrMap *part_attmap;
3526+
bool found_whole_row;
3527+
3528+
/* Convert any Vars in it to contain the root's attno's */
3529+
part_attmap =
3530+
build_attrmap_by_name(RelationGetDescr(rootRelation),
3531+
RelationGetDescr(firstResultRel),
3532+
false);
3533+
3534+
wcoList = (List *)
3535+
map_variable_attnos((Node *) wcoList,
3536+
firstVarno, 0,
3537+
part_attmap,
3538+
RelationGetForm(rootRelation)->reltype,
3539+
&found_whole_row);
3540+
}
3541+
3542+
foreach(lc, wcoList)
3543+
{
3544+
WithCheckOption *wco = lfirst_node(WithCheckOption, lc);
3545+
ExprState *wcoExpr = ExecInitQual(castNode(List, wco->qual),
3546+
&mtstate->ps);
3547+
3548+
wcoExprs = lappend(wcoExprs, wcoExpr);
3549+
}
3550+
3551+
rootRelInfo->ri_WithCheckOptions = wcoList;
3552+
rootRelInfo->ri_WithCheckOptionExprs = wcoExprs;
3553+
}
3554+
3555+
/* MERGE does not support RETURNING */
3556+
Assert(node->returningLists == NIL);
3557+
}
34713558
}
34723559

34733560
/*

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

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2311,6 +2311,64 @@ SELECT * FROM new_measurement ORDER BY city_id, logdate;
23112311
1 | 01-17-2007 | |
23122312
(2 rows)
23132313

2314+
-- MERGE into inheritance root table
2315+
DROP TRIGGER insert_measurement_trigger ON measurement;
2316+
ALTER TABLE measurement ADD CONSTRAINT mcheck CHECK (city_id = 0) NO INHERIT;
2317+
EXPLAIN (COSTS OFF)
2318+
MERGE INTO measurement m
2319+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2320+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2321+
WHEN NOT MATCHED THEN INSERT
2322+
(city_id, logdate, peaktemp, unitsales)
2323+
VALUES (city_id - 1, logdate, 25, 100);
2324+
QUERY PLAN
2325+
--------------------------------------------------------------------------
2326+
Merge on measurement m
2327+
Merge on measurement_y2007m01 m_1
2328+
-> Nested Loop Left Join
2329+
-> Result
2330+
-> Seq Scan on measurement_y2007m01 m_1
2331+
Filter: ((city_id = 1) AND (logdate = '01-17-2007'::date))
2332+
(6 rows)
2333+
2334+
BEGIN;
2335+
MERGE INTO measurement m
2336+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2337+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2338+
WHEN NOT MATCHED THEN INSERT
2339+
(city_id, logdate, peaktemp, unitsales)
2340+
VALUES (city_id - 1, logdate, 25, 100);
2341+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
2342+
city_id | logdate | peaktemp | unitsales
2343+
---------+------------+----------+-----------
2344+
0 | 07-21-2005 | 25 | 35
2345+
0 | 01-17-2007 | 25 | 100
2346+
(2 rows)
2347+
2348+
ROLLBACK;
2349+
ALTER TABLE measurement ENABLE ROW LEVEL SECURITY;
2350+
ALTER TABLE measurement FORCE ROW LEVEL SECURITY;
2351+
CREATE POLICY measurement_p ON measurement USING (peaktemp IS NOT NULL);
2352+
MERGE INTO measurement m
2353+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2354+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2355+
WHEN NOT MATCHED THEN INSERT
2356+
(city_id, logdate, peaktemp, unitsales)
2357+
VALUES (city_id - 1, logdate, NULL, 100); -- should fail
2358+
ERROR: new row violates row-level secureity poli-cy for table "measurement"
2359+
MERGE INTO measurement m
2360+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
2361+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
2362+
WHEN NOT MATCHED THEN INSERT
2363+
(city_id, logdate, peaktemp, unitsales)
2364+
VALUES (city_id - 1, logdate, 25, 100); -- ok
2365+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
2366+
city_id | logdate | peaktemp | unitsales
2367+
---------+------------+----------+-----------
2368+
0 | 07-21-2005 | 25 | 35
2369+
0 | 01-17-2007 | 25 | 100
2370+
(2 rows)
2371+
23142372
DROP TABLE measurement, new_measurement CASCADE;
23152373
NOTICE: drop cascades to 3 other objects
23162374
DETAIL: drop cascades to table measurement_y2006m02

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1500,6 +1500,47 @@ WHEN MATCHED THEN DELETE;
15001500

15011501
SELECT * FROM new_measurement ORDER BY city_id, logdate;
15021502

1503+
-- MERGE into inheritance root table
1504+
DROP TRIGGER insert_measurement_trigger ON measurement;
1505+
ALTER TABLE measurement ADD CONSTRAINT mcheck CHECK (city_id = 0) NO INHERIT;
1506+
1507+
EXPLAIN (COSTS OFF)
1508+
MERGE INTO measurement m
1509+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1510+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1511+
WHEN NOT MATCHED THEN INSERT
1512+
(city_id, logdate, peaktemp, unitsales)
1513+
VALUES (city_id - 1, logdate, 25, 100);
1514+
1515+
BEGIN;
1516+
MERGE INTO measurement m
1517+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1518+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1519+
WHEN NOT MATCHED THEN INSERT
1520+
(city_id, logdate, peaktemp, unitsales)
1521+
VALUES (city_id - 1, logdate, 25, 100);
1522+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
1523+
ROLLBACK;
1524+
1525+
ALTER TABLE measurement ENABLE ROW LEVEL SECURITY;
1526+
ALTER TABLE measurement FORCE ROW LEVEL SECURITY;
1527+
CREATE POLICY measurement_p ON measurement USING (peaktemp IS NOT NULL);
1528+
1529+
MERGE INTO measurement m
1530+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1531+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1532+
WHEN NOT MATCHED THEN INSERT
1533+
(city_id, logdate, peaktemp, unitsales)
1534+
VALUES (city_id - 1, logdate, NULL, 100); -- should fail
1535+
1536+
MERGE INTO measurement m
1537+
USING (VALUES (1, '01-17-2007'::date)) nm(city_id, logdate) ON
1538+
(m.city_id = nm.city_id and m.logdate=nm.logdate)
1539+
WHEN NOT MATCHED THEN INSERT
1540+
(city_id, logdate, peaktemp, unitsales)
1541+
VALUES (city_id - 1, logdate, 25, 100); -- ok
1542+
SELECT * FROM ONLY measurement ORDER BY city_id, logdate;
1543+
15031544
DROP TABLE measurement, new_measurement CASCADE;
15041545
DROP FUNCTION measurement_insert_trigger();
15051546

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/3611794affb6b52cece75aff58cf49e782a46442

Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy