Re: Invalid optimization of VOLATILE function in WHERE clause?

Merlin Moncure wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Merlin Moncure <mmoncure(at)gmail(dot)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