MIME-Version: 1.0
References: <!2751089.1710544721@sss.pgh.pa.us> <4a3ebf7d81bfc6dd4d545e5b27d6e8f6c32d8937.camel@cybertec.at>
 <3023817.1710629175@sss.pgh.pa.us> <6603e4e0.500a0220.a557f.4f39@mx.google.com>
 <3304322.1711551245@sss.pgh.pa.us> <20240327150826.GB3994937@nathanxps13>
 <20240401191930.GA2302032@nathanxps13> <1217588.1711999706@sss.pgh.pa.us>
 <ZqEND4ZcTDBmcv31@pryzbyj2023> <CAPpHfduUSA7c0YrTxN443SwXXCL41vmXkavYJjN8GS3Wc5Z42w@mail.gmail.com>
 <ZqQIxIsaPWO6coil@pryzbyj2023>
In-Reply-To: <ZqQIxIsaPWO6coil@pryzbyj2023>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Sat, 27 Jul 2024 00:42:23 +0300
Message-ID: <CAPpHfdsZ3L8gBggXmpDJN=TiPHmLohiszV1Bv-SQKYiJEFV8wA@mail.gmail.com>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Nathan Bossart <nathandbossart@gmail.com>, 
	Michael Banck <mbanck@gmx.net>, Laurenz Albe <laurenz.albe@cybertec.at>, 
	vignesh C <vignesh21@gmail.com>, "Kumar, Sachin" <ssetiya@amazon.com>, 
	Robins Tharakan <tharakan@gmail.com>, Jan Wieck <jan@wi3ck.info>, Bruce Momjian <bruce@momjian.us>, 
	Andrew Dunstan <andrew@dunslane.net>, Magnus Hagander <magnus@hagander.net>, 
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAPpHfdsZ3L8gBggXmpDJN%3DTiPHmLohiszV1Bv-SQKYiJEFV8wA%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 26, 2024 at 11:36=E2=80=AFPM Justin Pryzby <pryzby@telsasoft.co=
m> wrote:
> On Wed, Jul 24, 2024 at 09:17:51AM -0500, Justin Pryzby wrote:
> > With partitioning, we have a lot of tables, some of them wide (126
> > partitioned tables, 8942 childs, total 1019315 columns).
>
> On Fri, Jul 26, 2024 at 10:53:30PM +0300, Alexander Korotkov wrote:
> > It would be nice to identify such cases and check which memory contexts=
 are
> > growing and why.
>
> I reproduced the problem with this schema:
>
> SELECT format('CREATE TABLE p(i int, %s) PARTITION BY RANGE(i)', array_to=
_string(a, ', ')) FROM (SELECT array_agg(format('i%s int', i))a FROM genera=
te_series(1,999)i);
> SELECT format('CREATE TABLE t%s PARTITION OF p FOR VALUES FROM (%s)TO(%s)=
', i,i,i+1) FROM generate_series(1,999)i;
>
> This used over 4 GB of RAM.
> 3114201 pryzbyj   20   0 5924520   4.2g  32476 T   0.0  53.8   0:27.35 po=
stgres: pryzbyj postgres [local] UPDATE
>
> The large context is:
> 2024-07-26 15:22:19.280 CDT [3114201] LOG:  level: 1; CacheMemoryContext:=
 5211209088 total in 50067 blocks; 420688 free (14 chunks); 5210788400 used
>
> Note that there seemed to be no issue when I created 999 tables without
> partitioning:
>
> SELECT format('CREATE TABLE t%s(LIKE p)', i,i,i+1) FROM generate_series(1=
,999)i;

Thank you!  That was quick.
I'm looking into this.

------
Regards,
Alexander Korotkov
Supabase