Re: Why overhead of SPI is so large? - Mailing list pgsql-hackers

At Fri, 22 Nov 2019 08:08:24 +0100, Pavel Stehule <pavel.stehule@gmail.com> wrote in
> pá 22. 11. 2019 v 7:33 odesílatel Kyotaro Horiguchi <horikyota.ntt@gmail.com>
> napsal:
>
> > At Fri, 22 Nov 2019 06:15:25 +0100, Pavel Stehule <pavel.stehule@gmail.com>
> > wrote in
> > > čt 21. 11. 2019 v 20:44 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
> > >
> > > > Pavel Stehule <pavel.stehule@gmail.com> writes:
> > > > > čt 21. 11. 2019 v 10:31 odesílatel Konstantin Knizhnik <
> > > > > k.knizhnik@postgrespro.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?

I'm not sure, volatility is the total bahavior of the function,
regardless of whatever the function does internally. Even though I'm
not sure how to use volatile functions in non-volatile functions, I
don't see direct reason or how to inhibit that and I think we don't
even need to bother that. It's owe to the definier of the function.

> 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

I think such fine-grained categorization is beyond our maintainance
capability and users still much cannot follow.  I recall of an extreme
discussion that the result of immutable system functions still can
mutate beyond major version upgrade. Volatility is mere a hint and,
perhaps as Robert said somewhere, the baseline we should do is to
avoid crash for any possible configuration.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center