Andy Fan <zhihui.fan1213@gmail.com> writes: > We know volatile is very harmful for optimizers and it is the default > value (and safest value) if the user doesn't provide that. Asking user > to set the value is not a good experience, is it possible to auto-generate > the value for it rather than use the volatile directly for user defined > function. I > think it should be possible, we just need to scan the PlpgSQL_stmt to see > if there > is a volatile function?
Are you familiar with the halting problem? I don't see any meaningful difference here.
I think what is being suggested is akin to type checking, not solving the halting problem. Parse the function text, identify all functions it might call (without solving the equivalent of the halting problem to see if it actually does or could), and apply the most volatile value of called functions to the calling function.
That being said, there are significant difficulties, including but almost certainly not limited to:
- what happens if one modifies a called function after creating the calling function?
- EXECUTE
- a PL/PGSQL function's meaning depends on the search path in effect when it is called, unless it has a SET search_path clause or it fully qualifies all object references, so it isn't actually possible in general to determine what a function calls at definition time
If the Haskell compiler is possible then what is being requested here is conceptually possible even if there are major issues with actually doing it in the Postgres context. The halting problem is not the problem here.