How PostgreSQL Queries Tables Depending on Extension (Type)

In this article, the author discusses PostgreSQL queries based on the extension types.

By digoal

Background

If a dependency exists when deleting an extension plug-in for PostgreSQL, you must delete the dependency must first. You can also specify the cascade to delete extension plug-in together with objects dependent on the plug-in.

postgres=# drop extension palaemon ;  
ERROR:  2BP01: cannot drop extension palaemon because other objects depend on it  
DETAIL:  index v_hnsw_idx_t depends on operator class palaemon_hnsw_ops for access method palaemon_hnsw  
index v_ivfflat_idx_t depends on operator class palaemon_ivfflat_float_ops for access method palaemon_ivfflat  
index v_ivfflat_idx depends on operator class palaemon_ivfflat_float_ops for access method palaemon_ivfflat  
HINT:  Use DROP ... CASCADE to drop the dependent objects too.  
LOCATION:  reportDependentObjects, dependency.c:997  
  
postgres=# drop extension orafce ;  
ERROR:  2BP01: cannot drop extension orafce because other objects depend on it  
DETAIL:  column id of table t1234 depends on type varchar2  
HINT:  Use DROP ... CASCADE to drop the dependent objects too.  
LOCATION:  reportDependentObjects, dependency.c:997

The id field depends on orafce, so "drop extension orafce cascade" deletes the id field.

postgres=# drop extension orafce cascade;  
NOTICE:  00000: drop cascades to column id of table t1234  
LOCATION:  reportDependentObjects, dependency.c:1021  
DROP EXTENSION  
postgres=# \d t1234  
              Table "public.t1234"  
 Column | Type | Collation | Nullable | Default   
--------+------+-----------+----------+---------  
Publications:  
    "pub"

A plug-in may contain objects such as new type, operator, ops, and function. The question is how to know what these objects are referenced by.

with etypes as  
 (  
  select classid::regclass,  
         objid,  
         deptype,  
         e.extname  
    from pg_depend  
         join pg_extension e  
           on refclassid = 'pg_extension'::regclass  
          and refobjid = e.oid  
  where classid = 'pg_type'::regclass  
 )  
 select etypes.extname,  
        etypes.objid::regtype as type,  
        n.nspname as schema,  
        c.relname as table,  
        attname as column  
  
  from pg_depend  
    
       join etypes  
         on etypes.classid = pg_depend.refclassid  
        and etypes.objid = pg_depend.refobjid  
          
       join pg_class c on c.oid = pg_depend.objid  
         
       join pg_namespace n on n.oid = c.relnamespace  
         
       join pg_attribute attr  
         on attr.attrelid = pg_depend.objid  
        and attr.attnum = pg_depend.objsubid  
 where pg_depend.classid = 'pg_class'::regclass;

The above SQL statements can only query the dependency of type, but not other objects. You can modify the statements to query other dependencies.

extname |   type   | schema | table | column   
---------+----------+--------+-------+--------  
 orafce  | varchar2 | public | t1234 | id  
(1 row)

Community

How PostgreSQL Queries Tables Depending on Extension (Type)

Background

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

Tablestore

ApsaraDB for ClickHouse