GaussDB(DWS)运维 -- 常见问题分析 -- 更新报错ERROR：Non-deterministic UPDATE

【问题根因】

一条SQL语句中同一个元组被多次更新，详细以下面case为例说明

postgres=# CREATE TABLE public.t1(a int, b int) WITH(orientation = column);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
postgres=# CREATE TABLE public.t2(a int, b int) WITH(orientation = column);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
postgres=# INSERT INTO public.t1 VALUES (1, 1);
INSERT 0 1
postgres=# INSERT INTO public.t2 VALUES (1, 1);
INSERT 0 1
postgres=# INSERT INTO public.t2 VALUES (1, 2);
INSERT 0 1
postgres=# UPDATE t1 SET t1.b = t2.b FROM t2 WHERE t1.a = t2.a;
ERROR: Non-deterministic UPDATE
DETAIL:  multiple updates to a row by a single query for column store table.

查看语句的执行计划

postgres=# EXPLAIN VERBOSE UPDATE t1 SET t1.b = t2.b FROM t2 WHERE t1.a = t2.a;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
  id |                                    operation                                    | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+---------------------------------------------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Row Adapter                                                                 |      1 |            |          |      32 | 22.37
   2 |    ->  Vector Streaming (type: GATHER)                                          |      1 |            |          |      32 | 22.37
   3 |       ->  Vector Update on public.t1                                            |      2 |            | 16MB     |      32 | 22.21
   4 |          ->  Vector Streaming(type: RESTORE)                                    |      2 |            | 2MB      |      32 | 16.20
   5 |             ->  Vector Nest Loop (6,8)                                          |      2 |            | 1MB      |      32 | 16.18
   6 |                ->  Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST)     |      1 |            | 2MB      |      18 | 1.09
   7 |                   ->  CStore Scan on public.t1                                  |      1 |            | 1MB      |      18 | 1.00
   8 |                ->  Vector Materialize                                           |      2 |            | 16MB     |      18 | 15.09
   9 |                   ->  Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) |      2 |            | 2MB      |      18 | 15.09
  10 |                      ->  CStore Scan on public.t2                               |      2 |            | 1MB      |      18 | 15.00

          Predicate Information (identified by plan id)
 ---------------------------------------------------------------
   5 --Vector Nest Loop (6,8)
         Join Filter: (t1.a = t2.a)
         Skew Join Optimized by Statistic
   6 --Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST)
         Skew Filter: (t1.a = 1)
   9 --Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)
         Skew Filter: (t2.a = 1)

                       Targetlist Information (identified by plan id)
 -------------------------------------------------------------------------------------------
   2 --Vector Streaming (type: GATHER)
         Node/s: All datanodes
   4 --Vector Streaming(type: RESTORE)
         Output: t1.a, t2.b, t1.a, t1.ctid, t1.tableoid, t1.xc_node_id, t2.ctid, t2.tableoid
         Spawn on: All datanodes
         Consumer Nodes: All datanodes
   5 --Vector Nest Loop (6,8)
         Output: t1.a, t2.b, t1.a, t1.ctid, t1.tableoid, t1.xc_node_id, t2.ctid, t2.tableoid
   6 --Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST)
         Output: t1.a, t1.ctid, t1.tableoid, t1.xc_node_id
         Distribute Key: t1.a
         Spawn on: All datanodes
         Consumer Nodes: All datanodes
   7 --CStore Scan on public.t1
         Output: t1.a, t1.ctid, t1.tableoid, t1.xc_node_id
   8 --Vector Materialize
         Output: t2.b, t2.ctid, t2.tableoid, t2.a
   9 --Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN)
         Output: t2.b, t2.ctid, t2.tableoid, t2.a
         Distribute Key: t2.a
         Spawn on: All datanodes
         Consumer Nodes: All datanodes
  10 --CStore Scan on public.t2
         Output: t2.b, t2.ctid, t2.tableoid, t2.a

   ====== Query Summary =====
 -------------------------------
 System available mem: 3112960KB
 Query Max mem: 3112960KB
 Query estimated mem: 9751KB

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

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

针对上述case，对于表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语句执行结果的随机性，而这个实际是不可忍受的。

【解决方案】

建议根据业务实际情况解决，比如分析public.t2的字段含义，确定更新的目标字段。比如如上业务，我们如果期望在a值相等的情况下，把public.t1中字段b更新为public.t2中的最大值，那么可以修改为如下逻辑

postgres=# 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
postgres=# SELECT * FROM public.t1;
 a | b
---+---
 1 | 2
(1 row)

【注意事项】

1) . 针对上述场景，对于行存表，DWS默认不报错，保持和PostgreSQL一致的行为

postgres=# CREATE TABLE public.t1(a int, b int) WITH(orientation = row) DISTRIBUTE BY HASH(a);
CREATE TABLE
postgres=# CREATE TABLE public.t2(a int, b int) WITH(orientation = row) DISTRIBUTE BY HASH(a);
CREATE TABLE
postgres=# INSERT INTO public.t1 VALUES (1, 1);
INSERT 0 1
postgres=# INSERT INTO public.t2 VALUES (1, 1);
INSERT 0 1
postgres=# INSERT INTO public.t2 VALUES (1, 2);
INSERT 0 1
postgres=# UPDATE t1 SET t1.b = t2.b FROM t2 WHERE t1.a = t2.a;
UPDATE 1
postgres=#

但是可以设置参数behavior_compat_options，增加属性配置disable_row_update_multi，让行存达到和列存一样的效果

postgres=# SET behavior_compat_options = 'disable_row_update_multi';
SET
postgres=# UPDATE t1 SET t1.b = t2.b FROM t2 WHERE t1.a = t2.a;
ERROR:  dn_6001_6002: unable to get a stable set of rows in the source tables