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

Commit69f4b9c

Browse files
committed
Move targetlist SRF handling from expression evaluation to new executor node.
Evaluation of set returning functions (SRFs_ in the targetlist (like SELECTgenerate_series(1,5)) so far was done in the expression evaluation (i.e.ExecEvalExpr()) and projection (i.e. ExecProject/ExecTargetList) code.This meant that most executor nodes performing projection, and mostexpression evaluation functions, had to deal with the possibility that anevaluated expression could return a set of return values.That's bad because it leads to repeated code in a lot of places. It also,and that's my (Andres's) motivation, made it a lot harder to implement amore efficient way of doing expression evaluation.To fix this, introduce a new executor node (ProjectSet) that can evaluatetargetlists containing one or more SRFs. To avoid the complexity of the oldway of handling nested expressions returning sets (e.g. having to pass upExprDoneCond, and dealing with arguments to functions returning sets etc.),those SRFs can only be at the top level of the node's targetlist. Theplanner makes sure (via split_pathtarget_at_srfs()) that SRF evaluation isonly necessary in ProjectSet nodes and that SRFs are only present at thetop level of the node's targetlist. If there are nested SRFs the plannercreates multiple stacked ProjectSet nodes. The ProjectSet nodes always getinput from an underlying node.We also discussed and prototyped evaluating targetlist SRFs using ROWSFROM(), but that turned out to be more complicated than we'd hoped.While moving SRF evaluation to ProjectSet would allow to retain the old"least common multiple" behavior when multiple SRFs are present in onetargetlist (i.e. continue returning rows until all SRFs are at the end oftheir input at the same time), we decided to instead only return rows tillall SRFs are exhausted, returning NULL for already exhausted ones. Wedeemed the previous behavior to be too confusing, unexpected and actuallynot particularly useful.As a side effect, the previously prohibited case of multiple set returningarguments to a function, is now allowed. Not because it's particularlydesirable, but because it ends up working and there seems to be no argumentfor adding code to prohibit it.Currently the behavior for COALESCE and CASE containing SRFs has changed,returning multiple rows from the expression, even when the SRF containing"arm" of the expression is not evaluated. That's because the SRFs areevaluated in a separate ProjectSet node. As that's quite confusing, we'relikely to instead prohibit SRFs in those places. But that's still beingdiscussed, and the code would reside in places not touched here, so that'sa task for later.There's a lot of, now superfluous, code dealing with set return expressionsaround. But as the changes to get rid of those are verbose largely boring,it seems better for readability to keep the cleanup as a separate commit.Author: Tom Lane and Andres FreundDiscussion:https://postgr.es/m/20160822214023.aaxz5l4igypowyri@alap3.anarazel.de
1 parente37360d commit69f4b9c

35 files changed

+1186
-274
lines changed

‎doc/src/sgml/xfunc.sgml

Lines changed: 39 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -962,12 +962,11 @@ SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
962962
</para>
963963

964964
<para>
965-
Currently, functions returning sets can also be called in the select list
965+
Functions returning sets can also be called in the select list
966966
of a query. For each row that the query
967-
generates by itself, the function returning set is invoked, and an output
968-
row is generated for each element of the function's result set. Note,
969-
however, that this capability is deprecated and might be removed in future
970-
releases. The previous example could also be done with queries like
967+
generates by itself, the set-returning function is invoked, and an output
968+
row is generated for each element of the function's result set.
969+
The previous example could also be done with queries like
971970
these:
972971

973972
<screen>
@@ -998,6 +997,33 @@ SELECT name, listchildren(name) FROM nodes;
998997
the <literal>LATERAL</> syntax.
999998
</para>
1000999

1000+
<para>
1001+
If there is more than one set-returning function in the same select
1002+
list, the behavior is similar to what you get from putting the functions
1003+
into a single <literal>LATERAL ROWS FROM( ... )</> <literal>FROM</>-clause
1004+
item. For each row from the underlying query, there is an output row
1005+
using the first result from each function, then an output row using the
1006+
second result, and so on. If some of the set-returning functions
1007+
produce fewer outputs than others, null values are substituted for the
1008+
missing data, so that the total number of rows emitted for one
1009+
underlying row is the same as for the set-returning function that
1010+
produced the most outputs.
1011+
</para>
1012+
1013+
<para>
1014+
Set-returning functions can be nested in a select list, although that is
1015+
not allowed in <literal>FROM</>-clause items. In such cases, each level
1016+
of nesting is treated separately, as though it were
1017+
another <literal>LATERAL ROWS FROM( ... )</> item. For example, in
1018+
<programlisting>
1019+
SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM ...
1020+
</programlisting>
1021+
the set-returning functions <function>srf2</>, <function>srf3</>,
1022+
and <function>srf5</> would be run in lockstep for each row of the
1023+
underlying query, and then <function>srf1</> and <function>srf4</> would
1024+
be applied in lockstep to each row produced by the lower functions.
1025+
</para>
1026+
10011027
<note>
10021028
<para>
10031029
If a function's last command is <command>INSERT</>, <command>UPDATE</>,
@@ -1012,14 +1038,14 @@ SELECT name, listchildren(name) FROM nodes;
10121038

10131039
<note>
10141040
<para>
1015-
The key problem with using set-returning functions in the select list,
1016-
rather thanthe<literal>FROM</> clause, is that putting more than one
1017-
set-returning function in the same select list does not behave very
1018-
sensibly. (Whatyouactually get if you do so is a number of output
1019-
rows equal to the least commonmultiple of the numbers of rows produced
1020-
by eachset-returningfunction.) The <literal>LATERAL</> syntax
1021-
produces less surprising results when calling multiple set-returning
1022-
functions, and should usually be used instead.
1041+
Before <productname>PostgreSQL</> 10, putting more than one
1042+
set-returning function inthesame select list did not behave very
1043+
sensibly unless they always produced equal numbers of rows. Otherwise,
1044+
whatyougot was a number of output rows equal to the least common
1045+
multiple of the numbers of rows produced by the set-returning
1046+
functions. Furthermore, nestedset-returningfunctions did not work at
1047+
all. Use of the <literal>LATERAL</> syntax is recommended when writing
1048+
queries that need to work in older <productname>PostgreSQL</> versions.
10231049
</para>
10241050
</note>
10251051
</sect2>

‎src/backend/commands/explain.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -852,6 +852,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
852852
caseT_Result:
853853
pname=sname="Result";
854854
break;
855+
caseT_ProjectSet:
856+
pname=sname="ProjectSet";
857+
break;
855858
caseT_ModifyTable:
856859
sname="ModifyTable";
857860
switch (((ModifyTable*)plan)->operation)

‎src/backend/executor/Makefile

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,11 +17,12 @@ OBJS = execAmi.o execCurrent.o execGrouping.o execIndexing.o execJunk.o \
1717
execScan.o execTuples.o\
1818
execUtils.o functions.o instrument.o nodeAppend.o nodeAgg.o\
1919
nodeBitmapAnd.o nodeBitmapOr.o\
20-
nodeBitmapHeapscan.o nodeBitmapIndexscan.o nodeCustom.o nodeGather.o\
20+
nodeBitmapHeapscan.o nodeBitmapIndexscan.o\
21+
nodeCustom.o nodeFunctionscan.o nodeGather.o\
2122
nodeHash.o nodeHashjoin.o nodeIndexscan.o nodeIndexonlyscan.o\
2223
nodeLimit.o nodeLockRows.o\
2324
nodeMaterial.o nodeMergeAppend.o nodeMergejoin.o nodeModifyTable.o\
24-
nodeNestloop.onodeFunctionscan.o nodeRecursiveunion.o nodeResult.o\
25+
nodeNestloop.onodeProjectSet.o nodeRecursiveunion.o nodeResult.o\
2526
nodeSamplescan.o nodeSeqscan.o nodeSetOp.o nodeSort.o nodeUnique.o\
2627
nodeValuesscan.o nodeCtescan.o nodeWorktablescan.o\
2728
nodeGroup.o nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o\

‎src/backend/executor/execAmi.c

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@
3939
#include"executor/nodeMergejoin.h"
4040
#include"executor/nodeModifyTable.h"
4141
#include"executor/nodeNestloop.h"
42+
#include"executor/nodeProjectSet.h"
4243
#include"executor/nodeRecursiveunion.h"
4344
#include"executor/nodeResult.h"
4445
#include"executor/nodeSamplescan.h"
@@ -130,6 +131,10 @@ ExecReScan(PlanState *node)
130131
ExecReScanResult((ResultState*)node);
131132
break;
132133

134+
caseT_ProjectSetState:
135+
ExecReScanProjectSet((ProjectSetState*)node);
136+
break;
137+
133138
caseT_ModifyTableState:
134139
ExecReScanModifyTable((ModifyTableState*)node);
135140
break;

‎src/backend/executor/execProcnode.c

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,7 @@
8888
#include"executor/nodeCustom.h"
8989
#include"executor/nodeForeignscan.h"
9090
#include"executor/nodeFunctionscan.h"
91+
#include"executor/nodeGather.h"
9192
#include"executor/nodeGroup.h"
9293
#include"executor/nodeHash.h"
9394
#include"executor/nodeHashjoin.h"
@@ -100,7 +101,7 @@
100101
#include"executor/nodeMergejoin.h"
101102
#include"executor/nodeModifyTable.h"
102103
#include"executor/nodeNestloop.h"
103-
#include"executor/nodeGather.h"
104+
#include"executor/nodeProjectSet.h"
104105
#include"executor/nodeRecursiveunion.h"
105106
#include"executor/nodeResult.h"
106107
#include"executor/nodeSamplescan.h"
@@ -155,6 +156,11 @@ ExecInitNode(Plan *node, EState *estate, int eflags)
155156
estate,eflags);
156157
break;
157158

159+
caseT_ProjectSet:
160+
result= (PlanState*)ExecInitProjectSet((ProjectSet*)node,
161+
estate,eflags);
162+
break;
163+
158164
caseT_ModifyTable:
159165
result= (PlanState*)ExecInitModifyTable((ModifyTable*)node,
160166
estate,eflags);
@@ -392,6 +398,10 @@ ExecProcNode(PlanState *node)
392398
result=ExecResult((ResultState*)node);
393399
break;
394400

401+
caseT_ProjectSetState:
402+
result=ExecProjectSet((ProjectSetState*)node);
403+
break;
404+
395405
caseT_ModifyTableState:
396406
result=ExecModifyTable((ModifyTableState*)node);
397407
break;
@@ -634,6 +644,10 @@ ExecEndNode(PlanState *node)
634644
ExecEndResult((ResultState*)node);
635645
break;
636646

647+
caseT_ProjectSetState:
648+
ExecEndProjectSet((ProjectSetState*)node);
649+
break;
650+
637651
caseT_ModifyTableState:
638652
ExecEndModifyTable((ModifyTableState*)node);
639653
break;

0 commit comments

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