po 17. 2. 2020 v 19:23 odesílatel Tom Lane <tgl@xxxxxxxxxxxxx> napsal:
Pavel Stehule <pavel.stehule@xxxxxxxxx> writes:
> po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
> 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$
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
