From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nathan Bossart <nathandbossart@gmail.com>
cc: Hannu Krosing <hannuk@google.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Horribly slow pg_upgrade performance with many Large Objects
In-reply-to: <Z_VTucTiS01p-d6z@nathan>
References: <CAMT0RQSS-6qLH+zYsOeUbAYhop3wmQTkNmQpo5--QRDUR+qYmQ@mail.gmail.com> <Z_RO37whB1L2LbiD@nathan> <CAMT0RQRL08=Si+9V7RW7VKcbo2H7GO6ME3opkS_WHpE8qctBLg@mail.gmail.com> <Z_VE4Q4A2nNCX3Ny@nathan> <4044567.1744128814@sss.pgh.pa.us> <CAMT0RQSALxZ2DJbq0b5cy+cvmpVxkawmv7c+qTWYObubZVr+2g@mail.gmail.com> <4047312.1744130263@sss.pgh.pa.us> <Z_VTucTiS01p-d6z@nathan>
Comments: In-reply-to Nathan Bossart <nathandbossart@gmail.com>
	message dated "Tue, 08 Apr 2025 11:50:01 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4050386.1744132029.1@sss.pgh.pa.us>
Date: Tue, 08 Apr 2025 13:07:09 -0400
Message-ID: <4050387.1744132029@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4050387.1744132029%40sss.pgh.pa.us>
Precedence: bulk

Nathan Bossart <nathandbossart@gmail.com> writes:
> I do think it's worth considering going back to copying
> pg_largobject_metadata's files for upgrades from v16 and newer.

(If we do this) I don't see why we'd need to stop at v16.  I'm
envisioning that we'd use COPY, which will be dealing in the
text representation of aclitems, and I don't think that's changed
in a long time.  The sort of thing that would break it is changes
in the set of available/default privilege bits for large objects.

That is, where the dump currently contains something like

SELECT pg_catalog.lo_create('2121');
ALTER LARGE OBJECT 2121 OWNER TO postgres;
GRANT ALL ON LARGE OBJECT 2121 TO joe;

we'd have

COPY pg_largeobject_metadata FROM STDIN;
...
2121	10	{postgres=rw/postgres,joe=rw/postgres}
...

and some appropriate COPY data for pg_shdepend too.

(The fact that this representation will contain both numeric and
symbolic role OIDs is why I was concerned about OID stability.)

The key thing that worries me here is if the source and target
versions have different ideas of which roles are pinned, which would
silently change what appears in pg_shdepend.  But it'd only really
break if a role mentioned in some LO's owner or ACL is pinned in the
source and not in the target, which seems unlikely.  (In the other
direction, we'd just be adding a useless row in pg_shdepend.)

			regards, tom lane