Re: SubtransControlLock and performance problems

po 17. 2. 2020 v 19:23 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at
> >
> > napsal:
> >> Either use shorter transactions, or start fewer subtransactions.
>
> > 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$
>
> This example doesn't create or modify any table rows within the
> subtransactions, so (I think) we won't assign XIDs to them.
> It's consumption of subtransaction XIDs that causes the issue.
>

I tested

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

and I don't see any significant difference between numbers less than 64 and
higher

> regards, tom lane
>