Re: Horribly slow pg_upgrade performance with many Large Objects - Mailing list pgsql-hackers

On Tue, Apr 08, 2025 at 01:42:20PM -0400, Tom Lane wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
>> Unless I'm missing something, we don't seem to have had any dependency
>> handling before commit 12a53c7.  Was that broken before we moved to SQL
>> commands?
> 
> Sounds like it :-(

Huh.  Sure enough, it seems to be lost during an upgrade from 9.6 to 10.

v9.6:

    postgres=# select lo_from_bytea(1234, '1234');
     lo_from_bytea
    ---------------
              1234
    (1 row)

    postgres=# create role bob;
    CREATE ROLE
    postgres=# grant select on large object 1234 to bob;
    GRANT
    postgres=# drop role bob;
    ERROR:  role "bob" cannot be dropped because some objects depend on it
    DETAIL:  privileges for large object 1234

v10 (upgraded from v9.6):

    postgres=# select lo_get(1234);
       lo_get
    ------------
     \x31323334
    (1 row)

    postgres=# drop role bob;
    DROP ROLE

If I then try to upgrade that database to v17, it fails like this:

    pg_restore: from TOC entry 2422; 0 0 ACL LARGE OBJECT 1234 nathan
    pg_restore: error: could not execute query: ERROR:  role "16384" does not exist
    Command was: GRANT SELECT ON LARGE OBJECT 1234 TO "16384";

I've also verified that the dependency information is carried over in
upgrades to later versions (AFAICT all the supported ones).

-- 
nathan