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

Commitb30fb56

Browse files
committed
postgres_fdw: Push down FULL JOINs with restriction clauses.
The previous deparsing logic wasn't smart enough to produce subquerieswhen deparsing; make it smart enough to do that. However, we only doit that way when necessary, because it generates more complicated SQLwhich will be harder for any humans reading the queries to understand.Etsuro Fujita, reviewed by Ashutosh BapatDiscussion:http://postgr.es/m/c449261a-b033-dc02-9254-2fe5b7044795@lab.ntt.co.jp
1 parenta3eac98 commitb30fb56

File tree

5 files changed

+498
-40
lines changed

5 files changed

+498
-40
lines changed

‎contrib/postgres_fdw/deparse.c‎

Lines changed: 258 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,8 @@ typedef struct deparse_expr_cxt
109109
/* Handy macro to add relation name qualification */
110110
#defineADD_REL_QUALIFIER(buf,varno)\
111111
appendStringInfo((buf), "%s%d.", REL_ALIAS_PREFIX, (varno))
112+
#defineSUBQUERY_REL_ALIAS_PREFIX"s"
113+
#defineSUBQUERY_COL_ALIAS_PREFIX"c"
112114

113115
/*
114116
* Functions to determine whether an expression can be evaluated safely on
@@ -132,6 +134,7 @@ static void deparseTargetList(StringInfo buf,
132134
List**retrieved_attrs);
133135
staticvoiddeparseExplicitTargetList(List*tlist,List**retrieved_attrs,
134136
deparse_expr_cxt*context);
137+
staticvoiddeparseSubqueryTargetList(deparse_expr_cxt*context);
135138
staticvoiddeparseReturningList(StringInfobuf,PlannerInfo*root,
136139
Indexrtindex,Relationrel,
137140
booltrig_after_row,
@@ -159,14 +162,17 @@ static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
159162
deparse_expr_cxt*context);
160163
staticvoidprintRemotePlaceholder(Oidparamtype,int32paramtypmod,
161164
deparse_expr_cxt*context);
162-
staticvoiddeparseSelectSql(List*tlist,List**retrieved_attrs,
165+
staticvoiddeparseSelectSql(List*tlist,boolis_subquery,List**retrieved_attrs,
163166
deparse_expr_cxt*context);
164167
staticvoiddeparseLockingClause(deparse_expr_cxt*context);
165168
staticvoidappendOrderByClause(List*pathkeys,deparse_expr_cxt*context);
166169
staticvoidappendConditions(List*exprs,deparse_expr_cxt*context);
167170
staticvoiddeparseFromExprForRel(StringInfobuf,PlannerInfo*root,
168171
RelOptInfo*joinrel,booluse_alias,List**params_list);
169172
staticvoiddeparseFromExpr(List*quals,deparse_expr_cxt*context);
173+
staticvoiddeparseRangeTblRef(StringInfobuf,PlannerInfo*root,
174+
RelOptInfo*foreignrel,boolmake_subquery,
175+
List**params_list);
170176
staticvoiddeparseAggref(Aggref*node,deparse_expr_cxt*context);
171177
staticvoidappendGroupByClause(List*tlist,deparse_expr_cxt*context);
172178
staticvoidappendAggOrderBy(List*orderList,List*targetList,
@@ -175,6 +181,14 @@ static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
175181
staticNode*deparseSortGroupClause(Indexref,List*tlist,
176182
deparse_expr_cxt*context);
177183

184+
/*
185+
* Helper functions
186+
*/
187+
staticboolis_subquery_var(Var*node,RelOptInfo*foreignrel,
188+
int*relno,int*colno);
189+
staticvoidget_relation_column_alias_ids(Var*node,RelOptInfo*foreignrel,
190+
int*relno,int*colno);
191+
178192

179193
/*
180194
* Examine each qual clause in input_conds, and classify them into two groups,
@@ -896,12 +910,16 @@ build_tlist_to_deparse(RelOptInfo *foreignrel)
896910
*
897911
* pathkeys is the list of pathkeys to order the result by.
898912
*
913+
* is_subquery is the flag to indicate whether to deparse the specified
914+
* relation as a subquery.
915+
*
899916
* List of columns selected is returned in retrieved_attrs.
900917
*/
901918
externvoid
902919
deparseSelectStmtForRel(StringInfobuf,PlannerInfo*root,RelOptInfo*rel,
903920
List*tlist,List*remote_conds,List*pathkeys,
904-
List**retrieved_attrs,List**params_list)
921+
boolis_subquery,List**retrieved_attrs,
922+
List**params_list)
905923
{
906924
deparse_expr_cxtcontext;
907925
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)rel->fdw_private;
@@ -925,7 +943,7 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
925943
context.params_list=params_list;
926944

927945
/* Construct SELECT clause */
928-
deparseSelectSql(tlist,retrieved_attrs,&context);
946+
deparseSelectSql(tlist,is_subquery,retrieved_attrs,&context);
929947

930948
/*
931949
* For upper relations, the WHERE clause is built from the remote
@@ -972,13 +990,16 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
972990
* contains just "SELECT ... ".
973991
*
974992
* We also create an integer List of the columns being retrieved, which is
975-
* returned to *retrieved_attrs.
993+
* returned to *retrieved_attrs, unless we deparse the specified relation
994+
* as a subquery.
976995
*
977-
* tlist is the list of desired columns. Read prologue of
978-
* deparseSelectStmtForRel() for details.
996+
* tlist is the list of desired columns. is_subquery is the flag to
997+
* indicate whether to deparse the specified relation as a subquery.
998+
* Read prologue of deparseSelectStmtForRel() for details.
979999
*/
9801000
staticvoid
981-
deparseSelectSql(List*tlist,List**retrieved_attrs,deparse_expr_cxt*context)
1001+
deparseSelectSql(List*tlist,boolis_subquery,List**retrieved_attrs,
1002+
deparse_expr_cxt*context)
9821003
{
9831004
StringInfobuf=context->buf;
9841005
RelOptInfo*foreignrel=context->foreignrel;
@@ -990,10 +1011,22 @@ deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
9901011
*/
9911012
appendStringInfoString(buf,"SELECT ");
9921013

993-
if (foreignrel->reloptkind==RELOPT_JOINREL||
994-
foreignrel->reloptkind==RELOPT_UPPER_REL)
1014+
if (is_subquery)
1015+
{
1016+
/*
1017+
* For a relation that is deparsed as a subquery, emit expressions
1018+
* specified in the relation's reltarget. Note that since this is
1019+
* for the subquery, no need to care about *retrieved_attrs.
1020+
*/
1021+
deparseSubqueryTargetList(context);
1022+
}
1023+
elseif (foreignrel->reloptkind==RELOPT_JOINREL||
1024+
foreignrel->reloptkind==RELOPT_UPPER_REL)
9951025
{
996-
/* For a join relation use the input tlist */
1026+
/*
1027+
* For a join or upper relation the input tlist gives the list of
1028+
* columns required to be fetched from the foreign server.
1029+
*/
9971030
deparseExplicitTargetList(tlist,retrieved_attrs,context);
9981031
}
9991032
else
@@ -1155,10 +1188,18 @@ deparseLockingClause(deparse_expr_cxt *context)
11551188
StringInfobuf=context->buf;
11561189
PlannerInfo*root=context->root;
11571190
RelOptInfo*rel=context->scanrel;
1191+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)rel->fdw_private;
11581192
intrelid=-1;
11591193

11601194
while ((relid=bms_next_member(rel->relids,relid)) >=0)
11611195
{
1196+
/*
1197+
* Ignore relation if it appears in a lower subquery. Locking clause
1198+
* for such a relation is included in the subquery if necessary.
1199+
*/
1200+
if (bms_is_member(relid,fpinfo->lower_subquery_rels))
1201+
continue;
1202+
11621203
/*
11631204
* Add FOR UPDATE/SHARE if appropriate. We apply locking during the
11641205
* initial row fetch, rather than later on as is done for local
@@ -1329,6 +1370,40 @@ deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
13291370
appendStringInfoString(buf,"NULL");
13301371
}
13311372

1373+
/*
1374+
* Emit expressions specified in the given relation's reltarget.
1375+
*
1376+
* This is used for deparsing the given relation as a subquery.
1377+
*/
1378+
staticvoid
1379+
deparseSubqueryTargetList(deparse_expr_cxt*context)
1380+
{
1381+
StringInfobuf=context->buf;
1382+
RelOptInfo*foreignrel=context->foreignrel;
1383+
boolfirst;
1384+
ListCell*lc;
1385+
1386+
/* Should only be called in these cases. */
1387+
Assert(foreignrel->reloptkind==RELOPT_BASEREL||
1388+
foreignrel->reloptkind==RELOPT_JOINREL);
1389+
1390+
first= true;
1391+
foreach(lc,foreignrel->reltarget->exprs)
1392+
{
1393+
Node*node= (Node*)lfirst(lc);
1394+
1395+
if (!first)
1396+
appendStringInfoString(buf,", ");
1397+
first= false;
1398+
1399+
deparseExpr((Expr*)node,context);
1400+
}
1401+
1402+
/* Don't generate bad syntax if no expressions */
1403+
if (first)
1404+
appendStringInfoString(buf,"NULL");
1405+
}
1406+
13321407
/*
13331408
* Construct FROM clause for given relation
13341409
*
@@ -1344,18 +1419,18 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
13441419

13451420
if (foreignrel->reloptkind==RELOPT_JOINREL)
13461421
{
1347-
RelOptInfo*rel_o=fpinfo->outerrel;
1348-
RelOptInfo*rel_i=fpinfo->innerrel;
13491422
StringInfoDatajoin_sql_o;
13501423
StringInfoDatajoin_sql_i;
13511424

13521425
/* Deparse outer relation */
13531426
initStringInfo(&join_sql_o);
1354-
deparseFromExprForRel(&join_sql_o,root,rel_o, true,params_list);
1427+
deparseRangeTblRef(&join_sql_o,root,fpinfo->outerrel,
1428+
fpinfo->make_outerrel_subquery,params_list);
13551429

13561430
/* Deparse inner relation */
13571431
initStringInfo(&join_sql_i);
1358-
deparseFromExprForRel(&join_sql_i,root,rel_i, true,params_list);
1432+
deparseRangeTblRef(&join_sql_i,root,fpinfo->innerrel,
1433+
fpinfo->make_innerrel_subquery,params_list);
13591434

13601435
/*
13611436
* For a join relation FROM clause entry is deparsed as
@@ -1410,6 +1485,63 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
14101485
}
14111486
}
14121487

1488+
/*
1489+
* Append FROM clause entry for the given relation into buf.
1490+
*/
1491+
staticvoid
1492+
deparseRangeTblRef(StringInfobuf,PlannerInfo*root,RelOptInfo*foreignrel,
1493+
boolmake_subquery,List**params_list)
1494+
{
1495+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)foreignrel->fdw_private;
1496+
1497+
/* Should only be called in these cases. */
1498+
Assert(foreignrel->reloptkind==RELOPT_BASEREL||
1499+
foreignrel->reloptkind==RELOPT_JOINREL);
1500+
1501+
Assert(fpinfo->local_conds==NIL);
1502+
1503+
/* If make_subquery is true, deparse the relation as a subquery. */
1504+
if (make_subquery)
1505+
{
1506+
List*retrieved_attrs;
1507+
intncols;
1508+
1509+
/* Deparse the subquery representing the relation. */
1510+
appendStringInfoChar(buf,'(');
1511+
deparseSelectStmtForRel(buf,root,foreignrel,NIL,
1512+
fpinfo->remote_conds,NIL, true,
1513+
&retrieved_attrs,params_list);
1514+
appendStringInfoChar(buf,')');
1515+
1516+
/* Append the relation alias. */
1517+
appendStringInfo(buf," %s%d",SUBQUERY_REL_ALIAS_PREFIX,
1518+
fpinfo->relation_index);
1519+
1520+
/*
1521+
* Append the column aliases if needed. Note that the subquery emits
1522+
* expressions specified in the relation's reltarget (see
1523+
* deparseSubqueryTargetList).
1524+
*/
1525+
ncols=list_length(foreignrel->reltarget->exprs);
1526+
if (ncols>0)
1527+
{
1528+
inti;
1529+
1530+
appendStringInfoChar(buf,'(');
1531+
for (i=1;i <=ncols;i++)
1532+
{
1533+
if (i>1)
1534+
appendStringInfoString(buf,", ");
1535+
1536+
appendStringInfo(buf,"%s%d",SUBQUERY_COL_ALIAS_PREFIX,i);
1537+
}
1538+
appendStringInfoChar(buf,')');
1539+
}
1540+
}
1541+
else
1542+
deparseFromExprForRel(buf,root,foreignrel, true,params_list);
1543+
}
1544+
14131545
/*
14141546
* deparse remote INSERT statement
14151547
*
@@ -2054,10 +2186,25 @@ static void
20542186
deparseVar(Var*node,deparse_expr_cxt*context)
20552187
{
20562188
Relidsrelids=context->scanrel->relids;
2189+
intrelno;
2190+
intcolno;
20572191

20582192
/* Qualify columns when multiple relations are involved. */
20592193
boolqualify_col= (bms_num_members(relids)>1);
20602194

2195+
/*
2196+
* If the Var belongs to the foreign relation that is deparsed as a
2197+
* subquery, use the relation and column alias to the Var provided
2198+
* by the subquery, instead of the remote name.
2199+
*/
2200+
if (is_subquery_var(node,context->scanrel,&relno,&colno))
2201+
{
2202+
appendStringInfo(context->buf,"%s%d.%s%d",
2203+
SUBQUERY_REL_ALIAS_PREFIX,relno,
2204+
SUBQUERY_COL_ALIAS_PREFIX,colno);
2205+
return;
2206+
}
2207+
20612208
if (bms_is_member(node->varno,relids)&&node->varlevelsup==0)
20622209
deparseColumnRef(context->buf,node->varno,node->varattno,
20632210
context->root,qualify_col);
@@ -2935,3 +3082,100 @@ deparseSortGroupClause(Index ref, List *tlist, deparse_expr_cxt *context)
29353082

29363083
return (Node*)expr;
29373084
}
3085+
3086+
3087+
/*
3088+
* Returns true if given Var is deparsed as a subquery output column, in
3089+
* which case, *relno and *colno are set to the IDs for the relation and
3090+
* column alias to the Var provided by the subquery.
3091+
*/
3092+
staticbool
3093+
is_subquery_var(Var*node,RelOptInfo*foreignrel,int*relno,int*colno)
3094+
{
3095+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)foreignrel->fdw_private;
3096+
RelOptInfo*outerrel=fpinfo->outerrel;
3097+
RelOptInfo*innerrel=fpinfo->innerrel;
3098+
3099+
/* Should only be called in these cases. */
3100+
Assert(foreignrel->reloptkind==RELOPT_BASEREL||
3101+
foreignrel->reloptkind==RELOPT_JOINREL||
3102+
foreignrel->reloptkind==RELOPT_OTHER_MEMBER_REL);
3103+
3104+
/*
3105+
* If the given relation isn't a join relation, it doesn't have any lower
3106+
* subqueries, so the Var isn't a subquery output column.
3107+
*/
3108+
if (foreignrel->reloptkind!=RELOPT_JOINREL)
3109+
return false;
3110+
3111+
/*
3112+
* If the Var doesn't belong to any lower subqueries, it isn't a subquery
3113+
* output column.
3114+
*/
3115+
if (!bms_is_member(node->varno,fpinfo->lower_subquery_rels))
3116+
return false;
3117+
3118+
if (bms_is_member(node->varno,outerrel->relids))
3119+
{
3120+
/*
3121+
* If outer relation is deparsed as a subquery, the Var is an output
3122+
* column of the subquery; get the IDs for the relation/column alias.
3123+
*/
3124+
if (fpinfo->make_outerrel_subquery)
3125+
{
3126+
get_relation_column_alias_ids(node,outerrel,relno,colno);
3127+
return true;
3128+
}
3129+
3130+
/* Otherwise, recurse into the outer relation. */
3131+
returnis_subquery_var(node,outerrel,relno,colno);
3132+
}
3133+
else
3134+
{
3135+
Assert(bms_is_member(node->varno,innerrel->relids));
3136+
3137+
/*
3138+
* If inner relation is deparsed as a subquery, the Var is an output
3139+
* column of the subquery; get the IDs for the relation/column alias.
3140+
*/
3141+
if (fpinfo->make_innerrel_subquery)
3142+
{
3143+
get_relation_column_alias_ids(node,innerrel,relno,colno);
3144+
return true;
3145+
}
3146+
3147+
/* Otherwise, recurse into the inner relation. */
3148+
returnis_subquery_var(node,innerrel,relno,colno);
3149+
}
3150+
}
3151+
3152+
/*
3153+
* Get the IDs for the relation and column alias to given Var belonging to
3154+
* given relation, which are returned into *relno and *colno.
3155+
*/
3156+
staticvoid
3157+
get_relation_column_alias_ids(Var*node,RelOptInfo*foreignrel,
3158+
int*relno,int*colno)
3159+
{
3160+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)foreignrel->fdw_private;
3161+
inti;
3162+
ListCell*lc;
3163+
3164+
/* Get the relation alias ID */
3165+
*relno=fpinfo->relation_index;
3166+
3167+
/* Get the column alias ID */
3168+
i=1;
3169+
foreach(lc,foreignrel->reltarget->exprs)
3170+
{
3171+
if (equal(lfirst(lc), (Node*)node))
3172+
{
3173+
*colno=i;
3174+
return;
3175+
}
3176+
i++;
3177+
}
3178+
3179+
/* Shouldn't get here */
3180+
elog(ERROR,"unexpected expression in subquery output");
3181+
}

0 commit comments

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