Re: 2 questions about volatile attribute of pg_proc.

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

>
>
> út 20. 4. 2021 v 5:16 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
> napsal:
>
>>
>>
>> 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?
>>
>
> you cannot do it - with this you introduce strong dependency on nested
> objects
>

What does the plpgsql_check do in this area? I checked the README[1], but
can't find
anything about it.

> until we have global temp tables, then it is blocker for usage of
> temporary tables.
>

Can you explain more about this?

> Can be nice if some functionality of plpgsql_check can be in core,
> because I think so it is necessary for development, but the structure and
> integration of SQL in PLpgSQL is very good (and very practical).
>
>
I'm interested in plpgsql_check. However I am still confused why we can do
it in this way, but
can't do it in the VOLATILE_AUTO way.

>
>>
>>> 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?
>>
>
> Oracle doesn't allow write operations in functions. Or didn't allow it - I
> am not sure what is possible now. So when you migrate data from Oracle, and
> if the function is not marked as DETERMINISTIC, you can safely mark it as
> STABLE.
>

You are correct. Good to know the above points.

> Elsewhere - it works 99% well. In special cases, there is some black
> magic - with fresh snapshots, and with using autonomous transactions, and
> these cases should be solved manually. Sometimes is good enough just
> removing autonomous transactions, sometimes the complete rewrite is
> necessary - or redesign functionality.
>
>
is the 1% == "special cases" ? Do you mind sharing more information about
these cases,
either document or code?

[1] https://github.com/okbob/plpgsql_check/blob/master/README.md#features

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