鸟笼逻辑，惯性思维不可取
xiongcc约 3 分钟勿陷“慣性思維”泥潭
首先抛一个问题，假如现在有如下两个表，其中 t1 表有一个复合主键，t2 表没有主键
postgres=# \d t1Table "public.t1"Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+---------id | integer | | not null |id2 | integer | | not null |info | text | | |Indexes:"t1_pkey" PRIMARY KEY, btree (id, id2)postgres=# \d t2Table "public.t2"Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+---------id | integer | | |id2 | integer | | |info | text | | |postgres=# select * from t1;id | id2 | info----+-----+-------1 | 2 | orig11 | 3 | orig22 | 3 | orig3(3 rows)postgres=# select * from t2;id | id2 | info----+-----+------1 | 2 | new11 | 2 | new21 | 3 | new32 | 3 | new4(4 rows)
假如现在有一个关联更新语句，根据 t2 表的值去更新 t1 表：
UPDATE t1 AS tgtSET info = src.infoFROM t2 AS srcWHERE tgt.id = src.idAND tgt.id2 = src.id2;
那么请问，最终 t1 表中的 orig1 值是 new1 还是 new2？
这还不简单，包括我也是拍脑袋地认为最终值肯定是 new2！如果这样，那么恭喜你，你掉坑了！我们知道，在 PostgreSQL 中，表是可以关联更新的，根据某个表按照一定的关联条件去更新目标表，如果说按照惯性思维 —— 认为匹配到了多行就更新多次，那么再次恭喜你，你又错了。在官网上有这么一段话，https://www.postgresql.org/docs/current/sql-update.html[1]
When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.
使用 FROM 时，应确保关联对每个要修改的行最多生成一个输出行。换句话说，目标行不应关联到其他表中的多个行。如果发生这种情况，则只有其中一个连接行会用于更新目标行，但具体使用哪一行则难以预测。
也就是说，最终目标表只会更新一次，并且更新的是哪一行也不确定。哪一条生效取决于实际扫描顺序，比如数据插入顺序、VACUUM/CLUSTER、是否走索引等都会改变扫描顺序。我们可以搭配 RETUNING 观察是哪一行被选中：
UPDATE t1 AS tgtSET info = src.infoFROM t2 AS srcWHERE tgt.id = src.idAND tgt.id2 = src.id2RETURNING tgt.id, tgt.id2, src.ctid AS picked_src_row, src.info AS picked_value;
回到最开始提供的例子，各位可以自行验证，最终结果是 new1 (走了 HASH JOIN，顺序扫描 t1、t2)
postgres=# select * from t1;id | id2 | info----+-----+------1 | 2 | new11 | 3 | new32 | 3 | new4(3 rows)
如果我们换个顺序，换一下写入顺序
postgres=# DELETE FROM t2 WHERE id = 1 AND id2 = 2;DELETE 2postgres=# INSERT INTO t2 VALUESpostgres-# (1,2,'new2'), -- 先插 new2postgres-# (1,2,'new1'); -- 再插 new1INSERT 0 2postgres=# select * from t1;id | id2 | info----+-----+------1 | 3 | new32 | 3 | new41 | 2 | new2(3 rows)
最终的结果就会变成 new2 了。所以实际更新的结果是不可预期的，不可依赖此行为，若要确定性结果，应先去重或聚合。
小结
鸟笼逻辑，惯性思维不可取，勿陷“慣性思維”泥潭，小心这个关联更新的坑。
References
[1]: https://www.postgresql.org/docs/current/sql-update.html