为何PostgreSQL中第一条请求未更新bar表，其余请求却正常？

首先看执行的SQL代码：

create table foo (a text);
create table bar (b text);

insert into foo (a) values ('1');
insert into bar (b) values ('1');

select * from bar;

with _ as (update bar set b = '2' from (select * from foo for update) as foo returning b) update foo set a = '1';
select * from bar;

with _ as (update bar set b = '2' from (select * from foo for update) as foo returning b) update foo set a = '1' from _;
select * from bar;

with _ as (update bar set b = '3' from (select * from foo) as foo returning b) update foo set a = '1';
select * from bar;

with _ as (update foo set a = '1') update bar set b = '4' from (select * from foo for update) as foo;
select * from bar;

问题 #

为何第一条带WITH子句的请求未更新bar表，而其余请求却成功更新了？

原因解析 #

1. 第一条语句未更新的核心逻辑：
   第一条WITH子句定义了包含UPDATE bar的CTE，但主查询UPDATE foo SET a = '1'完全没有引用这个CTE。同时，CTE里的UPDATE bar通过FROM (select * from foo for update)对foo表加了排他锁，而主查询本身也在更新foo表。PostgreSQL优化器检测到该CTE执行会引发锁冲突，且其结果对主查询无任何作用，因此直接跳过了CTE中UPDATE bar的执行，导致bar表无变化。

2. 第二条语句执行成功的原因：
   主查询通过from _明确引用了CTE，PostgreSQL必须执行CTE来获取返回结果供主查询使用，因此即使存在锁竞争，也会按顺序执行CTE中的UPDATE bar，最终bar表被更新。

3. 第三条语句执行成功的原因：
   CTE中的UPDATE bar没有使用for update对foo表加锁，不存在锁冲突问题。即使主查询未引用CTE，PostgreSQL也会执行CTE中的UPDATE bar操作，因此bar表被更新。

4. 第四条语句执行成功的原因：
   CTE先执行UPDATE foo完成对foo表的修改，主查询随后通过from (select * from foo for update)获取foo表锁时，不存在锁冲突，因此主查询的UPDATE bar可以正常执行，bar表被更新。

内容的提问来源于stack exchange，提问作者Roman Koshelev