Re: Why overhead of SPI is so large?

pá 22. 11. 2019 v 7:33 odesílatel Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
napsal:

> At Fri, 22 Nov 2019 06:15:25 +0100, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote in
> > čt 21. 11. 2019 v 20:44 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> >
> > > Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > > > čt 21. 11. 2019 v 10:31 odesílatel Konstantin Knizhnik <
> > > > k(dot)knizhnik(at)postgrespro(dot)ru> napsal:
> > > >> With contain_mutable_functions the patch becomes trivial.
> > >
> > > > Stable functions doesn't need own snapshot too, so it is not fully
> > > correct,
> > > > but it is on safe side.
> > >
> > > No, I doubt that. A stable function is allowed to inspect database
> state,
> > > and if it's being called by a volatile function, it has every right to
> > > expect that it'd see updates-so-far made by the volatile function.
> >
> > for this I need new snapshot?
>
> It depends on what we regard as "query" or "command" here. It seems to
> me that every line in a plpgsql function is regarded as a "query" for
> stable function, even if the function is called in another "query".
>
> In short, we need it, I think.
>

ok, then the limits just to only immutable functions is wrong

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?

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?

Pavel

This test should

>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>