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

út 20. 4. 2021 v 5:16 odesílatel Andy Fan <zhihui.fan1213@gmail.com> napsal:

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? 

you cannot do it - with this you introduce strong dependency on nested objects

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

 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.

 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.