级联软删除

请注意countries_archive,前面没有对表格进行顺序扫描EXPLAIN.

好处和风险

优点

我们有定期删除的级联删除,可以让DB找出删除内容的顺序.

与此同时,我们也在归档我们的数据.每次软删除

不需要更改Go代码.我们只需要为每个要归档的表设置一个表和一个触发器.

每当我们发现我们不再希望这种行为与触发器和级联软删除时,我们可以轻松返回.

将来对原始表进行的所有模式迁移都将应用于_archive该表的版本.除了约束,这是好的.

风险

假设您添加一个新表,该表引用具有外键的另一个现有表ON DELETE CASCADE.如果现有表使用archive_record()上面的函数,DELETE当现有表中的某些内容为软删除时,新表将收到硬盘.如果您也使用archive_record()新的依赖表,这不是问题.但你必须记住它.

最后的想法

此处介绍的方法无法解决恢复单个行的问题.另一方面,这种方法不会使其变得更难或更复杂.它仍然没有解决.

在我们的应用程序中,工作项的某些字段没有指定外键.一个很好的例子是区域ID.这意味着当区域为DELETEd时,关联的工作项不会自动DELETEd.当一个区域被移除时,有两种情况:

直接从用户请求删除.

用户请求删除空格,然后由于空间上的外键约束而删除该区域.

请注意,在第一个场景中,用户的请求通过区域控制器代码,然后通过区域存储库代码.我们有可能在任何这些图层中修改将引用不存在区域的所有工作项.在第二种情况下,与区域相关的所有内容都会发生并保留在数据库层上,因此我们无法模拟工作项.好消息是我们没有必要.每个工作项都引用一个空格,因此当空间消失时,它将被删除.

适用于区域的内容也适用于迭代,标签和电路板列.

脚步

为继承原始表的所有表创建"*_archived"表.

使用上述archive_record()功能安装软删除触发器.

通过执行硬操作将所有条目移动deleted_at IS NOT NULL到其各自的_archive表中,DELETE这将触发该archive_record()功能.

这是一个完整的例子,我们演示了两个表的级联软删除,countries以及capitals.我们将显示如何独立于为删除选择的方法归档记录.

CREATE TABLE countries (
    id int primary key,
    name text unique,
    deleted_at timestamp
);
CREATE TABLE countries_archive (
    CHECK ( deleted_at IS NOT NULL )
) INHERITS(countries);

CREATE TABLE capitals (
    id int primary key,
    name text,
    country_id int references countries(id) on delete cascade,
    deleted_at timestamp
);
CREATE TABLE capitals_archive (
    CHECK ( deleted_at IS NOT NULL )
) INHERITS(capitals);

CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.deleted_at IS NOT NULL) THEN
        EXECUTE format('DELETE FROM %I.%I WHERE id = $1', TG_TABLE_SCHEMA, TG_TABLE_NAME) USING OLD.id;
        RETURN OLD;
    END IF;
    IF (TG_OP = 'DELETE') THEN
        IF (OLD.deleted_at IS NULL) THEN
            OLD.deleted_at := timenow();
        END IF;
        EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                    , TG_TABLE_SCHEMA, TG_TABLE_NAME || '_archive')
        USING OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER soft_delete_countries
    AFTER
        UPDATE OF deleted_at 
        OR DELETE
    ON countries
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

CREATE TRIGGER soft_delete_capitals
    AFTER
        UPDATE OF deleted_at 
        OR DELETE
    ON capitals
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

INSERT INTO countries (id, name) VALUES (1, 'France');
INSERT INTO countries (id, name) VALUES (2, 'India');
INSERT INTO capitals VALUES (1, 'Paris', 1);
INSERT INTO capitals VALUES (2, 'Bengaluru', 2);

SELECT 'BEFORE countries' as "info", * FROM ONLY countries;
SELECT 'BEFORE countries_archive' as "info", * FROM countries_archive;
SELECT 'BEFORE capitals' as "info", * FROM ONLY capitals;
SELECT 'BEFORE capitals_archive' as "info", * FROM capitals_archive;

-- Delete one country via hard-DELETE and one via soft-delete
DELETE FROM countries WHERE id = 1;
UPDATE countries SET deleted_at = '2018-12-01' WHERE id = 2;

SELECT 'AFTER countries' as "info", * FROM ONLY countries;
SELECT 'AFTER countries_archive' as "info", * FROM countries_archive;
SELECT 'AFTER capitals' as "info", * FROM ONLY capitals;
SELECT 'AFTER capitals_archive' as "info", * FROM capitals_archive;