在以下语句完成之前，不提交CTE删除 [英] CTE delete not committed until following statements complete

问题描述

我遇到的问题是，删除的数据稍后仍会出现在同一查询中。自然，在完全独立的查询中，不会显示已删除的数据。

The problem I'm having is that deleted data still appears later in the same query. Naturally, in a completely separate query, the deleted data does not appear.

这不是我的用例，但我认为这是显示以下内容的最简单方法问题：

This isn't my use-case, but I think this it's the simplest way to show the problem:

CREATE TABLE company (id INT PRIMARY KEY, name TEXT);
CREATE TABLE employee (id INT PRIMARY KEY, company_id INT REFERENCES company(id), name TEXT);

INSERT INTO company VALUES (1, 'first company');
INSERT INTO company VALUES (2, 'second company');

INSERT INTO employee VALUES (1, 1, 'first employee');
INSERT INTO employee VALUES (2, 2, 'second employee');

-- this select can successfully query for the data which has just been deleted
WITH deleted_employee AS (DELETE FROM employee WHERE id = 1 RETURNING id)
SELECT id, name FROM employee JOIN deleted_employee USING (id);

-- this select shows it has been deleted
SELECT * FROM employee;

我把它放在小提琴琴中此处。

I've put it into a fiddle here.

似乎是 DELETE 直到整个查询完成才提交code>，这感觉很奇怪，因为优先级要求 Delete 出现在 SELECT之前。

It seems the DELETE just isn't committed until the whole query has completed, which feels strange since the precedence requires that the DELETE occurs before the SELECT.

有没有办法在单个查询中实现？

Is there any way to achieve this in a single query?

编辑

答案已经回答了直接的问题。潜在的问题是，如果没有更多与该公司关联的员工，则删除该员工然后删除其关联的公司。

The answers have answered the direct problem. The underlying problem is to delete an employee then delete its associated company, if there are no more employees associated with that company.

这是我可以做到的查询：

Here's the query I though would do the trick:

WITH affected_company AS (DELETE FROM employee WHERE id = 1 RETURNING company_id)
DELETE FROM company
USING affected_company
WHERE NOT EXISTS (
  SELECT 1
  FROM employee
  WHERE company_id = affected_company.company_id
);

SELECT * FROM company;
SELECT * FROM employee;

和更新的小提琴。

您可以看到该公司没有被删除。

You can see the company isn't being deleted.

推荐答案

这是预期的并有文档记录。

This is expected and documented.

手册中的语录

WITH中的子语句是彼此并发执行的，主要查询。因此，当在WITH中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用相同的快照执行（请参见第13章），因此它们无法看到彼此对目标表的影响。这减轻了行更新实际顺序的不可预测性的影响，并且意味着RETURNING数据是在不同的WITH子语句与主查询之间传递更改的唯一方法

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" one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query

^{（强调我的意思）}

您可以使用链接的CTE删除公司：

You can use chained CTEs to delete the company:

with deleted_emp as (
  delete from employee 
  where id = 1 
  returning company_id, id as employee_id
)
delete from company
where id in (select company_id from deleted_emp) 
  and not exists (select * 
                  from employee e
                     join deleted_emp af 
                       on af.company_id = e.company_id 
                      and e.id <> af.employee_id) 

重要的是将刚刚删除的员工从不存在中排除子查询ll在第二个delete语句中始终可见，因此不存在永远不会为真。因此，子查询从本质上检查是否存在除分配给同一公司的已删除员工以外的其他员工。

It's important to exclude the just deleted employee from the not exists sub-query as that will always be visible in the second delete statement and thus the not exists would never be true. So the sub-query essentially checks if there is an employee other than the deleted one assigned to the same company.

在线示例： https://rextester.com/IVZ78695

这篇关于在以下语句完成之前，不提交CTE删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！