Re: Invalid optimization of VOLATILE function in WHERE clause?

Merlin Moncure wrote:

Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Merlin Moncure <mmoncure@gmail.com> wrote:

Hm, I bet it's possible (although probably not easy) to deduce
volatility from the function body...maybe through the validator.
If you could do that (perhaps warning in cases where you can't),
then the performance regression-inducing-argument (which I agree
with) becomes greatly ameliorated.

For about the last 10 years the Wisconsin Courts have been parsing
SQL code to generate Java query classes, including "stored
procedures", and determining information like this. For example,
we set a readOnly property for the query class by examining the
statements in the procedure and the readOnly status of each called
procedure. It wasn't that hard for us, and I'm not sure what would
make much it harder in PostgreSQL, if we can do it where a parse
tree for the function is handy.

hm, what do you do about 'after the fact' changes to things the
procedure body is pointing to? what would the server have to do?

We did a regeneration of the whole set near the end of each release
cycle, and that or smaller changes as needed during the release
cycle. Of course, we didn't have any equivalent of pg_depend.

-Kevin