Re: [PATCHES] Patch to inline stable SQL set returning UDFs
Richard Rowell [EMAIL PROTECTED] writes: I discussed this hack with Neil and Josh in Ottawa back in May. I took a stab at it a few weeks ago and below are the results. It is mostly modeled after the existing inline_function. The approach taken is to recursively cleanup the RTable where stable SQL functions are found in it. Applied with revisions --- the recursion issue wasn't being managed properly, and there were some other problems ... regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Patch to inline stable SQL set returning UDFs
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- [EMAIL PROTECTED] wrote: I discussed this hack with Neil and Josh in Ottawa back in May. I took a stab at it a few weeks ago and below are the results. It is mostly modeled after the existing inline_function. The approach taken is to recursively cleanup the rtable. I've been testing this in our dev environment for a few weeks now. I'm sure it still has issues but I think it is about as clean as I can get it without help from a wider audience. This is my first backend patch so please be gentle. I want to thank Neil for answering many of my supremely ignorant questions on IRC. [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] Patch to inline stable SQL set returning UDFs
I discussed this hack with Neil and Josh in Ottawa back in May. I
took a stab at it a few weeks ago and below are the results. It is
mostly modeled after the existing inline_function. The approach taken
is to recursively cleanup the RTable where stable SQL functions are
found in it.
I've been testing this in our dev environment for a few weeks now.
I'm sure it still has issues but I think it is about as clean as I can
get it without help from a wider audience. This is my first backend
patch so please be gentle.
I want to thank Neil for answering many of my supremely ignorant
questions on IRC.
--
An eye for eye only ends up making the whole world blind. -- Mohandas Gandhi
diff -r -c3 postgres-8.3/src/backend/optimizer/plan/planner.c postgres-8.3-hacked/src/backend/optimizer/plan/planner.c
*** postgres-8.3/src/backend/optimizer/plan/planner.c 2007-05-26 14:23:01.0 -0400
--- postgres-8.3-hacked/src/backend/optimizer/plan/planner.c 2007-10-01 11:49:08.0 -0400
***
*** 256,261
--- 256,268
parse-jointree-quals = pull_up_IN_clauses(root,
parse-jointree-quals);
+
+ /*
+ * Examine the rtable and inline any stable SQL set returning functions
+ * if possible.
+ */
+ parse-rtable = inline_stable_sql_funcs(parse-rtable);
+
/*
* Check to see if any subqueries in the rangetable can be merged into
* this query.
diff -r -c3 postgres-8.3/src/backend/optimizer/util/clauses.c postgres-8.3-hacked/src/backend/optimizer/util/clauses.c
*** postgres-8.3/src/backend/optimizer/util/clauses.c 2007-09-06 13:31:58.0 -0400
--- postgres-8.3-hacked/src/backend/optimizer/util/clauses.c 2007-10-01 11:49:57.0 -0400
***
*** 101,106
--- 101,117
static void sql_inline_error_callback(void *arg);
static Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod);
+ static Node *
+ substitute_actual_parameters_srf_inliner_mutator(Node *node,
+ substitute_actual_parameters_context *context);
+ static Query *
+ substitute_actual_parameters_srf_inliner(Query *querytree, int nargs, List *args,
+ int *usecounts);
+ static List * inline_stable_sql_funcs_mutator( List* rtable, eval_const_expressions_context *context);
+ static Query* inline_stable_sql_func(FuncExpr *expr, eval_const_expressions_context *context);
+ List * inline_stable_sql_funcs(List* rtable);
+
+
/*
* OPERATOR clause functions
***
*** 4419,4421
--- 4430,4792
else
return mutator(node, context);
}
+
+ /*
+ * Replace Param nodes with the actual arguments passed to the function.
+ */
+ static Query *
+ substitute_actual_parameters_srf_inliner(Query *querytree, int nargs, List *args,
+ int *usecounts)
+ {
+ substitute_actual_parameters_context context;
+
+ context.nargs = nargs;
+ context.args = args;
+ context.usecounts = usecounts;
+
+ return query_tree_mutator(querytree, substitute_actual_parameters_srf_inliner_mutator, context, 0);
+ }
+
+
+ /*
+ * This mutator cleanses the RTable of function body's to be inlined. Each parameter reference
+ * in the function body must be replaced with the appropriate function argument. Sub-queries
+ * are recursed, and sublink parameters are copied as is.
+ */
+ static Node *
+ substitute_actual_parameters_srf_inliner_mutator(Node *node,
+ substitute_actual_parameters_context *context)
+ {
+ if (node == NULL)
+ return NULL;
+ if (IsA(node, Query))
+ {
+ Query *nquery;
+
+ nquery = query_tree_mutator((Query *) node, substitute_actual_parameters_srf_inliner_mutator, context, 0);
+ return (Node *) nquery;
+ }
+ if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ if(param-paramkind == PARAM_SUBLINK )
+ {
+ return copyObject( node );
+ }
+ if (param-paramkind != PARAM_SUBLINK param-paramkind != PARAM_EXTERN)
+ {
+ elog(ERROR, unhanled parameter type);
+ }
+ if (param-paramid = 0 || param-paramid context-nargs)
+ elog(ERROR, invalid paramid: %d, param-paramid);
+
+ /* Count usage of parameter */
+ context-usecounts[param-paramid - 1]++;
+
+ /* Select the appropriate actual arg and replace the Param with it */
+ /* We don't need to copy at this time (it'll get done later) */
+ return list_nth(context-args, param-paramid - 1);
+ }
+ return expression_tree_mutator(node, substitute_actual_parameters_srf_inliner_mutator,
+ (void *) context);
+ }
+
+ /*
+ * Inline stable SQL set-returning functions
+ *
+ *
+ * Takes a function expression as a parameter. If the FuncExpr
+ * is that of a stable SQL set-returning function then we can
+ * replace the function's RTE with a subquery RTE containing the
+ * body of the function. We also recurse into the subqueries RTE
+ *
[PATCHES] Patch to inline stable SQL set returning UDFs
I discussed this hack with Neil and Josh in Ottawa back in May. I
took a stab at it a few weeks ago and below are the results. It is
mostly modeled after the existing inline_function. The approach taken
is to recursively cleanup the rtable.
I've been testing this in our dev environment for a few weeks now.
I'm sure it still has issues but I think it is about as clean as I can
get it without help from a wider audience. This is my first backend
patch so please be gentle.
I want to thank Neil for answering many of my supremely ignorant
questions on IRC.diff -r -c3 postgres-8.3/src/backend/optimizer/plan/planner.c postgres-8.3-hacked/src/backend/optimizer/plan/planner.c
*** postgres-8.3/src/backend/optimizer/plan/planner.c 2007-05-26 14:23:01.0 -0400
--- postgres-8.3-hacked/src/backend/optimizer/plan/planner.c 2007-10-01 11:49:08.0 -0400
***
*** 256,261
--- 256,268
parse-jointree-quals = pull_up_IN_clauses(root,
parse-jointree-quals);
+
+ /*
+ * Examine the rtable and inline any stable SQL set returning functions
+ * if possible.
+ */
+ parse-rtable = inline_stable_sql_funcs(parse-rtable);
+
/*
* Check to see if any subqueries in the rangetable can be merged into
* this query.
diff -r -c3 postgres-8.3/src/backend/optimizer/util/clauses.c postgres-8.3-hacked/src/backend/optimizer/util/clauses.c
*** postgres-8.3/src/backend/optimizer/util/clauses.c 2007-09-06 13:31:58.0 -0400
--- postgres-8.3-hacked/src/backend/optimizer/util/clauses.c 2007-10-01 11:49:57.0 -0400
***
*** 101,106
--- 101,117
static void sql_inline_error_callback(void *arg);
static Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod);
+ static Node *
+ substitute_actual_parameters_srf_inliner_mutator(Node *node,
+ substitute_actual_parameters_context *context);
+ static Query *
+ substitute_actual_parameters_srf_inliner(Query *querytree, int nargs, List *args,
+ int *usecounts);
+ static List * inline_stable_sql_funcs_mutator( List* rtable, eval_const_expressions_context *context);
+ static Query* inline_stable_sql_func(FuncExpr *expr, eval_const_expressions_context *context);
+ List * inline_stable_sql_funcs(List* rtable);
+
+
/*
* OPERATOR clause functions
***
*** 4419,4421
--- 4430,4792
else
return mutator(node, context);
}
+
+ /*
+ * Replace Param nodes with the actual arguments passed to the function.
+ */
+ static Query *
+ substitute_actual_parameters_srf_inliner(Query *querytree, int nargs, List *args,
+ int *usecounts)
+ {
+ substitute_actual_parameters_context context;
+
+ context.nargs = nargs;
+ context.args = args;
+ context.usecounts = usecounts;
+
+ return query_tree_mutator(querytree, substitute_actual_parameters_srf_inliner_mutator, context, 0);
+ }
+
+
+ /*
+ * This mutator cleanses the RTable of function body's to be inlined. Each parameter reference
+ * in the function body must be replaced with the appropriate function argument. Sub-queries
+ * are recursed, and sublink parameters are copied as is.
+ */
+ static Node *
+ substitute_actual_parameters_srf_inliner_mutator(Node *node,
+ substitute_actual_parameters_context *context)
+ {
+ if (node == NULL)
+ return NULL;
+ if (IsA(node, Query))
+ {
+ Query *nquery;
+
+ nquery = query_tree_mutator((Query *) node, substitute_actual_parameters_srf_inliner_mutator, context, 0);
+ return (Node *) nquery;
+ }
+ if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ if(param-paramkind == PARAM_SUBLINK )
+ {
+ return copyObject( node );
+ }
+ if (param-paramkind != PARAM_SUBLINK param-paramkind != PARAM_EXTERN)
+ {
+ elog(ERROR, unhanled parameter type);
+ }
+ if (param-paramid = 0 || param-paramid context-nargs)
+ elog(ERROR, invalid paramid: %d, param-paramid);
+
+ /* Count usage of parameter */
+ context-usecounts[param-paramid - 1]++;
+
+ /* Select the appropriate actual arg and replace the Param with it */
+ /* We don't need to copy at this time (it'll get done later) */
+ return list_nth(context-args, param-paramid - 1);
+ }
+ return expression_tree_mutator(node, substitute_actual_parameters_srf_inliner_mutator,
+ (void *) context);
+ }
+
+ /*
+ * Inline stable SQL set-returning functions
+ *
+ *
+ * Takes a function expression as a parameter. If the FuncExpr
+ * is that of a stable SQL set-returning function then we can
+ * replace the function's RTE with a subquery RTE containing the
+ * body of the function. We also recurse into the subqueries RTE
+ * looking for inlineable functions within those.
+ *
+ * Returns the inlined function body (Query) if successfull, NULL on failure.
