Re: 2 questions about volatile attribute of pg_proc.

ú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 - and that means a lot of problems - necessity of rechecks when any
nested object is changed. There will be new problems with dependency, when
you create functions, and until we have global temp tables, then it is
blocker for usage of temporary tables. The current behavior is not perfect,
but in this direction is very practical, and I would not change it. 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).

>
>> 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. Ora2pg does it. 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.

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