Re: SubtransControlLock and performance problems

Hi

po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
napsal:

> On Mon, 2020-02-17 at 15:03 +0000, Lars Aksel Opsahl wrote:
> > I have tested in branch (
> https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func
> )
> > where I use only have functions and no procedures and I still have the
> same problem with subtransaction locks.
> >
> > Can I based on this assume that the problem is only related to
> exceptions ?
>
> No, it is related to BEGIN ... EXCEPTION ... END blocks, no matter if
> an exception is thrown or not.
>
> As soon as execution enters such a block, a subtransaction is started.
>
> > Does this mean that if have 32 threads running in parallel and I get 2
> exceptions in each thread I have reached a state where I will get
> contention ?
>
> No, it means that if you enter a block with an EXCEPTION clause more
> than 64 times in a single transaction, performance will drop.
>
> > Is it any way increase from 64 to a much higher level, when compiling
> the code ?
>
> Yes, you can increase PGPROC_MAX_CACHED_SUBXIDS in
> src/include/storage/proc.h
>
> > Basically what I do here is that I catch exceptions when get them and
> tries to solve the problem in a alternative way.
>
> Either use shorter transactions, or start fewer subtransactions.
>
> Yours,
> Laurenz Albe
>

it is interesting topic, but I don't see it in my example

CREATE OR REPLACE FUNCTION public.fx(integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
for i in 1..$1 loop
begin
--raise notice 'xx';
exception when others then
raise notice 'yyy';
end;
end loop;
end;
$function$

the execution time is without performance drops.

Is there some prerequisite to see performance problems?

Pavel

--
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>