Re: problem with volatile functions in subselects ?

Based on how small this patch is and the demonstrated desire for this
behavior, can we consider putting this in 8.2, even though we're past
the deadline?

On Sun, Aug 13, 2006 at 10:03:30PM -0500, Jaime Casanova wrote:
> On 8/13/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >"Jaime Casanova" <systemguards(at)gmail(dot)com> 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.
> >
> >> 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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461