Re: 2 questions about volatile attribute of pg_proc. - Mailing list pgsql-hackers

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

út 20. 4. 2021 v 4:47 odesílatel Andy Fan <zhihui.fan1213@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? 

 

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/)