Re: Tightening behaviour for non-immutable behaviour in immutable functions

On Wed, 8 Jun 2022 at 19:39, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
>
>
> I like the general idea, but I'm confused why you are limiting the analysis to search path resolution.  The following
isclearly wrong, but not for that reason:
 
>
> create function public.identity () returns double precision as $$
>   select random()::integer;

Well.... I did originally think it would be necessary to consider
cases like this. (or even just cases where you call a user function
that is not immutable because of search_path dependencies).

But there are two problems:

Firstly, that would be a lot harder to implement. We don't actually do
any object lookups in plpgsql when defining plpgsql functions. So this
would be a much bigger change.

But secondly, there are a lot of cases where non-immutable functions
*are* immutable if they're used carefully. to_char() is obviously the
common example, but it's perfectly safe if you set the timezone or
other locale settings or if your format string doesn't actually depend
on any settings.

Similarly, a user function that is non-immutable only due to a
dependency on search_path *would* be safe to call from within an
immutable function if that function does set search_path. The
search_path would be inherited alleviating the problem.

Even something like random() could be safely used in an immutable
function as long as it doesn't actually change the output -- say if it
just logs diagnostic messages as a result?

Generally I think the idea is that the user *is* responsible for
writing immutable functions carefully to hide any non-deterministic
behaviour from the code they're calling. But that does raise the
question of why to focus on search_path.

I guess I'm just saying my goal isn't to *prove* the code is correct.
The user is still responsible for asserting it's correct. I just want
to detect cases where I can prove (or at least show it's likely that)
it's *not* correct.

-- 
greg