Citus 报错（八）ERROR: modifying the partition value of rows is not allowed

ERROR: modifying the partition value of rows is not allowed

如上报错

jydb@jydb=> begin;
BEGIN
jydb@jydb=*> UPDATE
jydb@jydb-*>    MF_CalmarRatio B
jydb@jydb-*> SET
jydb@jydb-*>    B.InnerCode  = A.InnerCode,
jydb@jydb-*>    B.IndexCode = A.IndexCode,
jydb@jydb-*>    B.IndexName = A.IndexName,
jydb@jydb-*>    B.IndexCycle = A.IndexCycle,
jydb@jydb-*>    B.EndDate = A.EndDate,
jydb@jydb-*>    B.DataValue = A.DataValue,
jydb@jydb-*>    B.UpdateTime = current_timestamp,
jydb@jydb-*>    B.JSID = nextval('JYDB.jsid')
jydb@jydb-*> FROM
jydb@jydb-*>    JYDB.MF_CalmarRatio_TMP A
jydb@jydb-*> WHERE
jydb@jydb-*>    A.InnerCode = B.InnerCode
jydb@jydb-*>    AND A.IndexCode = B.IndexCode
jydb@jydb-*>    AND A.EndDate = B.EndDate
jydb@jydb-*>    AND (COALESCE(A.InnerCode,-1) != COALESCE(B.InnerCode,-1)
jydb@jydb(*>          OR COALESCE(A.IndexCode,-1) != COALESCE(B.IndexCode,-1)
jydb@jydb(*>          OR COALESCE(A.IndexName,'') != COALESCE(B.IndexName,'')
jydb@jydb(*>          OR COALESCE(A.IndexCycle,-1) != COALESCE(B.IndexCycle,-1)
jydb@jydb(*>          OR COALESCE(A.EndDate,'1990-01-01') != COALESCE(B.EndDate,'1990-01-01')
jydb@jydb(*>          OR CAST(coalesce(A.DataValue, -1) as DECIMAL(18, 9)) != cast(coalesce(B.DataValue, -1) as DECIMAL(18, 9)));
ERROR:  modifying the partition value of rows is not allowed
jydb@jydb=!> 

通过查看SQL逻辑跟去掉分片键是等价的

UPDATE
   MF_CalmarRatio B
SET
   -- B.InnerCode  = A.InnerCode,
   B.IndexCode = A.IndexCode,
   B.IndexName = A.IndexName,
   B.IndexCycle = A.IndexCycle,
   B.EndDate = A.EndDate,
   B.DataValue = A.DataValue,
   B.UpdateTime = current_timestamp,
   B.JSID = nextval('JYDB.jsid')
FROM
   JYDB.MF_CalmarRatio_TMP A
WHERE
   A.InnerCode = B.InnerCode
   AND A.IndexCode = B.IndexCode
   AND A.EndDate = B.EndDate
   AND (COALESCE(A.InnerCode,-1) != COALESCE(B.InnerCode,-1)
         OR COALESCE(A.IndexCode,-1) != COALESCE(B.IndexCode,-1)
         OR COALESCE(A.IndexName,'') != COALESCE(B.IndexName,'')
         OR COALESCE(A.IndexCycle,-1) != COALESCE(B.IndexCycle,-1)
         OR COALESCE(A.EndDate,'1990-01-01') != COALESCE(B.EndDate,'1990-01-01')
         OR CAST(coalesce(A.DataValue, -1) as DECIMAL(18, 9)) != cast(coalesce(B.DataValue, -1) as DECIMAL(18, 9)));

此问题通过判断SQL逻辑等价解决，如果去掉更新的列，不等价，那么只能另寻办法！

ERROR: functions used in UPDATE queries on distributed tables must not be VOLATILE

去掉之后报如上错误

select proname, provolatile from pg_proc where proname in ('nextval');
alter function nextval() strict stable;

这个错误通常是由于在更新分布式表时使用了VOLATILE函数所导致的。

在Citus中，VOLATILE函数是指在相同的参数输入下，可能返回不同结果的函数。由于Citus是一个分布式数据库，它需要确保在分布式表上的操作在所有分片之间是一致的，因此不能使用VOLATILE函数。

要解决这个问题，可以考虑使用IMMUTABLE或STABLE函数来代替VOLATILE函数。IMMUTABLE函数是指在相同的参数输入下，总是返回相同结果的函数，而STABLE函数是指在相同的参数输入下，可能返回不同结果，但是返回结果不依赖于任何数据库中的可变状态。

如果无法避免使用VOLATILE函数，则可以考虑在应用程序中计算并存储结果，然后将结果用于更新分布式表。

举个例子，如果您的更新查询类似于这样：

UPDATE distributed_table SET column = random() WHERE ...

可以使用一个存储过程来计算随机值，如下所示：

CREATE OR REPLACE FUNCTION get_random() RETURNS double precision AS $$
  SELECT random();
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE PROCEDURE update_distributed_table() AS $$
DECLARE
  rand_value double precision;
BEGIN
  SELECT get_random() INTO rand_value;
  UPDATE distributed_table SET column = rand_value WHERE ...;
END;
$$ LANGUAGE plpgsql;

CALL update_distributed_table();

这个存储过程计算随机值并将其用于更新分布式表，同时避免了使用VOLATILE函数。