Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Jul 29, 2024 at 12:24 AM Tom Lane 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 sing

Re: pg_upgrade failing for 200+ million Large Objects

I wrote: > Alexander Korotkov writes: >> 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 :-) > Hmm ... that's not a completely silly idea. Let's keep it in > the back pocket in case we c

Re: pg_upgrade failing for 200+ million Large Objects

Alexander Korotkov writes: > 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 :-) Hmm ... that's not a completely silly idea. Let's keep it in the back pocket in case we can't easily reduce

Re: pg_upgrade failing for 200+ million Large Objects

On Sat, Jul 27, 2024 at 2:06 AM Tom Lane wrote: > Alexander Korotkov writes: > > On Sat, Jul 27, 2024 at 1:37 AM Tom Lane wrote: > >> 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

Re: pg_upgrade failing for 200+ million Large Objects

Alexander Korotkov writes: > On Sat, Jul 27, 2024 at 1:37 AM Tom Lane wrote: >> 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 qu

Re: pg_upgrade failing for 200+ million Large Objects

On Sat, Jul 27, 2024 at 1:37 AM Tom Lane wrote: > Justin Pryzby writes: > > 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: >

Re: pg_upgrade failing for 200+ million Large Objects

Justin Pryzby writes: > 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 RANG

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, Jul 26, 2024 at 11:36 PM Justin Pryzby 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, A

Re: pg_upgrade failing for 200+ million Large Objects

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 cas

Re: pg_upgrade failing for 200+ million Large Objects

Alexander Korotkov writes: > On Wed, Jul 24, 2024 at 5:18 PM Justin Pryzby wrote: >> We'd surely prefer that the transaction size be configurable. > I think we can add an option to pg_upgrade. But I wonder if there is > something else we can do. Yeah, I'm not enamored of adding a command-line

Re: pg_upgrade failing for 200+ million Large Objects

Hi, Justin! Thank you for sharing this. On Wed, Jul 24, 2024 at 5:18 PM Justin Pryzby wrote: > On Mon, Apr 01, 2024 at 03:28:26PM -0400, Tom Lane wrote: > > Nathan Bossart writes: > > > The one design point that worries me a little is the non-configurability > > > of > > > --transaction-size i

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Apr 01, 2024 at 03:28:26PM -0400, Tom Lane wrote: > Nathan Bossart writes: > > The one design point that worries me a little is the non-configurability of > > --transaction-size in pg_upgrade. I think it's fine to default it to 1,000 > > or something, but given how often I've had to fiddl

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Apr 01, 2024 at 03:28:26PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> The one design point that worries me a little is the non-configurability of >> --transaction-size in pg_upgrade. I think it's fine to default it to 1,000 >> or something, but given how often I've had to fiddle w

Re: pg_upgrade failing for 200+ million Large Objects

Nathan Bossart writes: > Sorry for taking so long to get back to this one. Overall, I think the > code is in decent shape. Thanks for looking at it! > The one design point that worries me a little is the non-configurability of > --transaction-size in pg_upgrade. I think it's fine to default it

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 27, 2024 at 10:08:26AM -0500, Nathan Bossart wrote: > On Wed, Mar 27, 2024 at 10:54:05AM -0400, Tom Lane wrote: >> Michael Banck writes: >>> What is the status of this? In the commitfest, this patch is marked as >>> "Needs Review" with Nathan as reviewer - Nathan, were you going to tak

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 27, 2024 at 10:54:05AM -0400, Tom Lane wrote: > Michael Banck writes: >> What is the status of this? In the commitfest, this patch is marked as >> "Needs Review" with Nathan as reviewer - Nathan, were you going to take >> another look at this or was your mail from January 12th a full r

Re: pg_upgrade failing for 200+ million Large Objects

Michael Banck writes: > What is the status of this? In the commitfest, this patch is marked as > "Needs Review" with Nathan as reviewer - Nathan, were you going to take > another look at this or was your mail from January 12th a full review? In my mind the ball is in Nathan's court. I feel it's

Re: pg_upgrade failing for 200+ million Large Objects

Hi, On Wed, Mar 27, 2024 at 10:53:51AM +0100, Laurenz Albe wrote: > On Wed, 2024-03-27 at 10:20 +0100, Michael Banck wrote: > > Also, is there a chance this is going to be back-patched? I guess it > > would be enough if the ugprade target is v17 so it is less of a concern, > > but it would be nice

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, 2024-03-27 at 10:20 +0100, Michael Banck wrote: > Also, is there a chance this is going to be back-patched? I guess it > would be enough if the ugprade target is v17 so it is less of a concern, > but it would be nice if people with millions of large objects are not > stuck until they are re

Re: pg_upgrade failing for 200+ million Large Objects

Hi, On Sat, Mar 16, 2024 at 06:46:15PM -0400, Tom Lane wrote: > Laurenz Albe writes: > > On Fri, 2024-03-15 at 19:18 -0400, Tom Lane wrote: > >> This patch seems to have stalled out again. In hopes of getting it > >> over the finish line, I've done a bit more work to address the two > >> loose e

Re: pg_upgrade failing for 200+ million Large Objects

On Sat, 2024-03-16 at 18:46 -0400, Tom Lane wrote: > > Without the patch: > > Runtime: 74.5 minutes > > > With the patch: > > Runtime: 70 minutes > > Hm, I'd have hoped for a bit more runtime improvement. I did a second run with the patch, and that finished in 66 minutes, so there is some jitter

Re: pg_upgrade failing for 200+ million Large Objects

Laurenz Albe writes: > On Fri, 2024-03-15 at 19:18 -0400, Tom Lane wrote: >> This patch seems to have stalled out again. In hopes of getting it >> over the finish line, I've done a bit more work to address the two >> loose ends I felt were probably essential to deal with: > Applies and builds fi

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, 2024-03-15 at 19:18 -0400, Tom Lane wrote: > This patch seems to have stalled out again. In hopes of getting it > over the finish line, I've done a bit more work to address the two > loose ends I felt were probably essential to deal with: Applies and builds fine. I didn't scrutinize the

Re: pg_upgrade failing for 200+ million Large Objects

This patch seems to have stalled out again. In hopes of getting it over the finish line, I've done a bit more work to address the two loose ends I felt were probably essential to deal with: * Duplicative blob ACLs are now merged into a single TOC entry (per metadata group) with the GRANT/REVOKE c

Re: pg_upgrade failing for 200+ million Large Objects

vignesh C writes: > CFBot shows that the patch does not apply anymore as in [1]: > === Applying patches on top of PostgreSQL commit ID > 46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92 === > === applying patch ./v9-005-parallel_pg_restore.patch > patching file src/bin/pg_upgrade/pg_upgrade.c > Hunk #3 FA

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, 2 Jan 2024 at 23:03, Kumar, Sachin wrote: > > > On 11/12/2023, 01:43, "Tom Lane" > > wrote: > > > I had initially supposed that in a parallel restore we could > > have child workers also commit after every N TOC items, but was > > soon disabused of that idea. Af

Re: pg_upgrade failing for 200+ million Large Objects

Nathan Bossart writes: > On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: >> +char *cmdEnd = psprintf(" OWNER TO %s", >> fmtId(te->owner)); >> + >> +IssueCommandPerBlob(AH, te, "ALTER LARGE OBJECT ", >> cmdEnd); > This is just a nitpick, bu

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, Jan 12, 2024 at 04:42:27PM -0600, Nathan Bossart wrote: > On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: >> +char *cmdEnd = psprintf(" OWNER TO %s", >> fmtId(te->owner)); >> + >> +IssueCommandPerBlob(AH, te, "ALTER LARGE OBJECT ", >

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, Jan 05, 2024 at 03:02:34PM -0500, Tom Lane wrote: > On further reflection, there is a very good reason why it's done like > that. Because pg_upgrade is doing schema-only dump and restore, > there's next to no opportunity for parallelism within either pg_dump > or pg_restore. There's no da

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: > * I did not invent a switch to control the batching of blobs; it's > just hard-wired at 1000 blobs per group here. Probably we need some > user knob for that, but I'm unsure if we want to expose a count or > just a boolean for one vs more

Re: pg_upgrade failing for 200+ million Large Objects

I wrote: > "Kumar, Sachin" writes: >> I was not able to find email thread which details why we are not using >> parallel pg_restore for pg_upgrade. > Well, it's pretty obvious isn't it? The parallelism is being applied > at the per-database level instead. On further reflection, there is a very

Re: pg_upgrade failing for 200+ million Large Objects

"Kumar, Sachin" writes: >> On 11/12/2023, 01:43, "Tom Lane" > > wrote: >> ... Maybe that >> could be improved in future, but it seems like it'd add a >> lot more complexity, and it wouldn't make life any better for >> pg_upgrade (which doesn't use parallel pg_restore, an

Re: pg_upgrade failing for 200+ million Large Objects

> On 11/12/2023, 01:43, "Tom Lane" > wrote: > I had initially supposed that in a parallel restore we could > have child workers also commit after every N TOC items, but was > soon disabused of that idea. After a worker processes a TOC > item, any dependent items (such a

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, 28 Dec 2023 at 01:48, Tom Lane wrote: > Robins Tharakan writes: > > Applying all 4 patches, I also see good performance improvement. > > With more Large Objects, although pg_dump improved significantly, > > pg_restore is now comfortably an order of magnitude faster. > > Yeah. The key th

Re: pg_upgrade failing for 200+ million Large Objects

Robins Tharakan writes: > Applying all 4 patches, I also see good performance improvement. > With more Large Objects, although pg_dump improved significantly, > pg_restore is now comfortably an order of magnitude faster. Yeah. The key thing here is that pg_dump can only parallelize the data tran

Re: pg_upgrade failing for 200+ million Large Objects

Nathan Bossart writes: > Wow, thanks for putting together these patches. I intend to help review, Thanks! > but I'm not sure I'll find much time to do so before the new year. There's no urgency, surely. If we can get these in during the January CF, I'll be happy. rega

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: > I have spent some more effort in this area and developed a patch > series that I think addresses all of the performance issues that > we've discussed in this thread, both for pg_upgrade and more > general use of pg_dump/pg_restore. Concre

Re: pg_upgrade failing for 200+ million Large Objects

I have spent some more effort in this area and developed a patch series that I think addresses all of the performance issues that we've discussed in this thread, both for pg_upgrade and more general use of pg_dump/pg_restore. Concretely, it absorbs the pg_restore --transaction-size switch that I p

Re: pg_upgrade failing for 200+ million Large Objects

I spent some time looking at the v7 patch. I can't help feeling that this is going off in the wrong direction, primarily for these reasons: * It focuses only on cutting the number of transactions needed to restore a large number of blobs (large objects). Certainly that's a pain point, but it's n

Re: pg_upgrade failing for 200+ million Large Objects

> I have updated the patch to use heuristic, During pg_upgrade we count > Large objects per database. During pg_restore execution if db large_objects > count is greater than LARGE_OBJECTS_THRESOLD (1k) we will use > --restore-blob-batch-size. I think both SECTION_DATA and SECTION_POST_DATA can

Re: pg_upgrade failing for 200+ million Large Objects

> "Tom Lane" mailto:t...@sss.pgh.pa.us>> wrote: > FWIW, I agree with Jacob's concern about it being a bad idea to let > users of pg_upgrade pass down arbitrary options to pg_dump/pg_restore. > I think we'd regret going there, because it'd hugely expand the set > of cases pg_upgrade has to deal

Re: pg_upgrade failing for 200+ million Large Objects

> On 09/11/2023, 18:41, "Tom Lane" > wrote: > Um ... you didn't attach the patch? Sorry , patch attached Regards Sachin pg_upgrade_improvements_v6.diff Description: pg_upgrade_improvements_v6.diff

Re: pg_upgrade failing for 200+ million Large Objects

Hi, On November 9, 2023 10:41:01 AM PST, Tom Lane wrote: >Also, pg_upgrade is often invoked indirectly via scripts, so I do >not especially buy the idea that we're going to get useful control >input from some human somewhere. I think we'd be better off to >assume that pg_upgrade is on its own t

Re: pg_upgrade failing for 200+ million Large Objects

[ Jacob's email address updated ] "Kumar, Sachin" writes: > Hi Everyone , I want to continue this thread , I have rebased the patch to > latest > master and fixed an issue when pg_restore prints to file. Um ... you didn't attach the patch? FWIW, I agree with Jacob's concern about it being a ba

Re: pg_upgrade failing for 200+ million Large Objects

 Hi Everyone , I want to continue this thread , I have rebased the patch to latest master and fixed an issue when pg_restore prints to file. ` ╰─$ pg_restore dump_small.custom --restore-blob-batch-size=2 --file=a -- -- End BLOB restore batch -- COMMIT; ` > On 09/11/2023, 17:05, "Jacob Cham

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, Sep 08, 2022 at 04:34:07PM -0700, Nathan Bossart wrote: > On Thu, Sep 08, 2022 at 04:29:10PM -0700, Jacob Champion wrote: >> To clarify, I agree that pg_dump should contain the core fix. What I'm >> questioning is the addition of --dump-options to make use of that fix >> from pg_upgrade, si

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, Sep 08, 2022 at 04:29:10PM -0700, Jacob Champion wrote: > On Thu, Sep 8, 2022 at 4:18 PM Nathan Bossart > wrote: >> IIUC the main benefit of this approach is that it isn't dependent on >> binary-upgrade mode, which seems to be a goal based on the discussion >> upthread [0]. > > To clarif

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, Sep 8, 2022 at 4:18 PM Nathan Bossart wrote: > IIUC the main benefit of this approach is that it isn't dependent on > binary-upgrade mode, which seems to be a goal based on the discussion > upthread [0]. To clarify, I agree that pg_dump should contain the core fix. What I'm questioning is

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Sep 07, 2022 at 02:42:05PM -0700, Jacob Champion wrote: > Just to clarify, was Justin's statement upthread (that the XID problem > is fixed) correct? And is this patch just trying to improve the > remaining memory and lock usage problems? I think "fixed" might not be totally accurate, but

Re: pg_upgrade failing for 200+ million Large Objects

On 8/24/22 17:32, Nathan Bossart wrote: > I'd like to revive this thread, so I've created a commitfest entry [0] and > attached a hastily rebased patch that compiles and passes the tests. I am > aiming to spend some more time on this in the near future. Just to clarify, was Justin's statement upt

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 24, 2021 at 12:05:27PM -0400, Jan Wieck wrote: > On 3/24/21 12:04 PM, Jan Wieck wrote: >> In any case I changed the options so that they behave the same way, the >> existing -o and -O (for old/new postmaster options) work. I don't think >> it would be wise to have option forwarding work

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 24, 2021 at 12:05:27PM -0400, Jan Wieck wrote: > On 3/24/21 12:04 PM, Jan Wieck wrote: > > In any case I changed the options so that they behave the same way, the > > existing -o and -O (for old/new postmaster options) work. I don't think > > it would be wise to have option forwarding w

Re: pg_upgrade failing for 200+ million Large Objects

On 3/24/21 12:04 PM, Jan Wieck wrote: In any case I changed the options so that they behave the same way, the existing -o and -O (for old/new postmaster options) work. I don't think it would be wise to have option forwarding work differently between options for postmaster and options for pg_dump/

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 4:55 PM, Tom Lane wrote: Jan Wieck writes: Have we even reached a consensus yet on that doing it the way, my patch is proposing, is the right way to go? Like that emitting BLOB TOC entries into SECTION_DATA when in binary upgrade mode is a good thing? Or that bunching all the SQL s

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > Have we even reached a consensus yet on that doing it the way, my patch > is proposing, is the right way to go? Like that emitting BLOB TOC > entries into SECTION_DATA when in binary upgrade mode is a good thing? > Or that bunching all the SQL statements for creating the blo

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 3:35 PM, Tom Lane wrote: Jan Wieck writes: The problem here is that pg_upgrade itself is invoking a shell again. It is not assembling an array of arguments to pass into exec*(). I'd be a happy camper if it did the latter. But as things are we'd have to add full shell escapeing for

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > The problem here is that pg_upgrade itself is invoking a shell again. It > is not assembling an array of arguments to pass into exec*(). I'd be a > happy camper if it did the latter. But as things are we'd have to add > full shell escapeing for arbitrary strings. Surely we

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:59 PM, Tom Lane wrote: Jan Wieck writes: On 3/23/21 2:35 PM, Tom Lane wrote: If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the double quotes and suddenly --bar is a separate option to pg_upgrade itself, not pa

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > On 3/23/21 2:35 PM, Tom Lane wrote: >> If you're passing multiple options, that is >> --pg-dump-options "--foo=x --bar=y" >> it seems just horribly fragile. Lose the double quotes and suddenly >> --bar is a separate option to pg_upgrade itself, not part of the argument >> for

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:35 PM, Tom Lane wrote: Jan Wieck writes: So the question remains, how do we name this? --pg-dump-options "" --pg-restore-options "" If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the dou

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > So the question remains, how do we name this? > --pg-dump-options "" > --pg-restore-options "" If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the double quotes and suddenly --bar is a s

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, Mar 23, 2021 at 02:23:03PM -0400, Jan Wieck wrote: > On 3/23/21 2:06 PM, Bruce Momjian wrote: > > We have the postmaster which can pass arbitrary arguments to postgres > > processes using -o. > > Right, and -o is already taken in pg_upgrade for sending options to the old > postmaster. > >

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:06 PM, Bruce Momjian wrote: We have the postmaster which can pass arbitrary arguments to postgres processes using -o. Right, and -o is already taken in pg_upgrade for sending options to the old postmaster. What we are looking for are options for sending options to pg_dump and p

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, Mar 23, 2021 at 01:25:15PM -0400, Jan Wieck wrote: > On 3/23/21 10:56 AM, Bruce Momjian wrote: > > Would it be better to allow pg_upgrade to pass arbitrary arguments to > > pg_restore, instead of just these specific ones? > > > > That would mean arbitrary parameters to pg_dump as well as

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 10:56 AM, Bruce Momjian wrote: On Tue, Mar 23, 2021 at 08:51:32AM -0400, Jan Wieck wrote: On 3/22/21 7:18 PM, Jan Wieck wrote: > On 3/22/21 5:36 PM, Zhihong Yu wrote: > > Hi, > > > > w.r.t. pg_upgrade_improvements.v2.diff. > > > > +       blobBatchCount = 0; > > +       blobInX

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, Mar 23, 2021 at 08:51:32AM -0400, Jan Wieck wrote: > On 3/22/21 7:18 PM, Jan Wieck wrote: > > On 3/22/21 5:36 PM, Zhihong Yu wrote: > > > Hi, > > > > > > w.r.t. pg_upgrade_improvements.v2.diff. > > > > > > +       blobBatchCount = 0; > > > +       blobInXact = false; > > > > > > The

Re: pg_upgrade failing for 200+ million Large Objects

On 3/22/21 7:18 PM, Jan Wieck wrote: On 3/22/21 5:36 PM, Zhihong Yu wrote: Hi, w.r.t. pg_upgrade_improvements.v2.diff. +       blobBatchCount = 0; +       blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. You are right.

Re: pg_upgrade failing for 200+ million Large Objects

On 3/22/21 5:36 PM, Zhihong Yu wrote: Hi, w.r.t. pg_upgrade_improvements.v2.diff. +       blobBatchCount = 0; +       blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. You are right. I will fix that. Thanks, Jan -- Jan

Re: pg_upgrade failing for 200+ million Large Objects

> > Hi, > w.r.t. pg_upgrade_improvements.v2.diff. + blobBatchCount = 0; + blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. Cheers

Re: pg_upgrade failing for 200+ million Large Objects

On 3/21/21 12:56 PM, Jan Wieck wrote: > On 3/21/21 7:47 AM, Andrew Dunstan wrote: >> One possible (probable?) source is the JDBC driver, which currently >> treats all Blobs (and Clobs, for that matter) as LOs. I'm working on >> improving that some: > >

Re: Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)

Jan Wieck writes: > On 3/20/21 12:39 AM, Jan Wieck wrote: >> On the way pg_upgrade also mangles the pg_database.datdba >> (all databases are owned by postgres after an upgrade; will submit a >> separate patch for that as I consider that a bug by itself). > Patch attached. Hmm, doesn't this lose

Re: pg_upgrade failing for 200+ million Large Objects

On 3/21/21 7:47 AM, Andrew Dunstan wrote: One possible (probable?) source is the JDBC driver, which currently treats all Blobs (and Clobs, for that matter) as LOs. I'm working on improving that some: You mean the user is using OID columns pointing to

Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)

On 3/20/21 12:39 AM, Jan Wieck wrote: On the way pg_upgrade also mangles the pg_database.datdba (all databases are owned by postgres after an upgrade; will submit a separate patch for that as I consider that a bug by itself). Patch attached. Regards, Jan -- Jan Wieck Principle Database Engin

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 12:55 PM, Jan Wieck wrote: > On 3/20/21 11:23 AM, Tom Lane wrote: >> Jan Wieck writes: >>> All that aside, the entire approach doesn't scale. >> >> Yeah, agreed.  When we gave large objects individual ownership and ACL >> info, it was argued that pg_dump could afford to treat each one

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 11:23 AM, Tom Lane wrote: Jan Wieck writes: All that aside, the entire approach doesn't scale. Yeah, agreed. When we gave large objects individual ownership and ACL info, it was argued that pg_dump could afford to treat each one as a separate TOC entry because "you wouldn't have t

Re: pg_upgrade failing for 200+ million Large Objects

Bruce Momjian writes: > On Sat, Mar 20, 2021 at 11:23:19AM -0400, Tom Lane wrote: >> Of course, that just reduces the memory consumption on the client >> side; it does nothing for the locks. Can we get away with releasing the >> lock immediately after doing an ALTER OWNER or GRANT/REVOKE on a blo

Re: pg_upgrade failing for 200+ million Large Objects

On Sat, Mar 20, 2021 at 11:23:19AM -0400, Tom Lane wrote: > I wonder if pg_dump could improve matters cheaply by aggregating the > large objects by owner and ACL contents. That is, do > > select distinct lomowner, lomacl from pg_largeobject_metadata; > > and make just *one* BLOB TOC entry for ea

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > On 3/8/21 11:58 AM, Tom Lane wrote: >> So it seems like the path of least resistance is >> (a) make pg_upgrade use --single-transaction when calling pg_restore >> (b) document (better) how to get around too-many-locks failures. > That would first require to fix how pg_upgrade

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 12:39 AM, Jan Wieck wrote: > On 3/8/21 11:58 AM, Tom Lane wrote: >> The answer up to now has been "raise max_locks_per_transaction enough >> so you don't see the failure".  Having now consumed a little more >> caffeine, I remember that that works in pg_upgrade scenarios too, >> since t

Re: pg_upgrade failing for 200+ million Large Objects

On 3/8/21 11:58 AM, Tom Lane wrote: The answer up to now has been "raise max_locks_per_transaction enough so you don't see the failure". Having now consumed a little more caffeine, I remember that that works in pg_upgrade scenarios too, since the user can fiddle with the target cluster's postgre

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 03, 2021 at 11:36:26AM +, Tharakan, Robins wrote: > While reviewing a failed upgrade from Postgres v9.5 (to v9.6) I saw that the > instance had ~200 million (in-use) Large Objects. I was able to reproduce > this on a test instance which too fails with a similar error. If pg_upgrade

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 5:58 PM Tom Lane wrote: > > Magnus Hagander writes: > > On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: > >> It does seem that --single-transaction is a better idea than fiddling with > >> the transaction wraparound parameters, since the latter is just going to > >> put off

Re: pg_upgrade failing for 200+ million Large Objects

Magnus Hagander writes: > On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: >> It does seem that --single-transaction is a better idea than fiddling with >> the transaction wraparound parameters, since the latter is just going to >> put off the onset of trouble. However, we'd have to do something a

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: > > Robins Tharakan writes: > > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > >> Without looking, I would guess it's the schema reload using > >> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known > >> issue in pg_dump/pg

Re: pg_upgrade failing for 200+ million Large Objects

Robins Tharakan writes: > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: >> Without looking, I would guess it's the schema reload using >> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known >> issue in pg_dump/pg_restore. And if that is the case -- perhaps just >> running

Re: pg_upgrade failing for 200+ million Large Objects

Hi Magnus, On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > AFAICT at a quick check, pg_dump in binary upgrade mode emits one lo_create() and one ALTER ... OWNER TO for each large object - so with > 500M large objects that would be a billion statements, and thus a > billion xids. And witho

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 12:02 PM Tharakan, Robins wrote: > > Thanks Peter. > > The original email [1] had some more context that somehow didn't get > associated with this recent email. Apologies for any confusion. Please take a look at your email configuration -- all your emails are lacking both R

Re: pg_upgrade failing for 200+ million Large Objects

Original Message- > From: Peter Eisentraut > Sent: Monday, 8 March 2021 9:25 PM > To: Tharakan, Robins ; pgsql-hack...@postgresql.org > Subject: [EXTERNAL] [UNVERIFIED SENDER] Re: pg_upgrade failing for 200+ > million Large Objects > > CAUTION: This email originated from ou

RE: pg_upgrade failing for 200+ million Large Objects

> -Original Message- > From: Daniel Gustafsson > Sent: Monday, 8 March 2021 9:42 AM > To: Tharakan, Robins > Cc: pgsql-hack...@postgresql.org > Subject: RE: [EXTERNAL] pg_upgrade failing for 200+ million Large Objects > > CAUTION: This email originated from outside

Re: pg_upgrade failing for 200+ million Large Objects

On 07.03.21 09:43, Tharakan, Robins wrote: Attached is a proof-of-concept patch that allows Postgres to perform pg_upgrade if the instance has Millions of objects. It would be great if someone could take a look and see if this patch is in the right direction. There are some pending tasks (such a

Re: pg_upgrade failing for 200+ million Large Objects

> On 7 Mar 2021, at 09:43, Tharakan, Robins wrote: > The patch (attached): > - Applies cleanly on REL9_6_STABLE - > c7a4fc3dd001646d5938687ad59ab84545d5d043 Did you target 9.6 because that's where you want to upgrade to, or is this not a problem on HEAD? If it's still a problem on HEAD you shou