Re: 2 questions about volatile attribute of pg_proc.

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

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, but
> can'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" 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.
>

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.

> [1] https://github.com/okbob/plpgsql_check/blob/master/README.md#features
>
>
> --
> Best Regards
> Andy Fan (https://www.aliyun.com/)
>