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?