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

问题描述

我有一个示例情况： parent table有一个名为 id 的列，在 child 表作为外键。

删除子行时，如果没有其他引用在PostgreSQL 9.1或更高版本中，你可以使用一个单独的语句来完成这个工作，使用一个 标签。 a data-modifying CTE 。这通常不太容易出错。 最小化竞争条件的两个DELETE之间的时间范围可能导致令人惊讶的并发操作结果：

  WITH DEL_CHILD AS（
 DELETE FROM child 
 WHERE child_id = 1 
 RETURNING parent_id，child_id 
）
 DELETE FROM parent （
 SELECT 1 
 FROM child c 
 WHERE c.parent_id = x.parent_id 
和c.child_id<> x.child_id  - ！
）; 
  

SQL小提琴

在任何情况下都会删除子项。我引用手册：

WITH 中的数据修改语句只执行一次，而
总是完成，与主查询是否读取
全部（或实际上是其中任何一个）的输出无关。请注意，这与 WITH 中的 SELECT 的规则是不同的
，如前一节所述，
执行 SELECT 仅在主要查询
要求其输出的情况下执行。

如果父项没有其他子项，则只会将其删除。

请注意最后一个条件。与预期相反，这是必要的，因为：

WITH 同时执行与其他
和主要查询。因此，当在 WITH 中使用数据修改
语句时，指定更新实际上
发生的顺序是不可预知的。所有的语句都是用相同的
快照执行的（见第13章），所以它们不能在目标表上看到对方的影响
。

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

消除竞争情况

我上面提到的条件完全，首先锁定父行 。当然，所有类似的操作都必须遵循相同的过程来使其工作。

$ p $ WITH lock_parent AS （
SELECT p.parent_id，c.child_id
FROM child c
JOIN父p ON p.parent_id = c.parent_id
WHERE c.child_id = 12 - 在此提供child_id一旦
FOR NO KEY UPDATE - 锁定父行。
）
，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 - ！
）;


这种方式一次只能处理一个 事务可以锁定同一个父项。所以不会发生多个交易删除同一个父母的孩子，仍然看到其他孩子和家长，而所有的孩子都不在了。 （非键列上的更新仍然允许使用 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屋！