Horton
Menu

Re: pg_upgrade failing for 200+ million Large Objects

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
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
Date: 2024-07-31T13:39:19Z
Lists: pgsql-hackers 
On Mon, Jul 29, 2024 at 12:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So I'm forced to the conclusion that we'd better make the transaction
> size adaptive as per Alexander's suggestion.
>
> In addition to the patches attached, I experimented with making
> dumpTableSchema fold all the ALTER TABLE commands for a single table
> into one command.  That's do-able without too much effort, but I'm now
> convinced that we shouldn't.  It would break the semicolon-counting
> hack for detecting that tables like these involve extra work.
> I'm also not very confident that the backend won't have trouble with
> ALTER TABLE commands containing hundreds of subcommands.  That's
> something we ought to work on probably, but it's not a project that
> I want to condition v17 pg_upgrade's stability on.
>
> Anyway, proposed patches attached.  0001 is some trivial cleanup
> that I noticed while working on the failed single-ALTER-TABLE idea.
> 0002 merges the catalog-UPDATE commands that dumpTableSchema issues,
> and 0003 is Alexander's suggestion.

Nice to see you picked up my idea.  I took a look over the patchset.
Looks good to me.

------
Regards,
Alexander Korotkov
Supabase

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Count individual SQL commands in pg_restore's --transaction-size mode.

  2. Reduce number of commands dumpTableSchema emits for binary upgrade.

  3. Invent --transaction-size option for pg_restore.

  4. Rearrange pg_dump's handling of large objects for better efficiency.

  5. Add temporal PRIMARY KEY and UNIQUE constraints

  6. Fix typo and case in messages