如果没有被任何其他子项引用，则删除父项 [英] Delete parent if it's not referenced by any other child

问题描述

我有一个示例情况:parent 表有一个名为 id 的列，在 child 表中作为外键引用.

删除子行时，如果没有其他子行引用，如何删除父行?

解决方案

在 PostgreSQL 9.1 或更高版本中，您可以使用 数据修改 CTE.这通常不太容易出错.它最小化两个 DELETE 之间的时间范围，其中 竞争条件 可能导致并发操作的意外结果:

WITH del_child AS (从孩子中删除WHERE child_id = 1返回 parent_id，child_id)从父 p 中删除使用 del_child x哪里 p.parent_id = x.parent_id并且不存在 (选择 1从孩子 c哪里 c.parent_id = x.parent_idAND c.child_id <>x.child_id——！);

SQL 小提琴.

无论如何都会删除子项.我引用了手册:><块引用>

WITH 中的数据修改语句只执行一次，并且始终完成，与主查询是否读取无关他们的所有(或任何)输出.注意这是不同的来自 WITH 中 SELECT 的规则:如上一节所述，SELECT 的执行只执行到主查询要求其输出.

父级仅在没有其他子级时才会被删除.
注意最后一个条件.与人们的预期相反，这是必要的，因为:

<块引用>

WITH 中的子语句同时执行和主要查询.因此，当使用数据修改WITH中的语句，指定的实际更新顺序发生是不可预测的.所有语句都以相同的方式执行快照(见第 13 章)，所以他们不能看到"彼此的效果在目标表上.

大胆强调我的.
我使用列名 parent_id 代替了非描述性的 id.

消除竞争条件

为了完全消除我上面提到的可能的竞争条件，首先锁定父行.当然，所有类似的操作都必须遵循相同的程序才能运行.

WITH lock_parent AS (选择 p.parent_id, c.child_id从孩子 cJOIN 父 p ON p.parent_id = c.parent_idWHERE c.child_id = 12 -- 在这里提供一次 child_idFOR NO KEY UPDATE -- 锁定父行.), del_child AS (从子 c 中删除使用 lock_parent l哪里 c.child_id = l.child_id)从父 p 中删除使用 lock_parent l哪里 p.parent_id = l.parent_id并且不存在 (选择 1从孩子 c哪里 c.parent_id = l.parent_idAND c.child_id <>l.child_id——！);

这种方式一次只有一个事务可以锁定同一个父级.因此，不可能发生多个事务删除同一父级的子级，仍然看到其他子级并保留父级，而所有子级之后都消失的情况.(使用 FOR NO KEY UPDATE 仍然允许对非键列进行更新.)

如果这种情况从未发生，或者您可以忍受(几乎永远不会)发生 - 第一个查询更便宜.否则，这是安全路径.

FOR NO KEY UPDATE 是在 Postgres 9.4 中引入的.手册中的详细信息.在旧版本中使用更强的锁 FOR UPDATE 代替.

I have an example situation: parent table has a column named id, referenced in child table as a foreign key.

When deleting a child row, how to delete the parent as well if it's not referenced by any other child?

解决方案

In PostgreSQL 9.1 or later you can do this with a single statement using a data-modifying CTE. This is generally less error prone. It minimizes the time frame between the two DELETEs in which a race conditions could lead to surprising results with concurrent operations:

WITH del_child AS (
    DELETE FROM child
    WHERE  child_id = 1
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  del_child x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT 1
   FROM   child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id   -- !
   );

SQL Fiddle.

The child is deleted in any case. I quote the manual:

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

The parent is only deleted if it has no other children.
Note the last condition. Contrary to what one might expect, this is necessary, since:

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables.

Bold emphasis mine.
I used the column name parent_id in place of the non-descriptive id.

Eliminate race condition

To eliminate possible race conditions I mentioned above completely, lock the parent row first. Of course, all similar operations must follow the same procedure to make it work.

WITH lock_parent AS (
   SELECT p.parent_id, c.child_id
   FROM   child  c
   JOIN   parent p ON p.parent_id = c.parent_id
   WHERE  c.child_id = 12              -- provide child_id here once
   FOR    NO KEY UPDATE                -- locks parent row.
   )
 , del_child AS (
   DELETE FROM child c
   USING  lock_parent l
   WHERE  c.child_id = l.child_id
   )
DELETE FROM parent p
USING  lock_parent l
WHERE  p.parent_id = l.parent_id
AND    NOT EXISTS (
   SELECT 1
   FROM   child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- !
   );

This way only one transaction at a time can lock the same parent. So it cannot happen that multiple transactions delete children of the same parent, still see other children and spare the parent, while all of the children are gone afterwards. (Updates on non-key columns are still allowed with FOR NO KEY UPDATE.)

If such cases never occur or you can live with it (hardly ever) happening - the first query is cheaper. Else, this is the secure path.

FOR NO KEY UPDATE was introduced with Postgres 9.4. Details in the manual. In older versions use the stronger lock FOR UPDATE instead.

这篇关于如果没有被任何其他子项引用，则删除父项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！