×
Community Blog How PostgreSQL Queries Tables Depending on Extension (Type)

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)  

References

0 0 0
Share on

digoal

210 posts | 13 followers

You may also like

Comments