Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table - Mailing list pgsql-bugs

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] 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)
```