On 8/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Jaime Casanova" <[EMAIL PROTECTED]> writes:
>>> There's been some talk about prohibiting flattening if there are any
>>> volatile functions in the subselect's targetlist, but nothing's been
>>> done about that.
Advertising
> BTW, can you think in a good name for a GUC for this?
I'm not in favor of a GUC for this; we should either do it or not.
me neither, the idea came because seems there wasn't enough
consensus... my opinion always was we have to return right results and
then think on performance...
if someone cares, this is the patch i use for avoiding pulling up of
subqueries containing volatile functions (at least it has worked for
me :)...
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Index: src/backend/optimizer/path/allpaths.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.151
diff -c -p -r1.151 allpaths.c
*** src/backend/optimizer/path/allpaths.c 10 Aug 2006 02:36:28 -0000 1.151
--- src/backend/optimizer/path/allpaths.c 14 Aug 2006 02:19:08 -0000
*************** make_one_rel_by_joins(PlannerInfo *root,
*** 737,742 ****
--- 737,745 ----
* component queries to see if any of them have different output types;
* differentTypes[k] is set true if column k has different type in any
* component.
+ *
+ * 4. If the subquery has any volatile functions may not be safe to push
+ * down any quals.
*/
static bool
subquery_is_pushdown_safe(Query *subquery, Query *topquery,
*************** subquery_is_pushdown_safe(Query *subquer
*** 769,774 ****
--- 772,782 ----
topop->colTypes,
differentTypes);
}
+
+ /* Check point 4 */
+ if (contain_volatile_functions(subquery->targetList))
+ return false;
+
return true;
}
Index: src/backend/optimizer/prep/prepjointree.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v
retrieving revision 1.42
diff -c -p -r1.42 prepjointree.c
*** src/backend/optimizer/prep/prepjointree.c 12 Aug 2006 20:05:55 -0000 1.42
--- src/backend/optimizer/prep/prepjointree.c 14 Aug 2006 02:19:11 -0000
*************** is_simple_subquery(Query *subquery)
*** 657,662 ****
--- 657,669 ----
return false;
/*
+ * Don't pull up a subquery that has any volatile functions in its
+ * targetlist.
+ */
+ if (contain_volatile_functions((Node *) subquery->targetList))
+ return false;
+
+ /*
* Hack: don't try to pull up a subquery with an empty jointree.
* query_planner() will correctly generate a Result plan for a jointree
* that's totally empty, but I don't think the right things happen if an
Index: src/include/optimizer/prep.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/optimizer/prep.h,v
retrieving revision 1.56
diff -c -p -r1.56 prep.h
*** src/include/optimizer/prep.h 5 Mar 2006 15:58:57 -0000 1.56
--- src/include/optimizer/prep.h 14 Aug 2006 02:19:17 -0000
***************
*** 17,23 ****
#include "nodes/plannodes.h"
#include "nodes/relation.h"
-
/*
* prototypes for prepjointree.c
*/
--- 17,22 ----
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
