Hi po 17. 2. 2020 v 17:36 odesÃlatel Laurenz Albe <laurenz.a...@cybertec.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 > > > >
