From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>,
        Lars Aksel Opsahl <Lars.Opsahl@nibio.no>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: SubtransControlLock and performance problems
In-reply-to: 
 <CAFj8pRCLPy1U6W-+hTJMkN-+2NsJ=T8cuu0-+wMrrUXSVUETFg@mail.gmail.com>
References: 
 <HE1P189MB0266B070D1452B04A7C9F6D99D170@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
 <eec0413432096c46c96580a7776f13a6f27a9858.camel@cybertec.at>
 <HE1P189MB026658CE70A359C45AB0F64F9D160@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
 <61eeb178848bdc2e0dd6c81ce94f3ace7c1348ff.camel@cybertec.at>
 <CAFj8pRCLPy1U6W-+hTJMkN-+2NsJ=T8cuu0-+wMrrUXSVUETFg@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
	message dated "Mon, 17 Feb 2020 19:01:11 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <9907.1581963796.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 17 Feb 2020 13:23:16 -0500
Message-ID: <9908.1581963796@sss.pgh.pa.us>
Precedence: bulk

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

			regards, tom lane