Re: 2 questions about volatile attribute of pg_proc.

On Sun, 18 Apr 2021 at 11:36, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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.