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

Commit4a2994f

Browse files
committed
Fix wrong order of operations in inheritance_planner.
When considering a partitioning parent rel, we should stop processing thatsubroot as soon as we've done adjust_appendrel_attrs and any securityQualsupdates. The rest of this is unnecessary, and indeed adding duplicatesubquery RTEs to the subroot is *wrong*. As the code stood, the childrenof that partition ended up with two sets of copied subquery RTEs, confusingmatters greatly. Even more hilarity ensued if all of the children gotexcluded by constraint exclusion, so that the extra RTEs didn't make itback into the parent rtable.Per fuzz testing by Andreas Seltenreich. Back-patch to v11 where thisgot broken (by commit0a48050, it looks like).Discussion:https://postgr.es/m/87va8g7vq0.fsf@ansel.ydns.eu
1 parenta2a8acd commit4a2994f

File tree

3 files changed

+111
-50
lines changed

3 files changed

+111
-50
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 56 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -1322,6 +1322,59 @@ inheritance_planner(PlannerInfo *root)
13221322
child_rte->securityQuals=parent_rte->securityQuals;
13231323
parent_rte->securityQuals=NIL;
13241324

1325+
/*
1326+
* Mark whether we're planning a query to a partitioned table or an
1327+
* inheritance parent.
1328+
*/
1329+
subroot->inhTargetKind=
1330+
partitioned_relids ?INHKIND_PARTITIONED :INHKIND_INHERITED;
1331+
1332+
/*
1333+
* If this child is further partitioned, remember it as a parent.
1334+
* Since a partitioned table does not have any data, we don't need to
1335+
* create a plan for it, and we can stop processing it here. We do,
1336+
* however, need to remember its modified PlannerInfo for use when
1337+
* processing its children, since we'll update their varnos based on
1338+
* the delta from immediate parent to child, not from top to child.
1339+
*
1340+
* Note: a very non-obvious point is that we have not yet added
1341+
* duplicate subquery RTEs to the subroot's rtable. We mustn't,
1342+
* because then its children would have two sets of duplicates,
1343+
* confusing matters.
1344+
*/
1345+
if (child_rte->inh)
1346+
{
1347+
Assert(child_rte->relkind==RELKIND_PARTITIONED_TABLE);
1348+
parent_relids=bms_add_member(parent_relids,appinfo->child_relid);
1349+
parent_roots[appinfo->child_relid]=subroot;
1350+
1351+
continue;
1352+
}
1353+
1354+
/*
1355+
* Set the nominal target relation of the ModifyTable node if not
1356+
* already done. We use the inheritance parent RTE as the nominal
1357+
* target relation if it's a partitioned table (see just above this
1358+
* loop). In the non-partitioned parent case, we'll use the first
1359+
* child relation (even if it's excluded) as the nominal target
1360+
* relation. Because of the way expand_inherited_rtentry works, the
1361+
* latter should be the RTE representing the parent table in its role
1362+
* as a simple member of the inheritance set.
1363+
*
1364+
* It would be logically cleaner to *always* use the inheritance
1365+
* parent RTE as the nominal relation; but that RTE is not otherwise
1366+
* referenced in the plan in the non-partitioned inheritance case.
1367+
* Instead the duplicate child RTE created by expand_inherited_rtentry
1368+
* is used elsewhere in the plan, so using the original parent RTE
1369+
* would give rise to confusing use of multiple aliases in EXPLAIN
1370+
* output for what the user will think is the "same" table. OTOH,
1371+
* it's not a problem in the partitioned inheritance case, because the
1372+
* duplicate child RTE added for the parent does not appear anywhere
1373+
* else in the plan tree.
1374+
*/
1375+
if (nominalRelation<0)
1376+
nominalRelation=appinfo->child_relid;
1377+
13251378
/*
13261379
* The rowMarks list might contain references to subquery RTEs, so
13271380
* make a copy that we can apply ChangeVarNodes to. (Fortunately, the
@@ -1425,56 +1478,9 @@ inheritance_planner(PlannerInfo *root)
14251478
/* and we haven't created PlaceHolderInfos, either */
14261479
Assert(subroot->placeholder_list==NIL);
14271480

1428-
/*
1429-
* Mark if we're planning a query to a partitioned table or an
1430-
* inheritance parent.
1431-
*/
1432-
subroot->inhTargetKind=
1433-
partitioned_relids ?INHKIND_PARTITIONED :INHKIND_INHERITED;
1434-
1435-
/*
1436-
* If the child is further partitioned, remember it as a parent. Since
1437-
* a partitioned table does not have any data, we don't need to create
1438-
* a plan for it. We do, however, need to remember the PlannerInfo for
1439-
* use when processing its children.
1440-
*/
1441-
if (child_rte->inh)
1442-
{
1443-
Assert(child_rte->relkind==RELKIND_PARTITIONED_TABLE);
1444-
parent_relids=
1445-
bms_add_member(parent_relids,appinfo->child_relid);
1446-
parent_roots[appinfo->child_relid]=subroot;
1447-
1448-
continue;
1449-
}
1450-
14511481
/* Generate Path(s) for accessing this result relation */
14521482
grouping_planner(subroot, true,0.0/* retrieve all tuples */ );
14531483

1454-
/*
1455-
* Set the nomimal target relation of the ModifyTable node if not
1456-
* already done. We use the inheritance parent RTE as the nominal
1457-
* target relation if it's a partitioned table (see just above this
1458-
* loop). In the non-partitioned parent case, we'll use the first
1459-
* child relation (even if it's excluded) as the nominal target
1460-
* relation. Because of the way expand_inherited_rtentry works, the
1461-
* latter should be the RTE representing the parent table in its role
1462-
* as a simple member of the inheritance set.
1463-
*
1464-
* It would be logically cleaner to *always* use the inheritance
1465-
* parent RTE as the nominal relation; but that RTE is not otherwise
1466-
* referenced in the plan in the non-partitioned inheritance case.
1467-
* Instead the duplicate child RTE created by expand_inherited_rtentry
1468-
* is used elsewhere in the plan, so using the original parent RTE
1469-
* would give rise to confusing use of multiple aliases in EXPLAIN
1470-
* output for what the user will think is the "same" table. OTOH,
1471-
* it's not a problem in the partitioned inheritance case, because the
1472-
* duplicate child RTE added for the parent does not appear anywhere
1473-
* else in the plan tree.
1474-
*/
1475-
if (nominalRelation<0)
1476-
nominalRelation=appinfo->child_relid;
1477-
14781484
/*
14791485
* Select cheapest path in case there's more than one. We always run
14801486
* modification queries to conclusion, so we care only for the
@@ -1492,9 +1498,9 @@ inheritance_planner(PlannerInfo *root)
14921498
continue;
14931499

14941500
/*
1495-
* Add the current parent's RT index to thepartitione_rels set if
1496-
* we'regoing to createthe ModifyTable path for a partitioned root
1497-
*table.
1501+
* Add the current parent's RT index to thepartitioned_relids set if
1502+
* we'recreatingthe ModifyTable path for a partitioned root table.
1503+
*(We only care about parents of non-excluded children.)
14981504
*/
14991505
if (partitioned_relids)
15001506
partitioned_relids=bms_add_member(partitioned_relids,

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

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1722,6 +1722,49 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2)
17221722
One-Time Filter: false
17231723
(11 rows)
17241724

1725+
-- Test case to verify proper handling of subqueries in a partitioned delete.
1726+
-- The weird-looking lateral join is just there to force creation of a
1727+
-- nestloop parameter within the subquery, which exposes the problem if the
1728+
-- planner fails to make multiple copies of the subquery as appropriate.
1729+
EXPLAIN (COSTS OFF)
1730+
DELETE FROM prt1_l
1731+
WHERE EXISTS (
1732+
SELECT 1
1733+
FROM int4_tbl,
1734+
LATERAL (SELECT int4_tbl.f1 FROM int8_tbl LIMIT 2) ss
1735+
WHERE prt1_l.c IS NULL);
1736+
QUERY PLAN
1737+
---------------------------------------------------------------
1738+
Delete on prt1_l
1739+
Delete on prt1_l_p1
1740+
Delete on prt1_l_p3_p1
1741+
Delete on prt1_l_p3_p2
1742+
-> Nested Loop Semi Join
1743+
-> Seq Scan on prt1_l_p1
1744+
Filter: (c IS NULL)
1745+
-> Nested Loop
1746+
-> Seq Scan on int4_tbl
1747+
-> Subquery Scan on ss
1748+
-> Limit
1749+
-> Seq Scan on int8_tbl
1750+
-> Nested Loop Semi Join
1751+
-> Seq Scan on prt1_l_p3_p1
1752+
Filter: (c IS NULL)
1753+
-> Nested Loop
1754+
-> Seq Scan on int4_tbl
1755+
-> Subquery Scan on ss_1
1756+
-> Limit
1757+
-> Seq Scan on int8_tbl int8_tbl_1
1758+
-> Nested Loop Semi Join
1759+
-> Seq Scan on prt1_l_p3_p2
1760+
Filter: (c IS NULL)
1761+
-> Nested Loop
1762+
-> Seq Scan on int4_tbl
1763+
-> Subquery Scan on ss_2
1764+
-> Limit
1765+
-> Seq Scan on int8_tbl int8_tbl_2
1766+
(28 rows)
1767+
17251768
--
17261769
-- negative testcases
17271770
--

‎src/test/regress/sql/partition_join.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -340,6 +340,18 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
340340
EXPLAIN (COSTS OFF)
341341
SELECTt1.a,t1.c,t2.b,t2.cFROM (SELECT*FROM prt1_lWHERE a=1AND a=2) t1RIGHT JOIN prt2_l t2ONt1.a=t2.bANDt1.b=t2.aANDt1.c=t2.c;
342342

343+
-- Test case to verify proper handling of subqueries in a partitioned delete.
344+
-- The weird-looking lateral join is just there to force creation of a
345+
-- nestloop parameter within the subquery, which exposes the problem if the
346+
-- planner fails to make multiple copies of the subquery as appropriate.
347+
EXPLAIN (COSTS OFF)
348+
DELETEFROM prt1_l
349+
WHERE EXISTS (
350+
SELECT1
351+
FROM int4_tbl,
352+
LATERAL (SELECTint4_tbl.f1FROM int8_tblLIMIT2) ss
353+
WHEREprt1_l.c ISNULL);
354+
343355
--
344356
-- negative testcases
345357
--

0 commit comments

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