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> <1870579.1722033430@sss.pgh.pa.us>
 <CAPpHfdtpr2FYVYqmARZdSp=2zsndgJgmSsHFxFFNTiYRfX8uaA@mail.gmail.com> <1873872.1722035181@sss.pgh.pa.us>
In-Reply-To: <1873872.1722035181@sss.pgh.pa.us>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Sat, 27 Jul 2024 06:00:47 +0300
Message-ID: <CAPpHfdu63MK5k4qBNc4YV14MFdNq8rCsvz0x7+Z=ijbFm1y6wQ@mail.gmail.com>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>, 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/CAPpHfdu63MK5k4qBNc4YV14MFdNq8rCsvz0x7%2BZ%3DijbFm1y6wQ%40mail.gmail.com>
Precedence: bulk

On Sat, Jul 27, 2024 at 2:06=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> writes:
> > On Sat, Jul 27, 2024 at 1:37=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wr=
ote:
> >> It's fairly easy to fix things so that this example doesn't cause
> >> that to happen: we just need to issue these updates as one command
> >> not N commands per table.
>
> > I was thinking about counting actual number of queries, not TOC
> > entries for transaction number as a more universal solution.  But that
> > would require usage of psql_scan() or writing simpler alternative for
> > this particular purpose.  That looks quite annoying.  What do you
> > think?
>
> The assumption underlying what we're doing now is that the number
> of SQL commands per TOC entry is limited.  I'd prefer to fix the
> code so that that assumption is correct, at least in normal cases.
> I confess I'd not looked closely enough at the binary-upgrade support
> code to realize it wasn't correct already :-(.  If we go that way,
> we can fix this while also making pg_upgrade faster rather than
> slower.  I also expect that it'll be a lot simpler than putting
> a full SQL parser in pg_restore.

I'm good with that as soon as we're not going to meet many cases of
high number SQL commands per TOC entry.

J4F, I have an idea to count number of ';' sings and use it for
transaction size counter, since it is as upper bound estimate of
number of SQL commands :-)

------
Regards,
Alexander Korotkov
Supabase