Re: BUG #5611: SQL Function STABLE promoting to VOLATILE - Mailing list pgsql-bugs

OK.   The documentation says "allows the optimizer to optimize . . . ."    =
But then the example guarantees the one-time-only for a index scan conditio=
n.=20=20=20

=46rom the documentation:    8.4.4 Chapter 32 and 8.2.17 Chapter 33.

   .A STABLE function cannot modify the database and is guaranteed to retur=
n the same results given the same arguments for all rows within a  single s=
tatement. This category allows the optimizer to optimize multiple calls of =
the function to a single call. In particular, it is safe to use an expressi=
on containing such a function in an index scan condition. (Since an index s=
can will evaluate the comparison value only once, not once at each row, it =
is not valid to use a VOLATILE function in an index scan condition.)


The behavior of the optimizers <=3D 8.2 certainly fit the description.   Th=
e 8.4 behavior is vastly different.=20=20


I recommend that somebody change the documentation to say, "This category a=
llows, but does not guarantee, the optimizer to optimize multiple calls . .=
 . ."   That would be more clear.    And then mention the inlining deal, if=
 you haven't already.=20=20=20=20

There remains the problem with the now() function.   A SQL function repetit=
ively calls now().   Is that what you intended?
There remains the problem with PGAdmin memory leak.

I will change my SQL functions to PL/PGSQL functions.  I am glad that there=
 is a solution.

Thank you for your help.




-----Original Message-----
From: Robert Haas [mailto:robertmhaas@gmail.com]=20
Sent: Wednesday, August 11, 2010 11:33 AM
To: Brian Ceccarelli
Cc: Tom Lane; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE

On Wed, Aug 11, 2010 at 11:01 AM, Brian Ceccarelli
<bceccarelli@net32.com> wrote:
> =A0 =A0 My complaint remains. =A0That inlined function f_return_ver_id_4(=
) is a STABLE function, inlined or not. =A0Postgres now calls it multiple t=
imes during the transaction, even though the arguments to f_return_ver_id_4=
() have not changed.
>
> =A0 =A0 STABLE no longer means STABLE. =A0This behavior is killing my per=
formance. =A0I am getting 500% to 30000% increase in latency.

We've never guaranteed that, and almost certainly never will.  Marking
a function STABLE means that the planner is *allowed to assume* that
the results won't change for a given set of arguments, not that it is
*required to prevent* it from being called multiple times with the
same set of arguments.

You can certainly prevent the function from being inlined, though
(perhaps, by writing it in PL/pgsql).

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company