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

> On Jun 8, 2022, at 2:42 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
>
> Thinking of plpgsql here, we already run the raw parser on all sql
> when the function is defined. We could somehow check whether the
> raw_parser found any non-schema-qualified references. This looks like
> it would be awkward but doable. That would allow users to write
> non-search_path-dependent code and if postgres doesn't warn they would
> know they've done it properly. It would still put quite a burden on
> users, especially when it comes to operators...
>
> Or alternatively we could offer lexical scoping so that all objects
> are looked up at parse time and the fully qualified reference is
> stored instead of the non-qualified reference. That would be more
> similar to how views and other object references are handled.

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;
$$
language sql
immutable
parallel safe
-- set search_path to 'pg_catalog'
;

Uncommenting that last bit wouldn't make it much better.

Isn't the more general approach to look for non-immutable (or non-stable) operations, with object resolution just one type of non-immutable operation? Perhaps raise an error when you can prove the given function's provolatile marking is wrong, and a warning when you cannot prove the marking is correct? That would tend to give warnings for polymorphic functions that use functions or operators over the polymorphic types, or which use dynamic sql, but maybe that's ok. Those functions probably deserve closer scrutiny anyway.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company