Hello,
in the related discussions mentioned on TODO list
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00292.php
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00991.php
(The 1st is rather on SQL, I didn't focuss on it yet.)
the implementation is discussed from optimizer/executor's point of view.
I'm wondering why not to address the problem at earlier stage: rewrite
the range function to a subquery.
For example:
SELECT *
FROM a, b, func(a.i, b.j) as c, d
WHERE a.i=b.j and b.j = d.k and c>1
may become
SELECT *
FROM a, b, <subquery> as c, d
WHERE a.i=b.j and b.j = d.k and c>1
where <subquery> is
SELECT func(a.i, b.j)
FROM a,b
WHERE a.i=b.j
The WHERE clause of the original query is considered a list of ANDed
subclauses.
Given 'rt_index' is range table index of the function, only those
subclauses are used in the substitution subquery having RT index
lower than 'rt_index'.
Even with such a partial qualification the subquery can safely exclude
(from function calls) rows that the main query won't need anyway.
Note that
1. This is rather an alternative to the optimizer/executor focused
approach that the past discussions covered. I'm aware of questions about
SQL conformance.
2. I only propose this for functions, not for general queries.
3. This draft does not deal with record-returning functions (Although I
might have some idea how to treat them.).
Is there any obvious reason not to go this way?
Attached is my (experimental) implementation.
Kind regards,
Tony.
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 7f4da92..ac4f620 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -563,25 +563,6 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
assign_expr_collations(pstate, funcexpr);
/*
- * The function parameters cannot make use of any variables from other
- * FROM items. (Compare to transformRangeSubselect(); the coding is
- * different though because we didn't parse as a sub-select with its own
- * level of namespace.)
- *
- * XXX this will need further work to support SQL99's LATERAL() feature,
- * wherein such references would indeed be legal.
- */
- if (pstate->p_relnamespace || pstate->p_varnamespace)
- {
- if (contain_vars_of_level(funcexpr, 0))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
- errmsg("function expression in FROM cannot refer to other relations of same query level"),
- parser_errposition(pstate,
- locate_var_of_level(funcexpr, 0))));
- }
-
- /*
* Disallow aggregate functions in the expression. (No reason to postpone
* this check until parseCheckAggregates.)
*/
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 04f9622..b7c93c6 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -18,6 +18,8 @@
#include "commands/trigger.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
+#include "optimizer/var.h"
#include "parser/analyze.h"
#include "parser/parse_coerce.h"
#include "parser/parsetree.h"
@@ -60,6 +62,7 @@ static List *matchLocks(CmdType event, RuleLock *rulelocks,
int varno, Query *parsetree);
static Query *fireRIRrules(Query *parsetree, List *activeRIRs,
bool forUpdatePushedDown);
+static void substituteRTEFunction(Query *parsetree, RangeTblEntry *rte, int rt_index);
/*
@@ -1557,6 +1560,14 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
continue;
}
+ if (rte->rtekind == RTE_FUNCTION)
+ {
+
+ substituteRTEFunction(parsetree, rte, rt_index);
+ continue;
+
+ }
+
/*
* Joins and other non-relation RTEs can be ignored completely.
*/
@@ -2239,3 +2250,192 @@ QueryRewrite(Query *parsetree)
return results;
}
+
+static void
+substituteRTEFunction(Query *parsetree, RangeTblEntry *rte, int rt_index) {
+ bool is_lateral = false;
+ FuncExpr *funcExpr;
+ ListCell *lcell;
+
+ if (!IsA(rte->funcexpr, FuncExpr)) {
+ elog(ERROR, "unrecognized range function type: %d", (int) nodeTag(rte->funcexpr));
+ }
+
+ funcExpr = (FuncExpr *) rte->funcexpr;
+
+ if (!funcExpr->args) {
+ return;
+ }
+
+ foreach(lcell, funcExpr->args) {
+ Node *arg = (Node *) lfirst(lcell);
+
+ is_lateral = contain_vars_of_level(arg, 0);
+ if (is_lateral) {
+ break;
+ }
+ }
+
+
+ if (is_lateral) {
+ List* from;
+ List *rt_sub = NIL;
+ Relids rt_sub_ids = NULL;
+ Query *subquery = makeNode(Query);
+ List *subquery_from = NIL;
+ TargetEntry *subquery_te = makeTargetEntry((Expr *) funcExpr, 1, NULL, false);
+ Node *subquery_quals = NULL;
+ int i;
+
+
+ Assert(parsetree->jointree != NULL && IsA(parsetree->jointree, FromExpr));
+
+
+ from = parsetree->jointree->fromlist;
+
+ /*
+ * Construct FROM list of the substitution subquery.
+ */
+ foreach (lcell, from) {
+ Node *fnode = lfirst(lcell);
+ int rt_ind_f = 0;
+
+ if (IsA(fnode, RangeTblRef)) {
+ rt_ind_f = ((RangeTblRef *) fnode)->rtindex;
+ } else if (IsA(fnode, JoinExpr)) {
+ rt_ind_f = ((JoinExpr *) fnode)->rtindex;
+ } else {
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(fnode));
+ }
+
+ if (rt_ind_f < rt_index) {
+ subquery_from = lappend(subquery_from, fnode);
+ } else {
+ /* Really found RangeTblRef for the function that we're just replacing? */
+ Assert(IsA(fnode, RangeTblRef) && rt_ind_f == rt_index);
+
+ break;
+ }
+ }
+ Assert(subquery_from != NIL);
+
+ /*
+ * Copy the corresponding RTEs into the subquery's range table.
+ */
+ i = 1;
+ foreach (lcell, parsetree->rtable) {
+ Node *rtnode = lfirst(lcell);
+
+ if (i < rt_index) {
+ rt_sub = lappend(rt_sub, rtnode);
+ rt_sub_ids = bms_add_member(rt_sub_ids, i);
+ i++;
+ } else {
+ break;
+ }
+ }
+
+ /*
+ * Construct qualification (WHERE) of the subquery.
+ * The result may only reference RTEs located before the function we're rewriting.
+ */
+ if (parsetree->jointree->quals) {
+ Node *quals = parsetree->jointree->quals;
+ Relids varnos = NULL;
+
+ if (IsA(quals, BoolExpr) && ((BoolExpr *) quals)->boolop == AND_EXPR) {
+ BoolExpr *andExpr = (BoolExpr *) quals;
+ ListCell *arg;
+ List *argsUsable = NIL;
+
+ /*
+ * Only subclauses referencing members of 'rt_sub' can be added.
+ * The other can't be applied until result of the subquery is known.
+ */
+
+ /*
+ * TODO
+ * If some subclause is AND operator, flatten it recursively.
+ *
+ * (i.e. do something like
+ * optimizer/utils/clauses.c:simplify_and_arguments()
+ * )
+ *
+ */
+ foreach (arg, andExpr->args) {
+ Node *andOperand = lfirst(arg);
+
+ varnos = pull_varnos(andOperand);
+ if (bms_is_subset(varnos, rt_sub_ids)) {
+ argsUsable = lappend(argsUsable, andOperand);
+ }
+ bms_free(varnos);
+ }
+
+ if (argsUsable != NIL) {
+ if (list_length(argsUsable) == 1) {
+ subquery_quals = (Node *) list_nth(argsUsable, 0);
+ list_free(argsUsable);
+ } else {
+ subquery_quals = (Node *) make_andclause(argsUsable);
+ }
+ }
+ } else {
+ /*
+ * non-AND operators as well as other expression types are used whole or not at all.
+ */
+ varnos = pull_varnos(quals);
+
+ if (bms_is_subset(varnos, rt_sub_ids)) {
+ subquery_quals = (Node *) copyObject(quals);
+ }
+ bms_free(varnos);
+ }
+ }
+
+ Assert(rt_sub_ids != NULL);
+ bms_free(rt_sub_ids);
+
+ /*
+ * TODO
+ * re-check all attributes
+ */
+ subquery->commandType = CMD_SELECT;
+ subquery->querySource = QSRC_INSTEAD_RULE;
+ subquery->utilityStmt = NULL;
+ subquery->resultRelation = 0;
+ subquery->intoClause = NULL;
+ subquery->hasAggs = false;
+ subquery->hasWindowFuncs = false;
+ subquery->hasSubLinks = false;
+ subquery->hasDistinctOn = false;
+ subquery->hasRecursive = false;
+ subquery->hasModifyingCTE = false;
+ subquery->hasForUpdate = false;
+ subquery->returningList = NIL;
+ subquery->groupClause = NIL;
+ subquery->havingQual = NULL;
+ subquery->windowClause = NIL;
+ subquery->distinctClause = NIL;
+ subquery->sortClause = NIL;
+ subquery->limitOffset = NULL;
+ subquery->limitCount = NULL;
+ subquery->rowMarks = NULL;
+ subquery->setOperations = NULL;
+ subquery->constraintDeps = NULL;
+
+ subquery->rtable = rt_sub;
+ subquery->jointree = makeNode(FromExpr);
+ subquery->jointree->fromlist = subquery_from;
+ subquery->jointree->quals = subquery_quals;
+
+ subquery->targetList = lappend(NIL, subquery_te);
+
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = subquery;
+ rte->relid = InvalidOid;
+ rte->alias = NULL;
+ rte->inFromCl = false;
+ }
+}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
