在 PostgreSQL 不支援 Invalid Object 物件,在調整表格 DDL 時可能會因為有 View 或 Constraint 等限制影響無法直接刪除,這些物件可以利用 pg_depend 還有 pg_shdepend 兩張系統表來查詢相依關係。
其中 pg_depend 裡面會記錄的是各別資料庫間內部的物件關聯,而 pg_shdepend 則是會記錄跨資料庫的物件相依姓,像是使用者等等。
在兩張表格 pg_depend 和 pg_shdepend 裡面,皆有一個欄位 deptype 會告訴我們兩個物件的關係如何,以下示範是查詢 pg_depend 這張表格,篩選 deptype 為 n 的紀錄,代表著被 Reference 的物件要移除或調整時,需要指定 Cascade 或移除掉對應 Depend 的物件。
View
select n.nspname AS "Schema Name", relname AS "Table Name", a.attname AS "Column Name", r.ev_class::regclass AS "Depend View Name" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN pg_depend d ON d.refobjid = c.oid JOIN pg_rewrite r ON r.oid = d.objid JOIN pg_attribute a ON a.attrelid=c.oid AND a.attnum = d.refobjsubid WHERE n.nspname = 'public' AND c.relname = 'ta' AND d.deptype = 'n' AND classid::regclass::text = 'pg_rewrite' ;
select n.nspname AS "Schema Name", relname AS "Table Name", a.attname AS "Column Name", cs.conname AS "Constraint Name" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN pg_depend d ON d.refobjid = c.oid JOIN pg_constraint cs ON cs.oid = d.objid JOIN pg_attribute a ON a.attrelid=c.oid AND a.attnum = d.refobjsubid WHERE n.nspname = 'public' AND c.relname = 'ta' AND d.deptype = 'n' AND classid::regclass::text = 'pg_constraint';