public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hüseyin Demir <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
Date: Sun, 7 Jun 2026 12:53:07 +0200
Message-ID: <CAB5wL7bH=0eAi5guRaxr2ZKomNfAwpADmoDseTDB8P8Ro=eHPw@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
References: <[email protected]>
<CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
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@mail....;
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@mail....
Greg Sabino Mullane <[email protected]>, 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?
>
>
>
>
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
In-Reply-To: <CAB5wL7bH=0eAi5guRaxr2ZKomNfAwpADmoDseTDB8P8Ro=eHPw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox