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

Commit0d49838

Browse files
committed
Arrange to "inline" SQL functions that appear in a query's FROM clause,
are declared to return set, and consist of just a single SELECT. Wecan replace the FROM-item with a sub-SELECT and then optimize much asif we were dealing with a view. Patch from Richard Rowell, cleaned upby me.
1 parent433c523 commit0d49838

File tree

8 files changed

+424
-48
lines changed

8 files changed

+424
-48
lines changed

‎src/backend/catalog/pg_proc.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.148 2008/01/01 19:45:48 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.149 2008/03/18 22:04:14 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -601,7 +601,8 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
601601
proc->proargtypes.values,
602602
proc->pronargs);
603603
(void)check_sql_fn_retval(funcoid,proc->prorettype,
604-
querytree_list,NULL);
604+
querytree_list,
605+
false,NULL);
605606
}
606607
else
607608
querytree_list=pg_parse_query(prosrc);

‎src/backend/executor/functions.c

Lines changed: 44 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.120 2008/01/01 19:45:49 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.121 2008/03/18 22:04:14 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -20,6 +20,7 @@
2020
#include"commands/trigger.h"
2121
#include"executor/functions.h"
2222
#include"funcapi.h"
23+
#include"nodes/makefuncs.h"
2324
#include"parser/parse_coerce.h"
2425
#include"parser/parse_expr.h"
2526
#include"tcop/tcopprot.h"
@@ -269,6 +270,7 @@ init_sql_fcache(FmgrInfo *finfo)
269270
fcache->returnsTuple=check_sql_fn_retval(foid,
270271
rettype,
271272
queryTree_list,
273+
false,
272274
&fcache->junkFilter);
273275

274276
/* Finally, plan the queries */
@@ -856,7 +858,9 @@ ShutdownSQLFunction(Datum arg)
856858
*
857859
* The return value of a sql function is the value returned by
858860
* the final query in the function. We do some ad-hoc type checking here
859-
* to be sure that the user is returning the type he claims.
861+
* to be sure that the user is returning the type he claims. There are
862+
* also a couple of strange-looking features to assist callers in dealing
863+
* with allowed special cases, such as binary-compatible result types.
860864
*
861865
* For a polymorphic function the passed rettype must be the actual resolved
862866
* output type of the function; we should never see a polymorphic pseudotype
@@ -868,13 +872,18 @@ ShutdownSQLFunction(Datum arg)
868872
* allow "SELECT rowtype_expression", this may be false even when the declared
869873
* function return type is a rowtype.
870874
*
875+
* If insertRelabels is true, then binary-compatible cases are dealt with
876+
* by actually inserting RelabelType nodes into the final SELECT; obviously
877+
* the caller must pass a parsetree that it's okay to modify in this case.
878+
*
871879
* If junkFilter isn't NULL, then *junkFilter is set to a JunkFilter defined
872880
* to convert the function's tuple result to the correct output tuple type.
873881
* Whenever the result value is false (ie, the function isn't returning a
874882
* tuple result), *junkFilter is set to NULL.
875883
*/
876884
bool
877885
check_sql_fn_retval(Oidfunc_id,Oidrettype,List*queryTreeList,
886+
boolinsertRelabels,
878887
JunkFilter**junkFilter)
879888
{
880889
Query*parse;
@@ -945,25 +954,36 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
945954
rettype==VOIDOID)
946955
{
947956
/*
948-
* For scalar-type returns, the target listshould have exactly one
949-
* entry, and its typeshould agree with what the user declared. (As
950-
*of Postgres 7.2,weaccept binary-compatible types too.)
957+
* For scalar-type returns, the target listmust have exactly one
958+
*non-junkentry, and its typemust agree with what the user
959+
*declared; exceptweallow binary-compatible types too.
951960
*/
961+
TargetEntry*tle;
962+
952963
if (tlistlen!=1)
953964
ereport(ERROR,
954965
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
955966
errmsg("return type mismatch in function declared to return %s",
956967
format_type_be(rettype)),
957968
errdetail("Final SELECT must return exactly one column.")));
958969

959-
restype=exprType((Node*) ((TargetEntry*)linitial(tlist))->expr);
970+
/* We assume here that non-junk TLEs must come first in tlists */
971+
tle= (TargetEntry*)linitial(tlist);
972+
Assert(!tle->resjunk);
973+
974+
restype=exprType((Node*)tle->expr);
960975
if (!IsBinaryCoercible(restype,rettype))
961976
ereport(ERROR,
962977
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
963978
errmsg("return type mismatch in function declared to return %s",
964979
format_type_be(rettype)),
965980
errdetail("Actual return type is %s.",
966981
format_type_be(restype))));
982+
if (insertRelabels&&restype!=rettype)
983+
tle->expr= (Expr*)makeRelabelType(tle->expr,
984+
rettype,
985+
-1,
986+
COERCE_DONTCARE);
967987
}
968988
elseif (fn_typtype==TYPTYPE_COMPOSITE||rettype==RECORDOID)
969989
{
@@ -977,14 +997,24 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
977997
* If the target list is of length 1, and the type of the varnode in
978998
* the target list matches the declared return type, this is okay.
979999
* This can happen, for example, where the body of the function is
980-
* 'SELECT func2()', where func2 has the same return type as the
981-
* function that's calling it.
1000+
* 'SELECT func2()', where func2 has the samecompositereturn type
1001+
*as thefunction that's calling it.
9821002
*/
9831003
if (tlistlen==1)
9841004
{
985-
restype=exprType((Node*) ((TargetEntry*)linitial(tlist))->expr);
1005+
TargetEntry*tle= (TargetEntry*)linitial(tlist);
1006+
1007+
Assert(!tle->resjunk);
1008+
restype=exprType((Node*)tle->expr);
9861009
if (IsBinaryCoercible(restype,rettype))
1010+
{
1011+
if (insertRelabels&&restype!=rettype)
1012+
tle->expr= (Expr*)makeRelabelType(tle->expr,
1013+
rettype,
1014+
-1,
1015+
COERCE_DONTCARE);
9871016
return false;/* NOT returning whole tuple */
1017+
}
9881018
}
9891019

9901020
/* Is the rowtype fixed, or determined only at runtime? */
@@ -1043,6 +1073,11 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
10431073
format_type_be(tletype),
10441074
format_type_be(atttype),
10451075
tuplogcols)));
1076+
if (insertRelabels&&tletype!=atttype)
1077+
tle->expr= (Expr*)makeRelabelType(tle->expr,
1078+
atttype,
1079+
-1,
1080+
COERCE_DONTCARE);
10461081
}
10471082

10481083
for (;;)
@@ -1070,14 +1105,6 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList,
10701105
/* Report that we are returning entire tuple result */
10711106
return true;
10721107
}
1073-
elseif (IsPolymorphicType(rettype))
1074-
{
1075-
/* This should already have been caught ... */
1076-
ereport(ERROR,
1077-
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
1078-
errmsg("cannot determine result data type"),
1079-
errdetail("A function returning a polymorphic type must have at least one polymorphic argument.")));
1080-
}
10811108
else
10821109
ereport(ERROR,
10831110
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),

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

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.226 2008/01/01 19:45:50 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.227 2008/03/18 22:04:14 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -253,13 +253,20 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
253253
/*
254254
* Look for IN clauses at the top level of WHERE, and transform them into
255255
* joins. Note that this step only handles IN clauses originally at top
256-
* level of WHERE; if we pull up any subqueriesin the next step, their
257-
*INs areprocessed just before pulling them up.
256+
* level of WHERE; if we pull up any subqueriesbelow, their INs are
257+
* processed just before pulling them up.
258258
*/
259259
if (parse->hasSubLinks)
260260
parse->jointree->quals=pull_up_IN_clauses(root,
261261
parse->jointree->quals);
262262

263+
/*
264+
* Scan the rangetable for set-returning functions, and inline them
265+
* if possible (producing subqueries that might get pulled up next).
266+
* Recursion issues here are handled in the same way as for IN clauses.
267+
*/
268+
inline_set_returning_functions(root);
269+
263270
/*
264271
* Check to see if any subqueries in the rangetable can be merged into
265272
* this query.

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 54 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
*
66
* NOTE: the intended sequence for invoking these operations is
77
*pull_up_IN_clauses
8+
*inline_set_returning_functions
89
*pull_up_subqueries
910
*do expression preprocessing (including flattening JOIN alias vars)
1011
*reduce_outer_joins
@@ -15,7 +16,7 @@
1516
*
1617
*
1718
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.49 2008/01/01 19:45:50 momjian Exp $
19+
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.50 2008/03/18 22:04:14 tgl Exp $
1920
*
2021
*-------------------------------------------------------------------------
2122
*/
@@ -124,6 +125,52 @@ pull_up_IN_clauses(PlannerInfo *root, Node *node)
124125
returnnode;
125126
}
126127

128+
/*
129+
* inline_set_returning_functions
130+
*Attempt to "inline" set-returning functions in the FROM clause.
131+
*
132+
* If an RTE_FUNCTION rtable entry invokes a set-returning function that
133+
* contains just a simple SELECT, we can convert the rtable entry to an
134+
* RTE_SUBQUERY entry exposing the SELECT directly. This is especially
135+
* useful if the subquery can then be "pulled up" for further optimization,
136+
* but we do it even if not, to reduce executor overhead.
137+
*
138+
* This has to be done before we have started to do any optimization of
139+
* subqueries, else any such steps wouldn't get applied to subqueries
140+
* obtained via inlining. However, we do it after pull_up_IN_clauses
141+
* so that we can inline any functions used in IN subselects.
142+
*
143+
* Like most of the planner, this feels free to scribble on its input data
144+
* structure.
145+
*/
146+
void
147+
inline_set_returning_functions(PlannerInfo*root)
148+
{
149+
ListCell*rt;
150+
151+
foreach(rt,root->parse->rtable)
152+
{
153+
RangeTblEntry*rte= (RangeTblEntry*)lfirst(rt);
154+
155+
if (rte->rtekind==RTE_FUNCTION)
156+
{
157+
Query*funcquery;
158+
159+
/* Check safety of expansion, and expand if possible */
160+
funcquery=inline_set_returning_function(root,rte->funcexpr);
161+
if (funcquery)
162+
{
163+
/* Successful expansion, replace the rtable entry */
164+
rte->rtekind=RTE_SUBQUERY;
165+
rte->subquery=funcquery;
166+
rte->funcexpr=NULL;
167+
rte->funccoltypes=NIL;
168+
rte->funccoltypmods=NIL;
169+
}
170+
}
171+
}
172+
}
173+
127174
/*
128175
* pull_up_subqueries
129176
*Look for subqueries in the rangetable that can be pulled up into
@@ -296,6 +343,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
296343
subroot->query_level=root->query_level;
297344
subroot->planner_cxt=CurrentMemoryContext;
298345
subroot->init_plans=NIL;
346+
subroot->eq_classes=NIL;
299347
subroot->in_info_list=NIL;
300348
subroot->append_rel_list=NIL;
301349

@@ -307,6 +355,11 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
307355
subquery->jointree->quals=pull_up_IN_clauses(subroot,
308356
subquery->jointree->quals);
309357

358+
/*
359+
* Similarly, inline any set-returning functions in its rangetable.
360+
*/
361+
inline_set_returning_functions(subroot);
362+
310363
/*
311364
* Recursively pull up the subquery's subqueries, so that
312365
* pull_up_subqueries' processing is complete for its jointree and

0 commit comments

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