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:robertmh...@gmail.com] 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 <bceccare...@net32.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
