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