Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

Hi, sorry for the late reply. There was a problem in my mailbox.

I was able to reproduce the same issue with the pg_stat_statements
extension. The problem is valid for orphan pg_init_privs records.
Therefore, I created a simple patch to introduce a new check to pg_upgrade
binary.

PS: I'm working on PostgreSQL 14 (on different minor versions)

Please see [1]
<https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA(at)mail(dot)gmail(dot)com>
to see and review the problem I try to solve.

```
my_db_v2=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid = pip.objoid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'pg_stat_statements'
AND pip.privtype = 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------+--------------------
16458 | 1255 | e | {16449=X/16449} | pg_stat_statements
16466 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
16471 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
(3 rows)

```

I applied the following steps.

1. Create the role and database on postgres database.

```
CREATE ROLE benchmark_owner SUPERUSER;
CREATE DATABASE my_db OWNER benchmark_owner;
```

2. Connect to the my_db and execute the following commands.

```
SET ROLE benchmark_owner;
create extension pg_stat_statements;
```

Afterwards, I see the records in pg_init_privs

my_db=# reset role;
RESET
my_db=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid = pip.objoid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'pg_stat_statements'
AND pip.privtype = 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------------------------------------+--------------------
16458 | 1255 | e | {benchmark_owner=X/benchmark_owner} | pg_stat_statements
16466 | 1259 | e |
{benchmark_owner=arwdDxt/benchmark_owner,=r/benchmark_owner} |
pg_stat_statements
16471 | 1259 | e |
{benchmark_owner=arwdDxt/benchmark_owner,=r/benchmark_owner} |
pg_stat_statements
(3 rows)

3. Connect to postgres database and execute the following ones.

```
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_db';ALTER DATABASE my_db RENAME TO my_db_v2;
```

4. Connect to my_db_2 and execute the following ones.

```
REASSIGN OWNED BY benchmark_owner TO postgres;
DROP OWNED BY benchmark_owner;
```

5. Connect to postgres database and drop the role we created the extension

```
DROP ROLE benchmark_owner;
```

6. Connect to my_db_2 and check the dangling/orphan records.

```
my_db_v2=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid = pip.objoid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'pg_stat_statements'
AND pip.privtype = 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------+--------------------
16458 | 1255 | e | {16449=X/16449} | pg_stat_statements
16466 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
16471 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
(3 rows)
```

[1]
https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA(at)mail(dot)gmail(dot)com

Greg Sabino Mullane <htamfids(at)gmail(dot)com>, 20 May 2026 Çar, 15:07 tarihinde
şunu yazdı:

> PostgreSQL version: 18.3
>> ...
>
> 5. Verify orphan records remain in pg_init_privs:erprise Postgres Software
>> Products & Tech Support
>>
>
> Thanks for providing a failing use case. I ran this on a 18.3 server and
> found no orphaned rows - but I used the pg_stat_statements extension
> instead of pg_wait_sampling. Could you try your experiment using
> pg_stat_statements? And could you also show us the contents of the errant
> rows in pg_init_privs for the failing case?
>
>
>
>