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

Commit1bc0100

Browse files
committed
postgres_fdw: Push down UPDATE/DELETE joins to remote servers.
Commit0bf3ae8 allowed directforeign table modification; instead of fetching each row, updatingit locally, and then pushing the modification back to the remoteside, we would instead do all the work on the remote server via asingle remote UPDATE or DELETE command. However, that commit onlyenabled this optimization when join tree consisted only of thetarget table.This change allows the same optimization when an UPDATE statementhas a FROM clause or a DELETE statement has a USING clause. Thisworks much like ordinary foreign join pushdown, in that the tablesmust be on the same remote server, relevant parts of the querymust be pushdown-safe, and so forth.Etsuro Fujita, reviewed by Ashutosh Bapat, Rushabh Lathia, and me.Some formatting corrections by me.Discussion:http://postgr.es/m/5A57193A.2080003@lab.ntt.co.jpDiscussion:http://postgr.es/m/b9cee735-62f8-6c07-7528-6364ce9347d0@lab.ntt.co.jp
1 parent7c44b75 commit1bc0100

File tree

5 files changed

+863
-92
lines changed

5 files changed

+863
-92
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 184 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -132,7 +132,9 @@ static void deparseTargetList(StringInfo buf,
132132
Bitmapset*attrs_used,
133133
boolqualify_col,
134134
List**retrieved_attrs);
135-
staticvoiddeparseExplicitTargetList(List*tlist,List**retrieved_attrs,
135+
staticvoiddeparseExplicitTargetList(List*tlist,
136+
boolis_returning,
137+
List**retrieved_attrs,
136138
deparse_expr_cxt*context);
137139
staticvoiddeparseSubqueryTargetList(deparse_expr_cxt*context);
138140
staticvoiddeparseReturningList(StringInfobuf,PlannerInfo*root,
@@ -168,11 +170,13 @@ static void deparseLockingClause(deparse_expr_cxt *context);
168170
staticvoidappendOrderByClause(List*pathkeys,deparse_expr_cxt*context);
169171
staticvoidappendConditions(List*exprs,deparse_expr_cxt*context);
170172
staticvoiddeparseFromExprForRel(StringInfobuf,PlannerInfo*root,
171-
RelOptInfo*joinrel,booluse_alias,List**params_list);
173+
RelOptInfo*foreignrel,booluse_alias,
174+
Indexignore_rel,List**ignore_conds,
175+
List**params_list);
172176
staticvoiddeparseFromExpr(List*quals,deparse_expr_cxt*context);
173177
staticvoiddeparseRangeTblRef(StringInfobuf,PlannerInfo*root,
174178
RelOptInfo*foreignrel,boolmake_subquery,
175-
List**params_list);
179+
Indexignore_rel,List**ignore_conds,List**params_list);
176180
staticvoiddeparseAggref(Aggref*node,deparse_expr_cxt*context);
177181
staticvoidappendGroupByClause(List*tlist,deparse_expr_cxt*context);
178182
staticvoidappendAggOrderBy(List*orderList,List*targetList,
@@ -1028,7 +1032,7 @@ deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs,
10281032
* For a join or upper relation the input tlist gives the list of
10291033
* columns required to be fetched from the foreign server.
10301034
*/
1031-
deparseExplicitTargetList(tlist,retrieved_attrs,context);
1035+
deparseExplicitTargetList(tlist,false,retrieved_attrs,context);
10321036
}
10331037
else
10341038
{
@@ -1071,7 +1075,7 @@ deparseFromExpr(List *quals, deparse_expr_cxt *context)
10711075
appendStringInfoString(buf," FROM ");
10721076
deparseFromExprForRel(buf,context->root,scanrel,
10731077
(bms_num_members(scanrel->relids)>1),
1074-
context->params_list);
1078+
(Index)0,NULL,context->params_list);
10751079

10761080
/* Construct WHERE clause */
10771081
if (quals!=NIL)
@@ -1340,9 +1344,14 @@ get_jointype_name(JoinType jointype)
13401344
*
13411345
* retrieved_attrs is the list of continuously increasing integers starting
13421346
* from 1. It has same number of entries as tlist.
1347+
*
1348+
* This is used for both SELECT and RETURNING targetlists; the is_returning
1349+
* parameter is true only for a RETURNING targetlist.
13431350
*/
13441351
staticvoid
1345-
deparseExplicitTargetList(List*tlist,List**retrieved_attrs,
1352+
deparseExplicitTargetList(List*tlist,
1353+
boolis_returning,
1354+
List**retrieved_attrs,
13461355
deparse_expr_cxt*context)
13471356
{
13481357
ListCell*lc;
@@ -1357,13 +1366,16 @@ deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
13571366

13581367
if (i>0)
13591368
appendStringInfoString(buf,", ");
1369+
elseif (is_returning)
1370+
appendStringInfoString(buf," RETURNING ");
1371+
13601372
deparseExpr((Expr*)tle->expr,context);
13611373

13621374
*retrieved_attrs=lappend_int(*retrieved_attrs,i+1);
13631375
i++;
13641376
}
13651377

1366-
if (i==0)
1378+
if (i==0&& !is_returning)
13671379
appendStringInfoString(buf,"NULL");
13681380
}
13691381

@@ -1406,27 +1418,107 @@ deparseSubqueryTargetList(deparse_expr_cxt *context)
14061418
* The function constructs ... JOIN ... ON ... for join relation. For a base
14071419
* relation it just returns schema-qualified tablename, with the appropriate
14081420
* alias if so requested.
1421+
*
1422+
* 'ignore_rel' is either zero or the RT index of a target relation. In the
1423+
* latter case the function constructs FROM clause of UPDATE or USING clause
1424+
* of DELETE; it deparses the join relation as if the relation never contained
1425+
* the target relation, and creates a List of conditions to be deparsed into
1426+
* the top-level WHERE clause, which is returned to *ignore_conds.
14091427
*/
14101428
staticvoid
14111429
deparseFromExprForRel(StringInfobuf,PlannerInfo*root,RelOptInfo*foreignrel,
1412-
booluse_alias,List**params_list)
1430+
booluse_alias,Indexignore_rel,List**ignore_conds,
1431+
List**params_list)
14131432
{
14141433
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)foreignrel->fdw_private;
14151434

14161435
if (IS_JOIN_REL(foreignrel))
14171436
{
14181437
StringInfoDatajoin_sql_o;
14191438
StringInfoDatajoin_sql_i;
1439+
RelOptInfo*outerrel=fpinfo->outerrel;
1440+
RelOptInfo*innerrel=fpinfo->innerrel;
1441+
boolouterrel_is_target= false;
1442+
boolinnerrel_is_target= false;
14201443

1421-
/* Deparse outer relation */
1422-
initStringInfo(&join_sql_o);
1423-
deparseRangeTblRef(&join_sql_o,root,fpinfo->outerrel,
1424-
fpinfo->make_outerrel_subquery,params_list);
1444+
if (ignore_rel>0&&bms_is_member(ignore_rel,foreignrel->relids))
1445+
{
1446+
/*
1447+
* If this is an inner join, add joinclauses to *ignore_conds and
1448+
* set it to empty so that those can be deparsed into the WHERE
1449+
* clause. Note that since the target relation can never be
1450+
* within the nullable side of an outer join, those could safely
1451+
* be pulled up into the WHERE clause (see foreign_join_ok()).
1452+
* Note also that since the target relation is only inner-joined
1453+
* to any other relation in the query, all conditions in the join
1454+
* tree mentioning the target relation could be deparsed into the
1455+
* WHERE clause by doing this recursively.
1456+
*/
1457+
if (fpinfo->jointype==JOIN_INNER)
1458+
{
1459+
*ignore_conds=list_concat(*ignore_conds,
1460+
list_copy(fpinfo->joinclauses));
1461+
fpinfo->joinclauses=NIL;
1462+
}
14251463

1426-
/* Deparse inner relation */
1427-
initStringInfo(&join_sql_i);
1428-
deparseRangeTblRef(&join_sql_i,root,fpinfo->innerrel,
1429-
fpinfo->make_innerrel_subquery,params_list);
1464+
/*
1465+
* Check if either of the input relations is the target relation.
1466+
*/
1467+
if (outerrel->relid==ignore_rel)
1468+
outerrel_is_target= true;
1469+
elseif (innerrel->relid==ignore_rel)
1470+
innerrel_is_target= true;
1471+
}
1472+
1473+
/* Deparse outer relation if not the target relation. */
1474+
if (!outerrel_is_target)
1475+
{
1476+
initStringInfo(&join_sql_o);
1477+
deparseRangeTblRef(&join_sql_o,root,outerrel,
1478+
fpinfo->make_outerrel_subquery,
1479+
ignore_rel,ignore_conds,params_list);
1480+
1481+
/*
1482+
* If inner relation is the target relation, skip deparsing it.
1483+
* Note that since the join of the target relation with any other
1484+
* relation in the query is an inner join and can never be within
1485+
* the nullable side of an outer join, the join could be
1486+
* interchanged with higher-level joins (cf. identity 1 on outer
1487+
* join reordering shown in src/backend/optimizer/README), which
1488+
* means it's safe to skip the target-relation deparsing here.
1489+
*/
1490+
if (innerrel_is_target)
1491+
{
1492+
Assert(fpinfo->jointype==JOIN_INNER);
1493+
Assert(fpinfo->joinclauses==NIL);
1494+
appendStringInfo(buf,"%s",join_sql_o.data);
1495+
return;
1496+
}
1497+
}
1498+
1499+
/* Deparse inner relation if not the target relation. */
1500+
if (!innerrel_is_target)
1501+
{
1502+
initStringInfo(&join_sql_i);
1503+
deparseRangeTblRef(&join_sql_i,root,innerrel,
1504+
fpinfo->make_innerrel_subquery,
1505+
ignore_rel,ignore_conds,params_list);
1506+
1507+
/*
1508+
* If outer relation is the target relation, skip deparsing it.
1509+
* See the above note about safety.
1510+
*/
1511+
if (outerrel_is_target)
1512+
{
1513+
Assert(fpinfo->jointype==JOIN_INNER);
1514+
Assert(fpinfo->joinclauses==NIL);
1515+
appendStringInfo(buf,"%s",join_sql_i.data);
1516+
return;
1517+
}
1518+
}
1519+
1520+
/* Neither of the relations is the target relation. */
1521+
Assert(!outerrel_is_target&& !innerrel_is_target);
14301522

14311523
/*
14321524
* For a join relation FROM clause entry is deparsed as
@@ -1486,7 +1578,8 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
14861578
*/
14871579
staticvoid
14881580
deparseRangeTblRef(StringInfobuf,PlannerInfo*root,RelOptInfo*foreignrel,
1489-
boolmake_subquery,List**params_list)
1581+
boolmake_subquery,Indexignore_rel,List**ignore_conds,
1582+
List**params_list)
14901583
{
14911584
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)foreignrel->fdw_private;
14921585

@@ -1501,6 +1594,14 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
15011594
List*retrieved_attrs;
15021595
intncols;
15031596

1597+
/*
1598+
* The given relation shouldn't contain the target relation, because
1599+
* this should only happen for input relations for a full join, and
1600+
* such relations can never contain an UPDATE/DELETE target.
1601+
*/
1602+
Assert(ignore_rel==0||
1603+
!bms_is_member(ignore_rel,foreignrel->relids));
1604+
15041605
/* Deparse the subquery representing the relation. */
15051606
appendStringInfoChar(buf,'(');
15061607
deparseSelectStmtForRel(buf,root,foreignrel,NIL,
@@ -1534,7 +1635,8 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
15341635
}
15351636
}
15361637
else
1537-
deparseFromExprForRel(buf,root,foreignrel, true,params_list);
1638+
deparseFromExprForRel(buf,root,foreignrel, true,ignore_rel,
1639+
ignore_conds,params_list);
15381640
}
15391641

15401642
/*
@@ -1645,35 +1747,46 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root,
16451747
/*
16461748
* deparse remote UPDATE statement
16471749
*
1648-
* The statement text is appended to buf, and we also create an integer List
1649-
* of the columns being retrieved by RETURNING (if any), which is returned
1650-
* to *retrieved_attrs.
1750+
* 'buf' is the output buffer to append the statement to
1751+
* 'rtindex' is the RT index of the associated target relation
1752+
* 'rel' is the relation descriptor for the target relation
1753+
* 'foreignrel' is the RelOptInfo for the target relation or the join relation
1754+
*containing all base relations in the query
1755+
* 'targetlist' is the tlist of the underlying foreign-scan plan node
1756+
* 'targetAttrs' is the target columns of the UPDATE
1757+
* 'remote_conds' is the qual clauses that must be evaluated remotely
1758+
* '*params_list' is an output list of exprs that will become remote Params
1759+
* 'returningList' is the RETURNING targetlist
1760+
* '*retrieved_attrs' is an output list of integers of columns being retrieved
1761+
*by RETURNING (if any)
16511762
*/
16521763
void
16531764
deparseDirectUpdateSql(StringInfobuf,PlannerInfo*root,
16541765
Indexrtindex,Relationrel,
1766+
RelOptInfo*foreignrel,
16551767
List*targetlist,
16561768
List*targetAttrs,
16571769
List*remote_conds,
16581770
List**params_list,
16591771
List*returningList,
16601772
List**retrieved_attrs)
16611773
{
1662-
RelOptInfo*baserel=root->simple_rel_array[rtindex];
16631774
deparse_expr_cxtcontext;
16641775
intnestlevel;
16651776
boolfirst;
16661777
ListCell*lc;
16671778

16681779
/* Set up context struct for recursion */
16691780
context.root=root;
1670-
context.foreignrel=baserel;
1671-
context.scanrel=baserel;
1781+
context.foreignrel=foreignrel;
1782+
context.scanrel=foreignrel;
16721783
context.buf=buf;
16731784
context.params_list=params_list;
16741785

16751786
appendStringInfoString(buf,"UPDATE ");
16761787
deparseRelation(buf,rel);
1788+
if (foreignrel->reloptkind==RELOPT_JOINREL)
1789+
appendStringInfo(buf," %s%d",REL_ALIAS_PREFIX,rtindex);
16771790
appendStringInfoString(buf," SET ");
16781791

16791792
/* Make sure any constants in the exprs are printed portably */
@@ -1700,14 +1813,28 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
17001813

17011814
reset_transmission_modes(nestlevel);
17021815

1816+
if (foreignrel->reloptkind==RELOPT_JOINREL)
1817+
{
1818+
List*ignore_conds=NIL;
1819+
1820+
appendStringInfo(buf," FROM ");
1821+
deparseFromExprForRel(buf,root,foreignrel, true,rtindex,
1822+
&ignore_conds,params_list);
1823+
remote_conds=list_concat(remote_conds,ignore_conds);
1824+
}
1825+
17031826
if (remote_conds)
17041827
{
17051828
appendStringInfoString(buf," WHERE ");
17061829
appendConditions(remote_conds,&context);
17071830
}
17081831

1709-
deparseReturningList(buf,root,rtindex,rel, false,
1710-
returningList,retrieved_attrs);
1832+
if (foreignrel->reloptkind==RELOPT_JOINREL)
1833+
deparseExplicitTargetList(returningList, true,retrieved_attrs,
1834+
&context);
1835+
else
1836+
deparseReturningList(buf,root,rtindex,rel, false,
1837+
returningList,retrieved_attrs);
17111838
}
17121839

17131840
/*
@@ -1735,39 +1862,62 @@ deparseDeleteSql(StringInfo buf, PlannerInfo *root,
17351862
/*
17361863
* deparse remote DELETE statement
17371864
*
1738-
* The statement text is appended to buf, and we also create an integer List
1739-
* of the columns being retrieved by RETURNING (if any), which is returned
1740-
* to *retrieved_attrs.
1865+
* 'buf' is the output buffer to append the statement to
1866+
* 'rtindex' is the RT index of the associated target relation
1867+
* 'rel' is the relation descriptor for the target relation
1868+
* 'foreignrel' is the RelOptInfo for the target relation or the join relation
1869+
*containing all base relations in the query
1870+
* 'remote_conds' is the qual clauses that must be evaluated remotely
1871+
* '*params_list' is an output list of exprs that will become remote Params
1872+
* 'returningList' is the RETURNING targetlist
1873+
* '*retrieved_attrs' is an output list of integers of columns being retrieved
1874+
*by RETURNING (if any)
17411875
*/
17421876
void
17431877
deparseDirectDeleteSql(StringInfobuf,PlannerInfo*root,
17441878
Indexrtindex,Relationrel,
1879+
RelOptInfo*foreignrel,
17451880
List*remote_conds,
17461881
List**params_list,
17471882
List*returningList,
17481883
List**retrieved_attrs)
17491884
{
1750-
RelOptInfo*baserel=root->simple_rel_array[rtindex];
17511885
deparse_expr_cxtcontext;
17521886

17531887
/* Set up context struct for recursion */
17541888
context.root=root;
1755-
context.foreignrel=baserel;
1756-
context.scanrel=baserel;
1889+
context.foreignrel=foreignrel;
1890+
context.scanrel=foreignrel;
17571891
context.buf=buf;
17581892
context.params_list=params_list;
17591893

17601894
appendStringInfoString(buf,"DELETE FROM ");
17611895
deparseRelation(buf,rel);
1896+
if (foreignrel->reloptkind==RELOPT_JOINREL)
1897+
appendStringInfo(buf," %s%d",REL_ALIAS_PREFIX,rtindex);
1898+
1899+
if (foreignrel->reloptkind==RELOPT_JOINREL)
1900+
{
1901+
List*ignore_conds=NIL;
1902+
1903+
appendStringInfo(buf," USING ");
1904+
deparseFromExprForRel(buf,root,foreignrel, true,rtindex,
1905+
&ignore_conds,params_list);
1906+
remote_conds=list_concat(remote_conds,ignore_conds);
1907+
}
17621908

17631909
if (remote_conds)
17641910
{
17651911
appendStringInfoString(buf," WHERE ");
17661912
appendConditions(remote_conds,&context);
17671913
}
17681914

1769-
deparseReturningList(buf,root,rtindex,rel, false,
1770-
returningList,retrieved_attrs);
1915+
if (foreignrel->reloptkind==RELOPT_JOINREL)
1916+
deparseExplicitTargetList(returningList, true,retrieved_attrs,
1917+
&context);
1918+
else
1919+
deparseReturningList(buf,root,rtindex,rel, false,
1920+
returningList,retrieved_attrs);
17711921
}
17721922

17731923
/*

0 commit comments

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