PostgreSQL 查询 依赖extension (type)的table

作者

digoal

日期

2019-11-15

标签

PostgreSQL , extension , type , table , column , depend , 依赖

背景

删除extnesion时，如果有依赖，会提示必须删除依赖才能删除插件。或者指定cascade，连依赖插件的对象一起删除。

```
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
```

字段id依赖orafce，所以drop extension orafce cascade会把id字段删掉

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"

插件中可能包含new type, operator, ops, function等，如何知道这些对象被谁引用了呢？

https://tapoueh.org/blog/2019/11/list-postgresql-tables-using-extensions/

```
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;
```

这个sql只能查询type的依赖，无法查询其他对象的依赖，如果需要查询其他依赖，可以改一下sql

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

参考

https://tapoueh.org/blog/2019/11/list-postgresql-tables-using-extensions/

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.