Re: 2 questions about volatile attribute of pg_proc.

ne 18. 4. 2021 v 17:06 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
napsal:

> Hi:
>
> 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?
>

plpgsql_check does this check - the performance check check if function can
be marked as stable

https://github.com/okbob/plpgsql_check

I don't think so this can be done automatically - plpgsql does not check
objects inside in registration time. You can use objects and functions that
don't exist in CREATE FUNCTION time. And you need to know this info before
optimization time. So if we implement this check automatically, then
planning time can be increased a lot.

Regards

Pavel

> The second question "It is v for “volatile” functions, whose results might
> change at any time.
> (Use v also for functions with side-effects, so that calls to them cannot
> get optimized away.)"
> I think they are different semantics. One of the results is volatile
> functions can't be removed
> by remove_unused_subquery_output even if it doesn't have side effects. for
> example:
> select b from (select an_expensive_random(), b from t); Is it by design
> on purpose?
>
>
> --
> Best Regards
> Andy Fan (https://www.aliyun.com/)
>