如何列出受级联删除影响的表

Question

我正在尝试在15个以上的表上执行级联删除，但我不确定所有必需的外键已经正确配置。我想检查丢失的约束而不手动查看每个约束。

有没有办法获得将会受到级联删除查询影响的表列表？

Answer 1

使用pg_depend。示例：

create table master (id int primary key);
create table detail_1 (id int, master_id int references master(id) on delete restrict);
create table detail_2 (id int, master_id int references master(id) on delete cascade);
select pg_describe_object(classid, objid, objsubid)
from pg_depend 
where refobjid = 'master'::regclass and deptype = 'n';
                  pg_describe_object
------------------------------------------------------
 constraint detail_1_master_id_fkey on table detail_1
 constraint detail_2_master_id_fkey on table detail_2
(2 rows)

deptype = 'n'表示：

依赖性正常 - 分别创建的正常关系 对象。可以删除因物体而不会影响 引用对象。引用的对象只能通过 指定级联

也

使用pg_get_constraintdef()获取约束定义：

select 
    pg_describe_object(classid, objid, objsubid), 
    pg_get_constraintdef(objid)
from pg_depend 
where refobjid = 'master'::regclass and deptype = 'n';

                  pg_describe_object                  |                       pg_get_constraintdef
------------------------------------------------------+------------------------------------------------------------------
 constraint detail_1_master_id_fkey on table detail_1 | FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE RESTRICT
 constraint detail_2_master_id_fkey on table detail_2 | FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE CASCADE
(2 rows)

---

要找到完整的级联依赖链，我们应该使用递归并查看目录pg_constraint以获取依赖表的id。

with recursive chain as (
    select classid, objid, objsubid, conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    where refobjid = 'the_table'::regclass and deptype = 'n'
union all
    select d.classid, d.objid, d.objsubid, c.conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    join chain on d.refobjid = chain.conrelid and d.deptype = 'n'
    )
select pg_describe_object(classid, objid, objsubid), pg_get_constraintdef(objid)
from chain;

Answer 2

使用及物闭合，可以确定引用和引用表。一个警告是，此查询/视图取决于外国密钥确定依赖项的存在，并且如果丢失了FK，则会找到表格（后者似乎是OP要求的，）。

表依赖性通过外键

CREATE OR REPLACE VIEW table_dependencies AS (
WITH RECURSIVE t AS (
    SELECT
        c.oid AS origin_id,
        c.oid::regclass::text AS origin_table,
        c.oid AS referencing_id,
        c.oid::regclass::text AS referencing_table,
        c2.oid AS referenced_id,
        c2.oid::regclass::text AS referenced_table,
        ARRAY[c.oid::regclass,c2.oid::regclass] AS chain
    FROM pg_catalog.pg_constraint AS co
    INNER JOIN pg_catalog.pg_class AS c ON c.oid = co.conrelid
    INNER JOIN pg_catalog.pg_class AS c2 ON c2.oid = co.confrelid
--     Add this line as an input parameter if you want to make a one-off query
--     WHERE c.oid::regclass::text = 'YOUR TABLE'
    UNION ALL
    SELECT
        t.origin_id,
        t.origin_table,
        t.referenced_id AS referencing_id,
        t.referenced_table AS referencing_table,
        c3.oid AS referenced_id,
        c3.oid::regclass::text AS referenced_table,
        t.chain || c3.oid::regclass AS chain
    FROM pg_catalog.pg_constraint AS co
    INNER JOIN pg_catalog.pg_class AS c3 ON c3.oid = co.confrelid
    INNER JOIN t ON t.referenced_id = co.conrelid
    WHERE
        -- prevent infinite recursion by pruning paths where the last entry in
        -- the path already appears somewhere else in the path
        NOT (
            ARRAY[ t.chain[array_upper(t.chain, 1)] ] -- an array containing the last element
            <@                                        -- "is contained by"
            t.chain[1:array_upper(t.chain, 1) - 1]    -- a slice of the chain,
                                                      -- from element 1 to n-1
        )
)
SELECT  origin_table,
        referenced_table,
        array_upper(chain,1) AS "depth",
        array_to_string(chain,',') as chain
FROM t
);

表引用特定表

SELECT * FROM table_dependencies WHERE origin_table = 'clients';

与"客户端"表直接相关的表

SELECT *
FROM table_dependencies
WHERE referenced_table = 'clients'
AND depth = 2
ORDER BY origin_table;

Answer 3

是。您可以在交易和回滚中截断级联。注意ROLLBACK是保存数据的关键。Postgres将NOTICE您将影响其他引用表。

postgres=# begin;
BEGIN
postgres=# truncate table a cascade;
NOTICE:  truncate cascades to table "b"
TRUNCATE TABLE
postgres=# rollback;
ROLLBACK