将h2/mysql查询转换为postgres/cockroach

flseospp 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(401)

我想将以下查询从h2/mysql转换为postgres/cockroach，这是公认的错误：

SET @UPDATE_TRANSFER= 
(select count(*) from transfer where id=‘+transfer_id+' and consumed=false)>0;

update balance_address set balance = 
case when @UPDATE_TRANSFER then balance +
     (select value from transaction where transfer_id=‘+id+' and t_index=0) 
else balance end where address = 
     (select address from transaction where transfer_id=‘+id+' and t_index=0)

此查询涉及三个表：balance\u address、bundle和transaction。查询的目的是在发生资金转移时更新总余额。
一次转移可以将多个事务捆绑在一起。例如，假设保罗的账户里有20美元，他想给简寄3美元。这将导致4笔交易：一笔向简的账户中添加3美元，一笔从保罗账户中删除20美元，一笔将保罗账户更改为0，一笔将保罗剩余资金放入新地址的交易；仍然属于他。
整个传输包中的每个事务都有一个索引和一个值。如你所见。因此，这个更新查询的目标是更新jane的帐户。
挑战在于，这种传输可以由多个服务器并行处理，并且没有分布式锁。所以，如果我们天真地并行处理，每个服务器都会增加jane的帐户，导致错误的结果。
为了防止出现这种情况，balance\u address表有一个名为consummed的列。第一个更新余额的服务器将传输设置为consumed=true。其他服务器或线程只能在consumered为false时更新。
所以，我的目标是1）改进这个查询，2）重写它以使用海报。现在，变量构造还没有被接受。
另外，我无法更改数据模型。

mysql postgresql cockroachdb h2

来源：https://stackoverflow.com/questions/50796064/converting-h2-mysql-query-to-postgres-cockroach

1条答案

按热度按时间

jobtbby31#

蟑螂数据库没有变量，但是 @UPDATE_TRANSFER 变量只使用一次，因此您只需内联替换子查询：

update balance_address set balance = 
    case 
        when (select count(*) from transfer where id=$1 and consumed=false)>0 
        then balance + (select value from transaction where transfer_id=$1 and t_index=0) 
        else balance
    end 
    where address = 
     (select address from transaction where transfer_id=$1 and t_index=0)

但这并不能确定 consumed 旗帜。最简单的方法是在客户机应用程序中使其成为一个多步骤事务：

num_rows = txn.execute("UPDATE transfer SET consumed=true 
    WHERE id=$1 AND consumed=false", transfer_id)
if num_rows == 0: return
value, address = txn.query("SELECT value, address FROM transaction 
    WHERE transfer_id=$1 and t_index=0", transfer_id)
txn.execute("UPDATE balance_address SET balance = balance+$1 
    WHERE address = $2", value, address)

在postgresql中，我认为您可以使用公共表表达式将其放入一个大语句中。然而，cockroachdb2.0只支持cte的一个子集，我认为在cockroach中使用cte还不可能做到这一点。

赞(0）回复(0）举报 2021-06-21

我来回答

将h2/mysql查询转换为postgres/cockroach

1条答案

相关问题

热门标签

最新问答