How to find out a object's parent object in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Sometimes it is helpful and needed to find out an object's (table, view, etc.) parent object.

For example, let's say we have a view named v4 and was created with these commands. v4 is based on view v3, and v3 is based on view v2 and etc.

create table test1 as select generate_series(1,1000);
create view v1 as select * from test1;
create view v2 as select * from v1;
create view v3 as select * from v2;
create view v4 as select * from v3;

This article covers how to find out the parent object of a specific object and how to quickly find how many objects are related to a specific object.

Environment

Product Version: 5.28

Resolution

We can use the below to find out the parent object of the target object:

SELECT  b.refobjid as oid,
        b.refobjid::regclass as object,
        a.refobjid as parent_oid, 
        a.refobjid::regclass as parent_object
FROM
    pg_depend a,
    pg_depend b
WHERE   a.refclassid = 1259     -- this is pg_depend
       AND a.classid = 2618     -- this is pg_rewrite
       AND b.deptype = 'i'      -- i means internal dependent
       AND a.objid = b.objid
       AND a.classid = b.classid
       AND a.refclassid = b.refclassid
       AND a.refobjid <> b.refobjid
       AND b.refobjid=382890;      -- set this value to the OID of the object you want to check

The result would be like:

  oid   | object | parent_oid | parent_object
--------+--------+------------+---------------
 382890 | v4     |     382886 | v3

then try to search for v3's oid, and repeat this method until there is nothing returned.

  oid   | object | parent_oid | parent_object
--------+--------+------------+---------------
 382886 | v3     |     382882 | v2

  oid   | object | parent_oid | parent_object
--------+--------+------------+---------------
 382882 | v2     |     382878 | v1

   oid   | object | parent_oid | parent_object
--------+--------+------------+---------------
 382878 | v1     |     382875 | test1