Re: 2 questions about volatile attribute of pg_proc. - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: 2 questions about volatile attribute of pg_proc. |
| Date | |
| Msg-id | CAFj8pRDsTFis-Ha35Uh1YNVdzpErDYOoNPLhs_Jcq9_KY_TNyQ@mail.gmail.com Whole thread Raw |
| In response to | Re: 2 questions about volatile attribute of pg_proc. (Andy Fan <zhihui.fan1213@gmail.com>) |
| List | pgsql-hackers |
you cannot do it - with this you introduce strong dependency on nested objectsWhat does the plpgsql_check do in this area? I checked the README[1], but can't findanything about it.
When you run plpgsql_check with performance warning (disabled by default), then it does check if all called functions are on the same or lower level than checked functions have. So when all called operations are stable (read only), then the function can be marked as stable - and if the function is marked as volatile, then plpgsql_check raises an warning.
until we have global temp tables, then it is blocker for usage of temporary tables.
All plpgsql expressions are SQL expressions - and anybody can call a function against a temporary table. But local temporary tables don't exist in typical CREATE FUNCTION time (registration time). Typically doesn't exist in plpgsql compile time too. Usually temporary tables are created inside executed plpgsql functions. So you cannot do any semantical (deeper) check in registration, or compile time. Just because one kind of object (temporary tables) doesn't exist. This is a difficult issue for plpgsql_check too.
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, butcan't do it in the VOLATILE_AUTO way.
You can do it. But you solve one issue, and introduce new kinds of more terrible issues (related to dependencies between database's objects). The design of plpgsql is pretty different from the design of Oracle's PL/SQL. So proposed change means total conceptual change, and you need to write a lot of new code that will provide necessary infrastructure. I am not sure if the benefit is higher than the cost. It can be usable, if plpgsql can be really compiled to some machine code - but it means ten thousands codes without significant benefits - the bottleneck inside stored procedures is SQL, and the compilation doesn't help with it.
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" flagis 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.
And DETERMINISTIC functions are IMMUTABLE on Postgres's side
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?
Unfortunately not. I have not well structured notes from work on ports from Oracle to Postgres. And these 1% cases are very very variable. People are very creative. But usually this code is almost very dirty, and not critical. In Postgres we can use LISTEN, NOTIFY, or possibility to set app_name or we can use RAISE NOTICE.
--Best RegardsAndy Fan (https://www.aliyun.com/)
pgsql-hackers by date: