Re: Why overhead of SPI is so large?

On 22.11.2019 10:08, Pavel Stehule wrote:
>
> I test it, and there is a problem already. We doesn't raise a
> exception, but the result is wrong
>
>
> create table foo(a int);
>
> create or replace function f1(int)
> returns void as $$
> begin
>   insert into foo values($1);
> end;
> $$ language plpgsql;
>
> create or replace function f2(int)
> returns void as $$declare r record;
> begin
>   perform f1(); for r in select * from foo loop raise notice '%', r;
> end loop;
> end;
> $$ language plpgsql immutable; -- or stable with same behave
>
> So current state is:
>
> a) we allow to call volatile functions from nonvolatile (stable,
> immutable) that really does write
> b) but this change is not visible in parent nonvolatile functions. Is
> visible only in volatile functions.
>
> Is it expected behave?

I think that in theory it is definitely not correct to call volatile
function from non-volatile.
But there are two questions:
1. Are we able to check it? Please taken in account that:
 - at the moment of "create function f2()"  called function f1() may
not yet be defined
 - instead of perform f1() it can do "execute 'select f1()'" and it is
not possible to check it at compile time.
2. Is it responsibility of programmer to correctly specify function
properties or it should be done by compiler?
  If we follow YWIYGI rule, then your definition of f2() is not correct
and that it is why you will get wrong result in this case.
  If we what to completely rely on compiler, then... we do not not
volatile/immutable/stable/qualifiers at all! Compiler should deduce this
information itself.
  But it will be non-trivial if ever possible, take in account 1)

In principle it is possible to add checks which will produce warning in
case of calling volatile function or executing dynamic SQL from
non-volatile function.
If such extra checking will be considered useful, I can propose patch
doing it.
But IMHO optimizer should rely on function qualifier provided by
programmer and it is acceptable to produce wrong result if this
information is not correct.

>
> So now, there are described issues already. And the limit to just
> immutable function is not enough - these functions should be immutable
> buildin.
>
> The core of these problems is based on function's flags related to
> planner optimizations.
>
> Maybe other flags WRITE | READ | PURE can help.
>
> Now we don't know if volatile function writes to database or not -
> surely random function doesn't do this. We can implement new set of
> flags, that can reduce a overhead with snapshots.
>
> The function random() can be VOLATILE PURE - and we will know, so 
> result of this function is not stable, but this function doesn't touch
> data engine.
>
> When we don't have these flags, then the current logic is used, when
> we have these flags, then it will be used. These flags can be more strict
>
> we should not to allow any WRITE from READ function, or we should not
> allow READ from PURE functions.
>
> Notes, comments?
I think that even current model with "volatile", "immutable" and
"stable" is complex enough.
Adding more qualifiers will make it even more obscure and error-prone.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company