在 PostgreSQL 中9.1 或更高版本您可以使用单个语句来完成此操作数据修改CTE https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING。这通常不太容易出错。它最小化两次 DELETE 之间的时间范围,其中竞争条件并发操作可能会导致令人惊讶的结果:
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 FROM child c
WHERE c.parent_id = x.parent_id
AND c.child_id <> x.child_id -- !
);
fiddle https://dbfiddle.uk/z3Zi907E
Old sqlfiddle http://www.sqlfiddle.com/#!17/7e89d5/1
无论如何,孩子都会被删除。我引用手册 https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING:
数据修改语句WITH
只执行一次,并且总是要完成,与主查询是否读取无关
他们的所有(或者实际上是任何)输出。请注意,这是不同的
从规则SELECT
in WITH
: 正如上一节所述,
执行一个SELECT
仅进行到主查询
要求其输出。
仅当父级没有时才会被删除other孩子们。
请注意最后一个条件。与人们的预期相反,这是必要的,因为:
中的子语句WITH
被处决同时与彼此
以及主要查询。因此,在使用数据修改时
中的陈述WITH
,指定更新实际的顺序
发生的事情是不可预测的。所有语句都以相同的方式执行
快照(参见第13章 https://www.postgresql.org/docs/current/mvcc.html),所以他们无法“看到”彼此的效果
在目标表上。
大胆强调我的。
我使用了列名parent_id
代替非描述性的id
.
消除竞争条件
消除可能的竞争条件完全地, 锁定父行first。所有类似的操作都必须遵循相同的程序才能起作用。
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 FROM child c
WHERE c.parent_id = l.parent_id
AND c.child_id <> l.child_id -- !
);
只能这样one一次事务可以锁定同一个父级。因此,不会发生多个事务删除同一父级的子级,但仍然看到其他子级并保留父级,而所有子级随后都消失的情况。 (仍然允许更新非键列FOR NO KEY UPDATE
.)
如果这种情况从未发生或者您可以忍受它(几乎从未)发生 - 第一个查询会更便宜。否则,这是安全路径。
FOR NO KEY UPDATE
是随 Postgres 9.4 引入的。手册中有详细说明。 https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS在旧版本中使用更强的锁FOR UPDATE
反而。