级联软删除

Question

SQL一直拥有一个很棒的功能：级联删除。你事先计划好，当需要删除某些内容时，咔嚓！无需担心所有依赖记录。

然而，现在实际上删除任何东西几乎成为了禁忌。您将其标记为已删除并停止显示它。不幸的是，我一直没有能够找到一种解决方案来处理具有依赖关系记录的软删除。 我总是手动编写复杂的软删除网络。

我是否完全错过了更好的解决方案？

- Arthur Chaparyan

Answer 1

最近我想出了一种使用Postgres 9.6的继承功能将实体分为已删除和未删除的部分来解决级联软删除的方法。以下是我为我们项目撰写的文档副本：

---

级联软删除

摘要

在本文中，我描述了我们当前处理Postgres数据库中对象删除的方法，并介绍了当前实现的缺陷。例如，到目前为止，我们没有办法实现级联软删除。然后，我展示了一种方法，它结合了Postgres的 级联硬删除和容易实现、维护且在所有搜索查询中提高性能的归档方法。

关于GORM中的软删除

在使用Go编写的fabric8-services/fabric8-wit 项目中，我们使用了一个面向对象的数据库映射器叫做GORM。

GORM提供了一种软删除数据库条目的方法：

如果模型有DeletedAt 字段，它将自动获得软删除功能！当调用Delete时，它不会永久从数据库中删除，但仅将DeletedAt 字段的值设置为当前时间。

假设您有一个模型定义，换句话说，一个Go结构看起来像这样：

// User is the Go model for a user entry in the database
type User struct {
    ID        int
    Name      string
DeletedAt *time.Time
}

假设你已经通过ID从数据库中加载了一个现有用户条目，并将其存储到名为u的对象中。

id := 123
u := User{}
db.Where("id=?", id).First(&u)

如果您继续使用GORM删除对象：

db.Delete(&u)

在 SQL 中使用 DELETE 不会删除数据库条目，而是会更新该行并将 deleted_at 设置为当前时间：

UPDATE users SET deleted_at="2018-10-12 11:24" WHERE id = 123;

GORM中软删除的问题 - 依赖反转和无级联

上述提到的软删除对于归档单个记录非常好，但对于所有依赖它的记录可能会导致非常奇怪的结果。这是因为GORM的软删除不像 SQL 中使用 ON DELETE CASCADE 对外键进行建模，可以作为一个潜在的DELETE级联。

当你设计一个数据库时，通常会先设计一个表，然后可能再设计另一个表，并将第一个表的外键与之关联：

CREATE TABLE countries (
    name text PRIMARY KEY,
    deleted_at timestamp
);

CREATE TABLE cities (
    name text,
    country text REFERENCES countries(name) ON DELETE CASCADE,
    deleted_at timestamp
);

我们建立了一个涵盖特定国家引用的国家和城市列表。当您DELETE一个国家记录时，所有城市也将被删除。但是，由于表中具有一个deleted_at列，在表示国家或城市的Go结构中携带，GORM映射器只会软删除国家并保留相关城市。

GORM从而把（软）删除所有依赖城市的责任放在了开发者手中。换句话说，之前被建模为从城市到国家的关系，现在被反向建模为从国家到城市的关系。这是因为当删除国家时，用户/开发人员现在负责（软）删除属于该国家的所有城市。

如果我们能够拥有软删除和ON DELETE CASCADE的所有好处，那不是很好吗？实际上，我们可以轻松做到这一点。让我们先专注于单个表，即countries表。

假设我们可以有另一个名为countries_archive的表，它具有与countries表完全相同的结构。同时，假设将应用于countries的所有未来模式迁移都应用于countries_archive表。唯一的例外是唯一约束和外键将不适用于countries_archive。

我们可以使用Postgres中的Inheritenance来创建这样的表：

CREATE TABLE countries_archive () INHERITS (countries);

生成的 countries_archive 表旨在存储所有记录，其中 deleted_at IS NOT NULL。

请注意，在我们的 Go 代码中，我们永远不会直接使用任何 _archive 表。相反，我们将查询从 *_archive 表继承的原始表，然后 Postgres 自动地查看 *_archive 表。稍微向下滑动一点，我会解释一下其中的原因；这与分区有关。

将条目移动到档案表上（软）删除

由于两个表，countries 和 countries_archive 在模式方面完全相同，因此当

1. 在 countries 表上发生 DELETE
2. 或者当进行软删除时通过将 deleted_at 设置为非 NULL 值。

时，我们可以使用触发器函数非常容易地将其插入到存档中。

触发器函数如下：

CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
    -- When a soft-delete happens...
    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;
    -- When a hard-DELETE or a cascaded delete happens
    IF (TG_OP = 'DELETE') THEN
        -- Set the time when the deletion happens
        IF (OLD.deleted_at IS NULL) THEN
            OLD.deleted_at := now();
        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
        -- this is what is triggered by GORM
        UPDATE OF deleted_at 
        -- this is what is triggered by a cascaded DELETE or a direct hard-DELETE
        OR DELETE
    ON countries
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

结论

最初，Postgres中的继承功能是为了分区数据而开发的。当您使用特定列或条件搜索分区数据时，Postgres可以找出要搜索的分区，从而提高查询性能。

我们可以通过仅搜索存在的实体来获得这种性能改进，除非另有说明。存在的条目是指deleted_at IS NULL为真的条目。(请注意，如果GORM的模型结构中有DeletedAt，则GORM将自动向每个查询添加AND deleted_at IS NULL。)

让我们运行一个EXPLAIN，看看Postgres是否已经知道如何利用我们的分离:

EXPLAIN SELECT * FROM countries WHERE deleted_at IS NULL;
+-------------------------------------------------------------------------+
| QUERY PLAN                                                              |
|-------------------------------------------------------------------------|
| Append  (cost=0.00..21.30 rows=7 width=44)                              |
|   ->  Seq Scan on countries  (cost=0.00..0.00 rows=1 width=44)          |
|         Filter: (deleted_at IS NULL)                                    |
|   ->  Seq Scan on countries_archive  (cost=0.00..21.30 rows=6 width=44) |
|         Filter: (deleted_at IS NULL)                                    |
+-------------------------------------------------------------------------+

我们可以看到，Postgres仍会搜索两个表，countries和countries_archive。让我们看看在创建countries_archive表时添加检查约束会发生什么：

CREATE TABLE countries_archive (
    CHECK (deleted_at IS NOT NULL)
) INHERITS (countries);

现在，Postgres知道当预期的deleted_at为NULL时，可以跳过countries_archive：

EXPLAIN SELECT * FROM countries WHERE deleted_at IS NULL;
+----------------------------------------------------------------+
| QUERY PLAN                                                     |
|----------------------------------------------------------------|
| Append  (cost=0.00..0.00 rows=1 width=44)                      |
|   ->  Seq Scan on countries  (cost=0.00..0.00 rows=1 width=44) |
|         Filter: (deleted_at IS NULL)                           |
+----------------------------------------------------------------+

注意在上述的EXPLAIN中，countries_archive表的顺序扫描被省略。

好处和风险

好处

1. 我们可以有定期的级联删除，并让数据库自行确定删除顺序。
2. 同时，我们也归档了数据。每次软删除操作后都会进行。
3. 不需要进行任何Go代码更改。我们只需要为每个要归档的表设置一个表和触发器即可。
4. 如果我们决定不再使用触发器和级联软删除，则可以轻松地返回原来的状态。
5. 所有未来对原始表进行的架构迁移都将应用于该表的_archive版本。除了约束条件，这是有好处的。

风险

1. 假设您添加了一个新表，其中包含引用具有ON DELETE CASCADE的现有表的外键。如果现有表使用了上面的archive_record()函数，则当现有表中的某些项被软删除时，您的新表将接收到硬DELETE。如果您也为新的依赖表使用archive_record()，则这不是问题。但您只需要记住它。

最后的想法

这里介绍的方法并不能解决恢复单个行的问题。另一方面，这种方法也不会使其更加困难或复杂。它仍然是未解决的。

在我们的应用程序中，某些工作项字段没有指定外键。一个很好的例子是区域ID。这意味着当删除区域时，相关联的工作项不会自动删除。有两种情况会导致区域被删除：

1. 用户直接请求删除。
2. 用户请求删除空间，然后由于区域在空间上的外键约束而被删除。

请注意，在第一种情况下，用户的请求通过区域控制器代码和区域存储库代码，并且我们有机会修改所有引用不存在区域的工作项的所有内容。在第二种情况下，与区域相关的所有内容都发生并留在DB层，因此我们无法修改工作项。好消息是我们不必这样做。每个工作项都引用一个空间，因此当空间消失时，它们都将被删除。

适用于区域的内容也适用于迭代、标签和看板列。

如何应用于我们的数据库？

步骤如下：

1. 为所有继承原始表的表创建“* _archived”表。
2. 使用上述的archive_record()函数安装软删除触发器。
3. 通过执行硬DELETE将deleted_at IS NOT NULL的所有条目移到其各自的_archive表中，这将触发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 := now();
        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;

- Konrad Kleine

---

非常感谢@cdunham！很高兴听到创建这个的努力对某人有用。如果这对您有效，请告诉我。我希望您明白，您不需要GORM来使其工作。我相信在其他系统中不需要这样做。事实上，我现在更加关注事件溯源/CQRS/DDD，其中您可以摆脱状态的概念。状态是事件发生后物体的外观。 - Konrad Kleine

应该使用 now() 替代 archive_record() 函数中的 timenow()，以在新版本的 Postgres 上使其正常工作。 - vkopio

感谢@vkopio提供这个技巧！我已验证它可行，并更新了上面的代码。 - Konrad Kleine

archive_record 函数还有一个需要注意的地方：如果表中有生成列，它将无法工作，因为它试图将生成的值插入到不允许的归档表中。 - vkopio

Answer 2

我不得不说，触发器是专门为这种情况设计的。

（讨厌的部分是因为编写好的触发器非常难，当然也无法进行调试）

- Learning

---

如果您想避免为每个需要进行级联软删除的表编写触发程序，可以查看我的答案（https://dev59.com/WXRB5IYBdhLWcg3wz6QJ#53046345）。它很长，但只是因为我希望在深入解释每个步骤执行的操作及其原因之前，避免跳入深度。 - Konrad Kleine

Answer 3

外键约束可以执行级联更新。如果您在主键和删除标志上链接了表，那么当主表中的删除标志更改时，该更改将向下传播到详细信息表。我没有尝试过，但应该可以正常工作。

- Matt Hamilton

---

在我看来，这非常优雅。唯一的问题是你不能使用NULL Delete_Date，而必须使用一些任意日期，比如“9999-12-31”。 - HaxElit

再仔细考虑后，发现这种方法行不通，因为如果您软删除一个依赖记录，则会出现键约束错误，因为父记录的删除日期不同。看来太美好而难以实现了;) - HaxElit

Answer 4

我认为软删除的好处通常在于，不是每个表都有软删除标志，因此需要级联的事物数量很少。这些行在数据库中只是未使用，但不是孤立的 - 它们仅由已删除的行引用。

不过，像所有东西一样，这取决于你的模型。

- Cade Roux

---

哦，那么你就会有一个数据库充满了行，而无法立即看出哪些是被使用的，对吗？你可以在主表上连接，但如果级联关系深入几层，这可能会变得混乱。或者我错过了什么吗？ - Piskvor left the building

这取决于模型。在关系型设计中，如果删除标志不属于关系/元组/表 - 即它不是键的属性，我就不会放置一个。在星型模式中 - 你只会把它们放在核心表上。 - Cade Roux

如果您提供一个子系统模式的示例，我将向您展示我会在哪些地方放置删除标记。例如，除非您保留链接更改历史记录，否则我永远不会将它们放在多对多表上，在这种情况下，您还需要添加有效日期。 - Cade Roux

Answer 5

不确定你在谈论哪个后端，但你可以通过触发器捕捉“删除标志”的变化并将其级联修改。

- Steve Robbins