Owner altering does not take effect when fixing catalog issue "No pg_authid {'oid': yyy} entry for pg_class {'relnamespace': xxx, 'relname': 'zzz'} on all segments"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Note: This article is not mainly talking about the cause of this catalog issue and the method to fix it. The main purpose is to fix a specific owner assignment issue which we may encounter when fixing such kind of catalog issue.

For the catalog issue "No pg_authid {'oid': xxx} entry for pg_class {'relnamespace': yyy, 'relname': 'zzz'} on all segments", one of the normal causes of it should be that the owner with the oid xxx of the related object does not exist anymore, so one of the ways to solve it is to alter the object to one of the existing owners or directly drop it based on customer's confirmation and approval.

However, in this example, the owner id does not exist, but we can still find the same owner name (with different oid) in pg_authid, when we tried to alter the related object to the same owner name, the relowner regarding this table in pg_class will still refer to the non-existing owner id.

Below is the example:

• catalog issue:

No pg_authid {'oid': 172019} entry for pg_class {'relnamespace': 848412, 'relname': 'my_type1'} on all segments

• the owner id of my_type1 does not exist:

select * from pg_authid where oid = 172019;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpas
sword | rolvaliduntil | rolconfig | rolresqueue | rolcreaterextgpfd | rolcreaterexthttp | rolcreatewextgpfd | rolcr
eaterexthdfs | rolcreatewexthdfs | rolresgroup 
---------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------
------+---------------+-----------+-------------+-------------------+-------------------+-------------------+------
-------------+-------------------+-------------
(0 rows)

• However, actually the owner name u1 existed but just with different oid 171480:

testdb=# select count(*) from pg_authid where rolname = 'u1';
 count 
-------
     1
(1 row)

• As the owner name still exists, so we try to alter the owner to the same owner.
• After that, the relowner of pg_class may still refer to the same non-existing owner id:

gp_bdl_cust_prod01=# alter type s1.my_type1 owner to u1 ;                                                                      
ALTER TYPE

gp_bdl_cust_prod01=# select * from pg_class where relowner = 172019;                                                                                           relname   | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrelid
| relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhas
pkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
------------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------
+---------------+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+-------
-----+-------------+----------------+--------------+--------+------------
 my_type1   |       848412 |  848581 |   172019 |     0 |      848580 |             0 |        0 |         0 |             0 |             0 |             0
|             0 | f           | f           | c       | v          |        4 |         0 |           0 |        0 |        0 |       0 | f          | f
     | f           | f              |            0 |        |

• The reason for this phenomenon might be due to the existence of the same owner name which causes the "alter...owner to..." statement does not take effect actually.

Resolution

Alter the owner of the object to any other owner (confirm which owner to alter), and then alter it back to the original owner name.