主页 / coding / 问题 / 79201009
Accepted
Peter Fence
Asked: 2024-11-19 02:20:07 +0800 CST

一次 CTE INSERT+UPDATE 与两次不同的查询之间有什么区别吗？

  • 772

想象一下这样的桌子

create table public.my_table (
    id          uuid primary key,
    category_id uuid not null,
    name        text not null,
    is_active   bool not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now()
);

有一条规则：一个 category_id 必须只有一个记录is_active=true

我可以想出两种添加记录的方法：

＃1 使用 CTE 插入添加新的活动记录+更新以停用上一个：

WITH new_record AS (
    INSERT INTO my_table (id, category_id, name, is_active) VALUES (?, ?, ?, true)
    RETURNING id, category_id
)
UPDATE my_table
SET is_active  = false,
    updated_at = now()
FROM new_record
WHERE my_table.category_id = new_record.category_id
  AND my_table.id <> new_record.id
  AND my_table.is_active = true

#2 在事务中进行单独的插入和更新查询：

BEGIN TRANSACTION;

INSERT INTO my_table (id, category_id, name, is_active) VALUES (?, ?, ?, true);

UPDATE my_table
   SET is_active  = false,
       updated_at = now()
   WHERE my_table.category_id = ?
         AND my_table.id <> ?
         AND my_table.is_active = true;

COMMIT;

我喜欢第二种方式，因为它简单。

第一种方式相对于第二种方式有什么优势吗？

postgresql

1 个回答

  1. Best Answer

    第一种方式有什么优点吗？

    是的，这是一个原子操作。

    默认情况下，您以read committed事务隔离模式进行操作，这意味着在您的第二个示例中，您的一名工作人员可能会
    在 db<>fiddle 上进行演示

    1. 打开事务，插入新行并将其提升为active。
    2. 与此同时，其他一些工作人员将插入甚至更新的active行并提交它。
    3. 第一个工作者将继续运行update以确保该类别的所有其他行都已停用。此时，它将注意到较新的行并将其停用。因此，一旦提交，您的活动行可能不是最新的，因为较慢的工作者覆盖了较快、最近的信使的工作。
    4. 如果另一个工作者在第一个工作者发出 之前没有提交update，它们都将停用一些较旧的行，之后它们都将提交它们的活动行。因此，您将在一个类别中拥有两个活动行。它可以达到并发客户端的数量。

    您的第一个例子一次性完成了整个插入一个更新另一个操作，在一个快照上，不允许任何人插入任何更改。@Adrian Klaver立即推荐的文档很好地说明了这一点：

    在 中使用数据修改语句时WITH，指定更新的实际发生顺序是不可预测的。所有语句都使用相同的快照执行（参见第 13 章），因此它们无法“看到”彼此对目标表的影响。这减轻了行更新实际顺序不可预测的影响

    如果my_table.category_id确实是指向 主键的外键category.id，最好将其is_active移至category表。 PK 表示它是唯一的，因此搜索类别并更新当前活动的 的指针会更小更快my_table.id：

    create table category(
  id uuid primary key
 ,active_my_table_id uuid references my_table(id)
);

    为了填充它，而不是全部取走where is_active，而是抓取一个distinct on，以防有非活动类别：

    insert into category
select distinct on(category_id)category_id
     , id
from my_table
order by category_id
        ,is_active desc
        ,created_at desc
        ,updated_at desc;

alter table public.my_table 
  drop column is_active cascade--drops the exclusion constraint too
 ,add foreign key(category_id)references category(id);

    然后，为某个类别插入一个新的活动行：

    with new_record as (
    insert into my_table (id, category_id, name) 
    values ( get_row_uuid(row_)
            ,get_category_uuid(category_)
            ,row_)
    returning id, category_id
)
insert into category
select category_id, id
from new_record
on conflict(id)
do update set active_my_table_id = excluded.active_my_table_id

    从技术角度来看，将业务规则表达为实际的 SQL 级约束是可选的。但如果您定义了它，它会自动拒绝任何违规尝试，抛出清晰的错误消息并记录事件，防止表进入无效状态并出现多个活动记录供您跟踪和修复。此外，在上面的配置中，您可以使用它来运行insert..on conflict..do update。

    话虽如此，即使您有单独的表，在其中category添加、维护和操作 128 位uuidfk 显然会比 8 位占用更多空间。如果您希望保持这种状态，可以按照以下@Bergi的建议进行操作： db<>fiddle 上的演示booleanmy_table

    alter table my_table 
  add constraint one_active_per_category
  exclude (category_id with =)
  where (is_active)
  deferrable initially deferred;

    它保持is_active不受影响并防止表格在每个类别中插入超过一个活动行。

    制定约束deferrable initially deferred意味着它只会在交易结束时进行检查，因此您可以先停用然后激活或反过来，只要您最终提交符合规则的状态即可。否则，您需要先停用，然后激活，以避免类别的旧条目和新条目同时具有的时间窗口is_active=true。
    如果您这样做，常规的唯一性会更简单且工作速度更快：

    create unique index on my_table(category_id)
  where (is_active);

    唯一约束和排除约束由索引支持，因此设置索引而不是约束将具有相同的效果。通常可以将索引绑定到表，以便它成为适当的约束，但部分索引不支持这样做。

    如果多个并发工作者尝试该操作，除了一个之外，其他所有工作者都会被锁定并被拒绝，确切的点和错误消息取决于他们是自动尝试还是在具有多个步骤的事务中尝试，以及是否被推迟。在单独的category表中标记活动内容并在一个步骤中执行此操作的优势是，它们将排队等待 - 先到先得。

    • 3

相关问题