On Wed, 8 Jun 2022 at 19:39, Mark Dilger <mark.dil...@enterprisedb.com> wrote: > > > I like the general idea, but I'm confused why you are limiting the analysis > to search path resolution. The following is clearly 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
