更新报错ERROR：Non-deterministic UPDATE

问题现象

执行update语句报错ERROR：Non-deterministic UPDATE。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE public.t1(a int, b int) WITH(orientation = column);
CREATE TABLE

CREATE TABLE public.t2(a int, b int) WITH(orientation = column);
CREATE TABLE

INSERT INTO public.t1 VALUES (1, 1);
INSERT INTO public.t2 VALUES (1, 1),(1, 2);

UPDATE t1 SET t1.b = t2.b FROM t2 WHERE t1.a = t2.a;
ERROR: Non-deterministic 
UPDATEDETAIL:  multiple updates to a row by a single query for column store table.

原因分析

当一条SQL语句中同一个元组被多次更新，执行便会报错ERROR：Non-deterministic UPDATE。

可以看到更新操作分成两步执行：

1. 通过关联操作查找满足更新条件的元组。
2. 执行更新操作。

针对上述案例，对于表public.t1中元组 (1, 1)来说，表public.t2中满足更新条件t1.a = t2.a的记录有两条，分别为(1, 1)， (1, 2)；按照执行器逻辑表t2的元组 (1, 1)需要被更新两次，那么就可能出现两种情况：

1. 表public.t1和表public.t2关联时先命中(1, 1)，再命中(1, 2)，这时public.t1的元组(1, 1)，先被更新为(1,1)，再被更新为(1,2)，最终结果为(1, 2)。
2. 表public.t1和表public.t2关联时先命中(1, 2)，再命中(1, 1)，这时public.t1的元组(1, 1)，先被更新为(1,2)，再被更新为(1,1)，最终结果为(1, 1)。

实际执行过程中public.t2表输出结果集的顺序会影响update语句的最终输出结果（实际业务中表public.t2的位置可能是一个非常复杂的子查询），导致了update语句执行结果的随机性，而这个实际业务中是无法接受的。

解决方案

建议根据业务实际情况调整update语句。比如分析public.t2的字段含义，确定更新的目标字段。针对上述案例，如果期望在a值相等的情况下，把public.t1中字段b更新为public.t2中的最大值，那么可以修改为如下逻辑：

1
2
3
4
5
6
7

UPDATE t1 SET t1.b = t2.b_max FROM (SELECT a, max(b) AS b_max FROM t2 GROUP BY a) t2 WHERE t1.a = t2.a;
UPDATE 1
SELECT * FROM public.t1;
 a | b
---+---
 1 | 2
(1 row)