Re: SubtransControlLock and performance problems

[Pavel Stehule]

po 17. 2. 2020 v 19:23 odesílatel Tom Lane <t...@sss.pgh.pa.us> napsal:

> Pavel Stehule <pavel.steh...@gmail.com> writes:
> > po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz.a...@cybertec.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
>