PostgreSQL 更新并返回

Question

假设我有一个名为t的表，它在Postgres中：

   id   | group_name | state
-----------------------------
   1    |   group1   |   0
   2    |   group1   |   0
   3    |   group1   |   0

我需要通过ID更新一行的state，同时返回以下内容：

• 旧状态
• 与该行处于同一组且state = 0的剩余行数

我有一个如下的查询语句可以实现这个功能：

UPDATE t AS updated SET state = 1
FROM t as original
WHERE 
    updated.id = original.id AND
    updated.id = :some_id
RETURNING
    updated.state AS new_state,
    original.state AS old_state,
    (
        SELECT COUNT(*) FROM t 
        WHERE 
            group_name = updated.group_name AND
            state = 0
    ) as remaining_count;

然而，在RETURNING中的子查询似乎是在更新完成之前执行的，导致我得到了一个偏差为1的remaining_count。

此外，我不确定当并发查询运行时它会如何表现。如果我们同时更新这两行，它们是否可能返回相同的remaining_count？

有没有更优雅的解决方案？也许是一些窗口/聚合函数？

- SuperTron

---

为什么不使用存储过程/触发器呢？ - Vinay

我发现长期维护存储过程很困难。在走这条路之前，想在这里问一下 :) - SuperTron

Answer 1

子查询确实是在不看到UPDATE的更改的情况下运行的，因为它在UPDATE提交之前运行，因此它是不可见的。尽管如此，这很容易修复；只需在子查询中添加一个where子句来过滤掉您刚刚更新的ID，使您的查询类似于这样：

UPDATE t AS updated SET state = 1
FROM t as original
WHERE 
    updated.id = original.id AND
    updated.id = :some_id
RETURNING
    updated.state AS new_state,
    original.state AS old_state,
    (
        SELECT COUNT(*) FROM t 
        WHERE 
            group_name = updated.group_name AND
            state = 0 AND
            t.id <> :some_id /* this is what I changed */
    ) as remaining_count;

就并发性而言，说实话我不确定其行为会是什么样子；我能做的最好的事情就是指向相关文档。

- AdamKG

---

看起来并发方面相当复杂。我已经重新设计了这一部分，使用Redis来跟踪剩余计数。感谢提供文档链接！ - SuperTron

Answer 2

你可以尝试使用非递归的WITH查询，也就是通用表达式（CTEs）。它们的一般结构如下：

WITH auxiliary_query_name AS (
    auxiliary_query_expression;
)
[, WITH ...] 
primary_query_expression;

通常情况下，auxiliary_query_expression和primary_query_expression会同时运行，如果它们引用相同的基础表，则结果是不可预测的。但是，您可以从primary_query_expression中引用auxiliary_query_name，以及其他辅助查询，从而强制执行运行顺序，其中引用查询必须等待被引用的查询完成。可能还有一些细节需要注意，但这就是要点。CTE还具有仅计算一次的优点。

关于您的查询，假设您最终想要的是更新项目的ID、旧状态、新状态、所属组以及该组中剩余要更新的其他项目数量，我认为以下内容可以实现这一目标。我稍微修改了原始查询，以便一次更新多个项目，以展示这种方法的优越性（除了明显的顺序外，如果您一次只更新一个项目，则其性能优势是无意义的）。

WITH updated_t AS (
    UPDATE t AS updated SET state = 1
    FROM t as original
    WHERE 
        updated.id = original.id AND
        updated.id in :array_of_IDs -- I changed this 
    RETURNING
        updated.id,
        original.state AS old_state,
        updated.state AS new_state,
        updated.group_name
),
WITH remaining AS (
    SELECT t.group_name, count(*) as remaining_count
    -- we need to JOIN then filter out the updated rows because
    -- all WITH in a statement share the same snapshot, thus have
    -- the same starting "view" of base tables.
    FROM t LEFT JOIN updated_t
        ON t.id = updated_t.id
    WHERE updated_t.id is NULL
        AND t.group_name in (SELECT DISTINCT group_name from updated_t)
        AND t.state = 0
    GROUP BY group_name
)
SELECT 
    updated_t.id, 
    updated_t.group_name, 
    updated_t.old_state, 
    updated_t.new_state, 
    remaining.remaining_count
FROM updated_t, remaining
WHERE 
    updated_t.group_name = remaining.group_name;

- Michael Ekoka