任何开发人员最终都会面临一个挑战：对大型代码库进行重大修改。但在这里，我将介绍一种非正统的解决方案，由于底层数据库的强大和力量，所有应用程序代码的复杂性都不会被触动！这就是软删除。这是软删除，但我们会把逻辑放在数据库本身。继续阅读，了解 "如何 "和 "为什么"!

虽然这篇文章主要是关于PostgreSQL的，但我需要接触几次应用程序代码。由于Ruby on Rails是我最有经验的堆栈，我将在我的例子中使用它。虽然Ruby主义者可能会对这篇文章有额外的赞赏，但让我们先强调一下，最终的解决方案将完全采用SQL。

首先，让我们来谈谈删除本身，我们将了解 "硬 "和 "软 "删除之间的区别。

硬删除和软删除：简单介绍和比较

一个典型的应用程序的逻辑需要不时地删除数据。在SQL中，实现这种情况的直接方法是使用DELETE 语句简单地从数据库中删除记录。

这种 "硬 "删除的问题是，任何受影响的数据都会不可恢复地丢失，并在以后的分析中变得不可访问。

由于这个原因，开发人员经常采用另一种被广泛称为软删除的选项。这里的想法很简单：你可以指定一些属性（如deleted: true 或deleted_at: Time.now ）来标记它已经被删除，而不是从数据库中删除一条记录。现在，由于没有进行实际的删除，你的数据的整个历史可以保持完整。

当然，为了使这个方法可行，有必要对所有的请求进行范围控制，以便只有未删除的行对应用程序本身是可见的。

在Ruby中，这样的代码可能看起来是这样的。

# performing a "soft" delete
Order.find(1).update!(deleted: true)

# counting the visible records
Order.where.not(deleted: true).count

无论是 "硬 "还是 "软 "的方法都已经很普遍了。

那么，现在的问题是：如何在实际应用中优雅地从硬删除过渡到软删除？

当在一个实际应用中转换到软删除时，有许多因素需要考虑。首先，在应用程序方面，有许多删除记录的方式需要考虑到。

此外，数据库中可能存在依赖关系，这意味着依赖的记录应该被逐级删除。

class User
  has_many :orders, dependent: :destroy # or :delete
end

而且不要忘记外键，使得这种级联是隐含的。

# somewhere in a migration
add_foreign_key :orders, :users, on_delete: :cascade

我们怎样才能确保所有可能的删除都被发现并被执行--"柔和地"--如期进行？

如果有一些神奇的方法来实现应用程序之外的软删除，生活不是会更容易吗？如果在数据库层实现这一功能，从而消除对应用程序的任何需求，不是很好吗？

好吧，我们可以通过现代数据库的力量做到这一点。我相信任何先进的数据库，如MySQL或Oracle，都具有完成这项工作的功能，但在这个演示中，我将坚持使用PostgreSQL（我们在项目中使用的主要选择之一）。事实上，使用PostgreSQL，我们有几种方法可以以一种干净、简单和可管理的方式实现这个解决方案。

但是，等等，真的吗？

嗯，是的，真的。完全公开--这无疑是一个有点幽默的错误冒险，但尽管如此，我仍然非常期待这种方法成为现实--数据库只是把一些事情做得更好。想想数据的完整性。如果数据相关的逻辑是在应用方面，而你决定将一个微服务迁移到软删除，你就不需要谨慎地避免弄乱任何东西。

如果你有许多微服务的应用程序，如果所有与数据相关的规则和约束都是在应用程序层面上制定的，那么一些东西就很容易被破坏。有可能错过一些实例，一个应用程序执行检查，而另一个没有，因此，你最终会有损坏的数据。

而且，如果你遇到数据库逻辑的问题，PostgreSQL社区是开放源码中的第一大社区，所以你可以相信你会有一群坚实的开发者在一旁提供支持。

诚然，如果你不从开发的一开始就计划好，这将很难扩展，而且，如果你不断向数据库添加逻辑，它最终可能会变得和应用程序本身一样胖，而这其实并不是太好。但不管怎样，这在理论上肯定还是有可能的，所以我们继续前进吧。

为删除做准备

正如我在上面提到的，从硬删除切换到软删除包括2个简单的步骤。

将所有的删除行为改为更新
对所有查询进行范围界定，以隐藏 "已删除 "的记录

让我们从删除本身开始。首先，我们要为我们的例子提供一些背景。让我们打开psql控制台，建立一个环境。

CREATE TABLE users (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL
);

CREATE TABLE orders (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  user_id integer NOT NULL,
  number text NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

接下来，我们将用一些数据来填充它。

INSERT INTO users (name) VALUES ('Andrew'), ('Vladimir'), ('Sara');
INSERT INTO orders (user_id, number) VALUES (1, 'A1'), (1, 'A2'), (2, 'V1'), (2, 'V2'), (3, 'S3');

为了使软删除发挥作用，我们需要额外的列：deleted::boolean 或deleted_at::timestamp 。在真正的数据库中，我总是喜欢第二种方法，但现在让我们保持简单。

ALTER TABLE users ADD COLUMN deleted boolean NOT NULL DEFAULT false;
ALTER TABLE orders ADD COLUMN deleted boolean NOT NULL DEFAULT false;

现在我们已经准备好了模式，让我们把它用起来。

删除的黑魔法开始了

自古以来，PostgreSQL就支持带有规则的操作（创建、更改和删除）。这些都是SQL语法的数据库特定扩展，不在标准范围内。这是一个简单的概念：规则本质上是一个由查询执行器应用来修改你的查询的宏。

在执行之前，一个查询要经过几个步骤。首先，分析器接受初始请求并将其转换为查询树（在检查语法的正确性之后）。然后，分析器对该树进行语义分析。在这个阶段，它并不关心查询的语义（所有请求的对象是否真的存在于数据库中，更不用说它们的可访问性）。

在查询树被证明是正确的之后，现在是改写者扫描树的时候了，寻找适用于每个节点的规则。每当找到一些规则，改写者就用它来更新树。事实上，任何有一些数据库经验的人都会遇到视图的这种机制：在PostgreSQL中，视图只不过是一个规则，它指定了如何将SELECT FROM <view> 节点替换到相应的子查询中。

在应用了所有的规则，并且检查了所产生的查询树的语义之后，查询计划器/优化器就开始发挥作用。计划者和执行者都不知道关于原始请求的任何事情；他们只是处理重写的树。一个有趣的应用是，在某些情况下，你可以扩展SQL语法（正如Alexey Kondratov在他的FOSDEM-21的精彩演讲中所解释的那样）。

那么，我们如何将这种特殊的方法应用于我们的问题？事实上，这很容易做到：我们需要做的就是创建一个规则，将删除改为更新。让我们就这样做吧。

CREATE RULE "_soft_deletion" AS ON DELETE TO "orders" DO INSTEAD (
  UPDATE orders SET deleted = true WHERE id = old.id AND NOT deleted
);

现在我们可以尝试从orders 删除一些项目。

DELETE FROM orders WHERE id = 4;
SELECT FROM orders ORDER BY id;
 id | user_id | number | deleted
----+---------+--------+---------
  1 |       1 | A1     |
  2 |       1 | A2     |
  3 |       2 | V1     |
  4 |       2 | V2     | t
  5 |       3 | S3     |
(5 rows)

我们就这样做了!虽然这个操作本身只是一个经典的DELETE 语句，但PostgreSQL默默地应用了这个规则，并将请求改为更新。

级联删除

那级联删除呢？这将如何处理与我们的users 工作？让我们试一试吧。

DELETE FROM users WHERE id = 1;

在运行这个过程中，我们得到了一个错误。

ERROR:  XX000: referential integrity query on "users" from constraint "orders_user_id_fkey" on "orders" gave unexpected result
HINT:  This is most likely due to a rule having rewritten the query.

实际上，这是一个相当合理的错误。我们试图删除用户，但是相应的订单并没有在这个语句中体现出来，所以我们的外键约束是按照预期的保护参考完整性来工作的。

为了解决这个问题，让我们把orders 表也改一下。

CREATE RULE "_soft_deletion" AS ON DELETE TO "users" DO INSTEAD (
  UPDATE users SET deleted = true WHERE id = old.id AND NOT deleted
);

SELECT * FROM users ORDER BY id;
 id |   name   | deleted
----+----------+---------
  1 | Andrew   | t
  2 | Vladimir |
  3 | Sara     |

SELECT * FROM orders ORDER BY id;
 id | user_id | number | deleted
----+---------+--------+---------
  1 |       1 | A1     |
  2 |       1 | A2     |
  3 |       2 | V1     |
  4 |       2 | V2     | t
  5 |       3 | S3     |

另一个问题：虽然用户记录的软删除成功了，但订单仍然存在。这是因为我们的外键约束不能在一个从属表中提供级联更新。

我们可以用另一条规则来解决这个问题。

-- restore the "deleted" user for the next attempt
UPDATE users SET deleted = false WHERE id = 1;

CREATE RULE "_delete_orders" AS ON UPDATE TO users
  WHERE NOT old.deleted AND new.deleted
  DO ALSO UPDATE orders SET deleted = true WHERE user_id = old.id;

DELETE FROM users WHERE id = 1;

SELECT * FROM orders ORDER BY id;
 id | user_id | number | deleted
----+---------+--------+---------
  1 |       1 | A1     | t
  2 |       1 | A2     | t
  3 |       2 | V1     |
  4 |       2 | V2     | t
  5 |       3 | S3     |
(5 rows)

你可能已经发现了这里的区别：我们使用了DO INSTEAD ，而不是DO ALSO 规则。除了更新users ，这个规则还更新了orders ；这两个操作都是由PostgreSQL在处理同一个请求时执行的。

现在，依赖的orders 和相应的用户一起被删除。从应用程序的角度来看，实际上没有任何改变。它可以继续向数据库发送删除请求，并且由数据库本身来处理（尽管现在有点不同）。

虽然这些变化可能很复杂，但请注意，与典型的应用程序不同，SQL是一种声明性语言。这意味着我们只需要定义一次任何规则，然后它就会被应用于任何请求。这与应用程序代码形成了鲜明的对比，在那里我们需要单独修复每一个请求。

添加一个硬性的删除舱口

现在，在继续文章的第二部分（范围选择）之前，我想指出另一个问题。由于我们的数据库现在是 "软 "工作的，如果团队中有人（例如，你的数据库管理员）无法真正从数据库中删除一条记录，他们可能会相当惊讶。有时，即使是在使用软删除的情况下，我们仍然需要有删除记录的选项。

这可以通过给我们的规则添加一个WHERE 子句来实现。在这个子句中，我们将要求PostgreSQL在执行替换之前检查一些设置，比如rules.soft_deletion 。

首先，我们应该添加设置本身（要做到这一点，需要超级用户访问）。

ALTER SETTINGS SET rules.soft_deletion TO on;

然后，让我们修改我们所有的规则来检查这个设置。我们将用OR REPLACE 子句来做这件事。

CREATE OR REPLACE RULE "_soft_deletion"
  AS ON DELETE TO "orders"
  WHERE current_setting('rules.soft_deletion') = 'on'
  DO INSTEAD UPDATE orders SET deleted = true WHERE id = old.id;

有了这个，我们现在就可以为当前的连接修改这个设置，并在必要时进行硬删除。

SET rules.soft_deletion TO off;
DELETE FROM orders WHERE id = 5;
SELECT * FROM orders ORDER BY id;
 id | user_id | number | deleted
----+---------+--------+---------
  1 |       1 | A1     | t
  2 |       1 | A2     | t
  3 |       2 | V1     |
  4 |       2 | V2     | t

范围

在教给我们的数据库这个新的 "安全 "删除技巧后，现在是时候掩盖证据了！要做到这一点，我们将教给我们的数据库一个新的 "安全 "删除技巧。要做到这一点，我们要教它自动隐藏任何已被标记为删除的记录。

我们不能通过抓取SELECTs的方式来做到这一点，因为在PostgreSQL中对SELECT 规则的支持在某些方面是有限的。例如，你不能为一个有索引的表定义这样的规则。

绕过这个限制的一个方法是用一个VIEW 来代替表，并为插入、更新和删除记录添加规则。

ALTER TABLE orders RENAME TO _orders;

CREATE VIEW orders AS SELECT * FROM _orders WHERE NOT deleted;

SELECT * FROM orders ORDER BY id;
 id | user_id | number | deleted
----+---------+--------+---------
  3 |       2 | V1     |

VIEWPostgreSQL视图的神奇之处在于，你可以直接从INSERT 、UPDATE 和DELETE 行。在引擎盖下，PostgreSQL查询执行器将应用一个视图特定的规则，有效地将所有这些操作 "委托 "给底层表。这是唯一可能的，因为我们的视图是建立在唯一的表之上的。

现在我们来试试。

INSERT INTO orders (user_id, number) VALUES (2, 'V3');

-- try the results in the view...
SELECT FROM orders;
 id | user_id | number | deleted
----+---------+--------+---------
  3 |       2 | V1     |
  6 |       3 | V3     |

-- ..and the underlying table
SELECT FROM _orders;
 id | user_id | number | deleted
----+---------+--------+---------
  1 |       1 | A1     | t
  2 |       1 | A2     | t
  3 |       2 | V1     |
  4 |       2 | V2     | t
  6 |       3 | V3     |

很好。

接下来，让我们试试删除。

DELETE FROM orders WHERE id = 6;
DELETE 0

SELECT FROM orders;
 id | user_id | number | deleted
----+---------+--------+---------
  3 |       2 | V1     |

SELECT FROM _orders;
 id | user_id | number | deleted
----+---------+--------+---------
  1 |       1 | A1     | t
  2 |       1 | A2     | t
  3 |       2 | V1     |
  4 |       2 | V2     | t
  6 |       3 | V3     |

成功了!我们的数据库现在完全按照我们的需要工作。所有的删除都是安全的，而且范围是由视图正确提供的，它的行为与底层表完全一样。

然而，我们还可以再做一个改进。以前，为了实现从表中硬删除，我们必须添加一个条件。但是，当使用视图层时，我们不再需要这样做了。我们可以在视图上做同样的事情，而不是在表本身上定义规则，保持我们的表重命名，但在所有其他方面都不改变。

这整个场景与我们之前所做的非常相似。

添加deleted 列，以允许软删除users 和orders
重命名表：orders ->_orders 和users ->_users
在表的上面创建相应的视图：orders 和users
为users 视图定义2条规则（_soft_deletion 和_delete_orders ），也为orders定义_soft_deletion 规则。

-- let's suggest we are at the initial stage with users and orders

ALTER TABLE users RENAME TO _users;
CREATE VIEW users AS SELECT * FROM _users WHERE deleted IS NULL OR NOT deleted;

ALTER TABLE orders RENAME TO _orders;
CREATE VIEW orders AS SELECT * FROM _orders WHERE deleted IS NULL OR NOT deleted;

CREATE RULE _soft_deletion AS ON DELETE TO orders DO INSTEAD (
  UPDATE _orders SET deleted = true WHERE id = old.id
);

CREATE RULE _soft_deletion AS ON DELETE TO users DO INSTEAD (
  UPDATE _users SET deleted = true WHERE id = old.id
);

-- here we deal with updates in _users table, not with a view
CREATE RULE _delete_orders AS ON UPDATE TO _users
  WHERE old.deleted IS NULL OR NOT old.deleted AND new.deleted
  DO ALSO UPDATE _orders SET deleted = true WHERE user_id = old.id;

在这里，你可以找到再现该功能的最终SQL片段。

这样，向软删除的迁移就完成了。虽然在本文的开头我使用了几个Ruby on Rails的片段，但最终的解决方案使用了普通的SQL（PostgreSQL版本），所以实际上，应用程序使用哪种堆栈并不重要；这可以在Python/Django，或者PHP/Symphony，或者其他任何东西上使用。

迁移

这种方法唯一需要你自己处理的是把DDL代码包装成迁移。我想在这里多停留一会儿。在Ruby on Rails中，你有几个关于如何定义迁移规则的选项。主要的问题是：虽然你可以通过调用execute(sql) 命令来运行SQL，但应该如何将变化反映在数据库模式中呢？

默认模式，db/schema.rb ，是由Rails作为Ruby代码构建的。它与数据库版本无关，也很安全，但在功能上受到限制。就其迁移而言，Rails甚至不支持更流行的功能，如自定义类型和视图，更不用说像规则这样的怪兽了。对于一个必须支持与许多数据库集成的框架来说，这是一个谨慎的决定。但这也意味着我们需要其他的选项来构建模式。

第一个选项：从默认的db/schema.rb 转到db/structure.sql 。后者不是由Rails提供的，而是由PostgreSQL本身提供的纯SQL。在数据库中创建的所有对象都将反映在模式中。

这样的力量不能在没有相应的黑暗面的情况下实现。

第一个坏处是模式的格式是特定版本的。每次数据库的本地版本与服务器版本不同，它就会提供一个略有不同的模式，每次迁移都要修复一个模式，这不是对你时间的最佳利用。另一个问题是关于安全性的：如果你通过迁移以外的方式在本地数据库中做了一些改变（例如，如果你正在试验一些函数，或者你已经创建了一个临时表），这些改变会进入模式（PostgreSQL对你的意图一无所知，它只是报告它的当前状态）。这就需要开发人员更多的关心，而不是他们可能适应的。

我试图用我最新的项目pg_trunk来解决这个问题。除了表和索引，它还支持一些PostgreSQL特有的对象，例如：外键（与Rails相比有一些扩展）、函数、触发器和过程（类似于gemf(x)）、视图和物化视图（看看场景、类型、统计、序列，最后还有规则。

上面的最后一个片段可以在迁移中重新写成以下样子。

# let's suggest we are at the initial stage with users and orders
class SwitchToSoftDeletion < ActiveRecord::Migration[6.1]
  # add columns to mark deleted records

  add_column 'users',  :deleted, :boolean, null: false, default: false, comment: 'Recognize users as deleted'
  add_column 'orders', :deleted, :boolean, null: false, default: false, comment: 'Recognize orders as deleted'

  # place views on top of raw tables

  rename_table 'users', to: '_users'
  create_view 'users' do |v|
    v.sql_definition 'SELECT * FROM _users WHERE NOT deleted'
    v.comment 'A view hiding softly deleted rows'
  end

  rename_table 'orders', to: '_orders'
  create_view 'orders' do |v|
    v.sql_definition 'SELECT * FROM _orders WHERE NOT deleted'
    v.comment 'A view hiding softly deleted rows'
  end

  # add the corresponding rules

  create_rule 'orders' do |r|
    r.name '_soft_deletion'
    r.event :delete
    r.kind :instead
    r.command 'UPDATE _orders SET deleted = true WHERE id = old.id'
    r.comment 'Make soft instead of hard deletion'
  end

  create_rule 'users' do |r|
    r.name '_soft_deletion'
    r.event :delete
    r.kind :instead
    r.command 'UPDATE _users SET deleted = true WHERE id = old.id'
    r.comment 'Make soft instead of hard deletion'
  end

  create_rule 'users' do |r|
    r.name '_delete_orders'
    r.event :update
    r.kind :also
    r.where 'NOT old.deleted AND new.deleted'
    r.command 'UPDATE _orders SET deleted = true WHERE user_id = old.id'
    r.comment 'Mark dependent orders as softly deleted'
  end
end

你可以在这个文档中找到实现上述SQL Rails迁移的所有必要操作。我们欢迎你的反馈和功能要求，并对你的反馈和功能要求表示高度赞赏!

宣布结束

Egor Rogov在他惊人的新书 "PostgreSQL from the Inside"（目前只有俄文版，但我希望英文译本即将面世）中写道。

"对规则的支持被宣布为创建Postgres的目标之一。因此，自从这个数据库作为一个大学项目被开发出来后，规则就一直存在，而且在后来的几个阶段也被重新考虑过。这是一个强大的机制，尽管它在调试和理解方面都很难处理。甚至有人建议从PostgreSQL中删除规则，但这个运动没有找到社区的支持。尽管如此，在大多数情况下，使用触发器而不是规则更方便、更安全。"

尽管我同意这个观念，但我还是忍不住要展示一下这个强大功能中比较亮眼的部分。

但是，无论规则多么闪亮，从这篇愚蠢的文章中得到的更重要的启示是要有一点不同。作为开发者，在面对架构挑战时（比如从硬删除转为软删除），我们不必纯粹停留在应用程序的边界内。相反，我们可以考虑复杂性本身之外的其他选择，特别是在数据存储层。现代数据库是令人印象深刻的强大工具，让你以更干净、高效和安全的方式解决许多任务。

如何用PostgreSQL进行软删除（并且要有数据库的逻辑）