Re: Stable function being evaluated more than once in a single

Here is updated documentation for STABLE. I just changed a few words
for clarification.

---------------------------------------------------------------------------

Jim C. Nasby wrote:
> Adding -docs...
>
> On Fri, Jan 13, 2006 at 07:27:28PM -0500, Tom Lane wrote:
> > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > Is the issue that the optimizer won't combine two function calls (ie:
> > > SELECT foo(..) ... WHERE foo(..)), or is it that sometimes it won't make
> > > the optimization (maybe depending on the query plan, for example)?
> >
> > What the STABLE category actually does is give the planner permission to
> > use the function within an indexscan qualification, eg,
> > WHERE indexed_column = f(42)
> > Since an indexscan involves evaluating the comparison expression just
> > once and using its value to search the index, this would be incorrect
> > if the expression's value might change from row to row. (For VOLATILE
> > functions, we assume that the correct behavior is the naive SQL
> > semantics of actually computing the WHERE clause at each candidate row.)
> >
> > There is no function cache and no checking for duplicate expressions.
> > I think we do check for duplicate aggregate expressions, but not
> > anything else.
>
> In that case I'd say that the sSTABLE section of 32.6 should be changed
> to read:
>
> A STABLE function cannot modify the database and is guaranteed to
> return the same results given the same arguments for all calls within a
> single surrounding query. This category gives the planner permission to
> use the function within an indexscan qualification. (Since an indexscan
> involves evaluating the comparison expression just once and using its
> value to search the index, this would be incorrect if the expression's
> value might change from row to row.) There is no function cache and no
> checking for duplicate expressions.
>
> I can provide a patch to that effect if it's easier...
>
> On a related note, would it be difficult to recognize multiple calls of
> the same function in one query? ISTM that would be a win for all but the
> most trivial of functions...
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073