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)
digoal - May 28, 2021
Alibaba Clouder - July 26, 2019
digoal - May 16, 2019
digoal - December 14, 2018
digoal - March 20, 2019
zhuodao - July 30, 2020
Fully managed and less trouble database servicesLearn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
A fully managed NoSQL cloud database service that enables storage of massive amount of structured and semi-structured dataLearn More
ApsaraDB for ClickHouse is a distributed column-oriented database service that provides real-time analysis.Learn More
More Posts by digoal