Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

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

From the documentation: 8.4.4 Chapter 32 and 8.2.17 Chapter 33.

.A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan 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 <= 8.2 certainly fit the description. The 8.4 behavior is vastly different.

I recommend that somebody change the documentation to say, "This category allows, 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.

There remains the problem with the now() function. A SQL function repetitively 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(at)gmail(dot)com]
Sent: Wednesday, August 11, 2010 11:33 AM
To: Brian Ceccarelli
Cc: Tom Lane; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE

On Wed, Aug 11, 2010 at 11:01 AM, Brian Ceccarelli
<bceccarelli(at)net32(dot)com> wrote:
>     My complaint remains.  That inlined function f_return_ver_id_4() is a STABLE function, inlined or not.  Postgres now calls it multiple times during the transaction, even though the arguments to f_return_ver_id_4() have not changed.
>
>     STABLE no longer means STABLE.  This behavior is killing my performance.  I 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).

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