Re: 2 questions about volatile attribute of pg_proc.

On Tue, Apr 20, 2021 at 10:57 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> út 20. 4. 2021 v 4:47 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
> napsal:
>
>>
>>
>> > - 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
>>
>>
>> I'd think this one as a blocker issue at the beginning since I have to
>> insist on
>> any new features should not cause semantic changes for existing ones.
>> Later I
>> found the new definition. As for this feature request, I think we can
>> define the
>> features like this:
>>
>> 1. We define a new attribute named VOLATILE_AUTO; The semantic is PG
>> will auto
>> detect the volatile info based on current search_path / existing
>> function. If any embedded function can't be found, we can raise an
>> error if
>> VOLATILE_AUTO is used. If people change the volatile attribute later,
>> we can:
>> a). do nothing. This can be the documented feature. or. b). Maintain
>> the
>> dependency tree between functions and if anyone is changed, other
>> functions
>> should be recalculated as well.
>>
>> 2. VOLATILE_AUTO should never be the default value. It only works when
>> people
>> requires it.
>>
>> Then what we can get from this? Thinking a user is migrating lots of UDF
>> from
>> other databases. Asking them to check/set each function's attribute might
>> be bad. However if we tell them about how VOLATILE_AUTO works, and they
>> accept it (I guess most people would accept), then the migration would be
>> pretty productive.
>>
>> I'm listening to any obvious reason to reject it.
>>
>
> a) This analyses can be very slow - PLpgSQL does lazy planning - query
> plans are planned only when are required - and this feature requires
> complete planning current function and all nested VOLATILE_AUTO functions -
> so start of function can be significantly slower
>

Actually I am thinking we can do this when we compile the function, which
means that would
happen on the "CREATE FUNCTION " stage. this would need some hacks for
sure. Does
this remove your concern?

> b) When you migrate from Oracle,then you can use the STABLE flag, and it
> will be mostly correct.
>

This was suggested in our team as well, but I don't think it is very
strict. For example:
SELECT materialize_bills_for(userId) from users; Any more proof to say
"STABLE" flag
is acceptable?

> --
>> Best Regards
>> Andy Fan (https://www.aliyun.com/)
>>
>

--
Best Regards
Andy Fan (https://www.aliyun.com/)