On Tue, Apr 20, 2021 at 11:32 AM Pavel Stehule <pavel.steh...@gmail.com> wrote:
> > > út 20. 4. 2021 v 5:16 odesílatel Andy Fan <zhihui.fan1...@gmail.com> > napsal: > >> >> >> On Tue, Apr 20, 2021 at 10:57 AM Pavel Stehule <pavel.steh...@gmail.com> >> wrote: >> >>> >>> >>> út 20. 4. 2021 v 4:47 odesílatel Andy Fan <zhihui.fan1...@gmail.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/)
