MIME-Version: 1.0
References: <19483-80de42dc4e62cfd6@postgresql.org>
 <CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
In-Reply-To: 
 <CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
From: =?UTF-8?Q?H=C3=BCseyin_Demir?= <huseyin.d3r@gmail.com>
Date: Sun, 7 Jun 2026 12:53:07 +0200
Message-ID: 
 <CAB5wL7bH=0eAi5guRaxr2ZKomNfAwpADmoDseTDB8P8Ro=eHPw@mail.gmail.com>
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv
 catalog table
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009db3c10653a7b3cf"
Archived-At: 
 <https://www.postgresql.org/message-id/CAB5wL7bH%3D0eAi5guRaxr2ZKomNfAwpADmoDseTDB8P8Ro%3DeHPw%40mail.gmail.com>
Precedence: bulk

--0000000000009db3c10653a7b3cf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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-=3DUE+=
=3Dmk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com>
to see and review the problem I try to solve.

```
my_db_v2=3D# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid =3D pip.objoid
JOIN pg_extension e ON e.oid =3D d.refobjid
WHERE e.extname =3D 'pg_stat_statements'
AND pip.privtype =3D 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------+-----------=
---------
16458 | 1255 | e | {16449=3DX/16449} | pg_stat_statements
16466 | 1259 | e | {16449=3DarwdDxt/16449,=3Dr/16449} | pg_stat_statements
16471 | 1259 | e | {16449=3DarwdDxt/16449,=3Dr/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=3D# reset role;
RESET
my_db=3D# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid =3D pip.objoid
JOIN pg_extension e ON e.oid =3D d.refobjid
WHERE e.extname =3D 'pg_stat_statements'
AND pip.privtype =3D 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------------------=
------------------+--------------------
16458 | 1255 | e | {benchmark_owner=3DX/benchmark_owner} | pg_stat_statemen=
ts
16466 | 1259 | e |
{benchmark_owner=3DarwdDxt/benchmark_owner,=3Dr/benchmark_owner} |
pg_stat_statements
16471 | 1259 | e |
{benchmark_owner=3DarwdDxt/benchmark_owner,=3Dr/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 =3D '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=3D# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid =3D pip.objoid
JOIN pg_extension e ON e.oid =3D d.refobjid
WHERE e.extname =3D 'pg_stat_statements'
AND pip.privtype =3D 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------+-----------=
---------
16458 | 1255 | e | {16449=3DX/16449} | pg_stat_statements
16466 | 1259 | e | {16449=3DarwdDxt/16449,=3Dr/16449} | pg_stat_statements
16471 | 1259 | e | {16449=3DarwdDxt/16449,=3Dr/16449} | pg_stat_statements
(3 rows)
```

[1]
https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=3DUE+=
=3Dmk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com


Greg Sabino Mullane <htamfids@gmail.com>, 20 May 2026 =C3=87ar, 15:07 tarih=
inde
=C5=9Funu yazd=C4=B1:

> PostgreSQL version: 18.3
>> ...
>
> 5. Verify orphan records remain in pg_init_privs:erprise Postgres Softwar=
e
>> 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?
>
>
>
>

--0000000000009db3c10653a7b3cf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi, sorry for the late reply. There was a problem in my ma=
ilbox.<br><br>I was able to reproduce the same issue with the pg_stat_state=
ments extension. The problem is valid for orphan pg_init_privs records. The=
refore, I created a simple patch to introduce a new check to pg_upgrade bin=
ary.<br><br>PS: I&#39;m working on PostgreSQL 14 (on different minor versio=
ns)<br><br>Please see <a href=3D"https://www.postgresql.org/message-id/flat=
/CAB5wL7aig++XphVjyBjvXG-=3DUE+=3Dmk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com">[1=
]</a> to see and review the problem I try to solve. <br><br>```<br>my_db_v2=
=3D# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs, e.extnam=
e<br>FROM pg_init_privs pip<br>JOIN pg_depend d ON d.objid =3D pip.objoid<b=
r>JOIN pg_extension e ON e.oid =3D d.refobjid<br>WHERE e.extname =3D &#39;p=
g_stat_statements&#39;<br>AND pip.privtype =3D &#39;e&#39;;<br>objoid | cla=
ssoid | privtype | initprivs | extname<br>--------+----------+----------+--=
------------------------------+--------------------<br>16458 | 1255 | e | {=
16449=3DX/16449} | pg_stat_statements<br>16466 | 1259 | e | {16449=3DarwdDx=
t/16449,=3Dr/16449} | pg_stat_statements<br>16471 | 1259 | e | {16449=3Darw=
dDxt/16449,=3Dr/16449} | pg_stat_statements<br>(3 rows)<br><br>```<br><br>I=
 applied the following steps.<br><br>1. Create the role and database on pos=
tgres database.<br><br>```<br>CREATE ROLE benchmark_owner SUPERUSER;<br>CRE=
ATE DATABASE my_db OWNER benchmark_owner;<br>```<br><br>2. Connect to the m=
y_db and execute the following commands.<br><br>```<br>SET ROLE benchmark_o=
wner;<br>create extension pg_stat_statements;<br>```<br><br>Afterwards, I s=
ee the records in pg_init_privs<br><br>my_db=3D# reset role;<br>RESET<br>my=
_db=3D# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs, e.ext=
name<br>FROM pg_init_privs pip<br>JOIN pg_depend d ON d.objid =3D pip.objoi=
d<br>JOIN pg_extension e ON e.oid =3D d.refobjid<br>WHERE e.extname =3D =
9;pg_stat_statements&#39;<br>AND pip.privtype =3D &#39;e&#39;;<br>objoid | =
classoid | privtype | initprivs | extname<br>--------+----------+----------=
+--------------------------------------------------------------+-----------=
---------<br>16458 | 1255 | e | {benchmark_owner=3DX/benchmark_owner} | pg_=
stat_statements<br>16466 | 1259 | e | {benchmark_owner=3DarwdDxt/benchmark_=
owner,=3Dr/benchmark_owner} | pg_stat_statements<br>16471 | 1259 | e | {ben=
chmark_owner=3DarwdDxt/benchmark_owner,=3Dr/benchmark_owner} | pg_stat_stat=
ements<br>(3 rows)<br><br>3. Connect to postgres database and execute the f=
ollowing ones.<br><br>```<br>SELECT pg_terminate_backend(pid)<br>FROM pg_st=
at_activity<br>WHERE datname =3D &#39;my_db&#39;;ALTER DATABASE my_db RENAM=
E TO my_db_v2;<br>```<br><br>4. Connect to my_db_2 and execute the followin=
g ones.<br><br>```<br>REASSIGN OWNED BY benchmark_owner TO postgres;<br>DRO=
P OWNED BY benchmark_owner;<br>```<br><br>5. Connect to postgres database a=
nd drop the role we created the extension<br><br>```<br>DROP ROLE benchmark=
_owner;<br>```<br><br>6. Connect to my_db_2 and check the dangling/orphan r=
ecords.<br><br>```<br>my_db_v2=3D# SELECT pip.objoid, pip.classoid, pip.pri=
vtype, pip.initprivs, e.extname<br>FROM pg_init_privs pip<br>JOIN pg_depend=
 d ON d.objid =3D pip.objoid<br>JOIN pg_extension e ON e.oid =3D d.refobjid=
<br>WHERE e.extname =3D &#39;pg_stat_statements&#39;<br>AND pip.privtype =
=3D &#39;e&#39;;<br>objoid | classoid | privtype | initprivs | extname<br>-=
-------+----------+----------+--------------------------------+------------=
--------<br>16458 | 1255 | e | {16449=3DX/16449} | pg_stat_statements<br>16=
466 | 1259 | e | {16449=3DarwdDxt/16449,=3Dr/16449} | pg_stat_statements<br=
>16471 | 1259 | e | {16449=3DarwdDxt/16449,=3Dr/16449} | pg_stat_statements=
<br>(3 rows)<br>```<div><br>[1] <a href=3D"https://www.postgresql.org/messa=
ge-id/flat/CAB5wL7aig++XphVjyBjvXG-=3DUE+=3Dmk3xfZZxkxV5XS4Hb58aHA@mail.gma=
il.com">https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=
=3DUE+=3Dmk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com</a></div></div><br><br><div =
class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail=
_attr">Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfi=
ds@gmail.com</a>&gt;, 20 May 2026 =C3=87ar, 15:07 tarihinde =C5=9Funu yazd=
=C4=B1:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">PostgreSQL version: 18.3<br>...</blockquote><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">5. Verify orphan records remain in pg_init_pr=
ivs:erprise Postgres Software Products &amp; Tech Support<br></blockquote><=
div><br></div><div>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?</div><div><br></div><div>=C2=A0=
</div></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><di=
v><br></div></div></div></div>
</blockquote></div>

--0000000000009db3c10653a7b3cf--