主页 / coding / 问题 / 79596885
Accepted
dan-kli
Asked: 2025-04-29 00:13:46 +0800 CST

在触发器中锁定一行

  • 772

我有两张 PostgreSQL 表：objects和metadata。每个对象都有一个size属性，表示其大小（以字节为单位），并且可以存储在一个唯一的位置storage_id。元数据中维护了total_size每个存储中所有对象的 ，每个存储的 都具有给定的storage_id。简化表格：

CREATE TABLE IF NOT EXISTS objects (
  object_id UUID PRIMARY KEY,
  storage_id UUID NOT NULL,
  size BIGINT NOT NULL,
  FOREIGN KEY (storage_id) REFERENCES metadata(storage_id)
);

CREATE TABLE IF NOT EXISTS metadata (
  storage_id UUID PRIMARY KEY,
  total_size BIGINT DEFAULT 0
);

为了维护表total_size中的内容metadata，我有一个触发器，每当插入或删除对象时，total_size就会更新，即这里是插入的触发器：

CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  UPDATE metadata
  SET total_size = total_size + NEW.size
  WHERE storage_id = NEW.storage_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_insert
AFTER INSERT ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_insert();

问题是并发插入/删除操作可能会覆盖 ，total_size从而导致数据无效。如何更改触发器，使其在 执行时包含行级SET total_size = total_size + NEW.size锁？我考虑过FOR UPDATE语句，但这需要一个SELECT。我可以使用PERFORM ... FOR UPDATE;来锁定行吗？

sql

1 个回答

  1. Best Answer

    我可以用来PERFORM ... FOR UPDATE;锁定行吗？

    update可以，但那只是复制了你已经获取的锁，正如@Iłya Bursov立即指出的那样。你可以在db<>fiddle的演示中看到它。pg_locks

    start transaction;
create table t(v int primary key);
insert into t values(1),(2);
commit and chain;
update t set v=9 where v=1;
select mode
     , relation::regclass
     , pid=pg_backend_pid() as is_me
from pg_locks
join pg_database on pg_locks.database   = pg_database.oid
                and pg_database.datname = current_catalog
where relation::regclass in ('t','t_pkey');
    模式 关系 是我
    行排他锁 t_pkey 吨
    行排他锁 吨 吨

    一个在表上，另一个在索引上强制执行主键。

    正如@Frank Heikens所暗示的，您可能希望将 换成upsert ，以确保安全地处理初始插入，并添加以涵盖@Charlieface建议的任何更新和删除：updatecoalesce()

    CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO metadata AS m(storage_id,total_size)
  VALUES(coalesce(NEW.storage_id,OLD.storage_id),coalesce(NEW.size,0))
  ON CONFLICT(storage_id)DO UPDATE
  SET total_size = m.total_size 
                  +coalesce(NEW.size,0) 
                  -coalesce(OLD.size,0);
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_insert
AFTER INSERT OR UPDATE OR DELETE ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_insert();

    要将其保留为after触发器，您需要更改 FK 约束。同时，请考虑添加一个CHECK约束，以确保没有人会滑入负尺寸的物体：

    size BIGINT NOT NULL CHECK(size>=0),
  FOREIGN KEY (storage_id) REFERENCES metadata(storage_id) DEFERRABLE INITIALLY DEFERRED

    可延迟性使得触发器有时间storage_id在约束验证之前添加新内容。如果没有可延迟性，触发器就必须触发before。

    db<>fiddle 上的演示

    insert into objects values
 (gen_random_uuid(),gen_random_uuid(),1)
,(gen_random_uuid(),gen_random_uuid(),1)
returning*;
    对象 ID 存储 ID 尺寸
    35176f69-0f1f-4051-b903-9bb436c26b23 4d3d8298-0f2a-415f-b9a5-2784dafed066 1
    bfda9791-2e11-4998-b98f-5b674b647a16 63d59ab2-de3a-442f-b05d-7708fb8d61dd 1

    现在元数据已经自动填充了：

    table metadata;
    存储 ID 总大小
    4d3d8298-0f2a-415f-b9a5-2784dafed066 1
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 1

    将对象添加到现有存储：

    insert into objects 
select gen_random_uuid()
     , storage_id
     , 9 as size
from objects
limit 1 --any existing storage will do
returning*;
    对象 ID 存储 ID 尺寸
    B60B5742-45E0-4118-97AB-FD60B915E9C2 4d3d8298-0f2a-415f-b9a5-2784dafed066 9 
    table metadata;
    存储 ID 总大小
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 1
    4d3d8298-0f2a-415f-b9a5-2784dafed066 10 个
    新对象增加了总大小

    更新任意一个对象的大小：

    update objects 
set size=5
where ctid=(select min(ctid)from objects)
returning*;
    对象 ID 存储 ID 尺寸
    35176f69-0f1f-4051-b903-9bb436c26b23 4d3d8298-0f2a-415f-b9a5-2784dafed066 5 
    table metadata;
    存储 ID 总大小
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 1
    4d3d8298-0f2a-415f-b9a5-2784dafed066 14 
    delete from objects
where ctid=(select min(ctid)from objects)
returning*;
    对象 ID 存储 ID 尺寸
    bfda9791-2e11-4998-b98f-5b674b647a16 63d59ab2-de3a-442f-b05d-7708fb8d61dd 1 
    table metadata;
    存储 ID 总大小
    4d3d8298-0f2a-415f-b9a5-2784dafed066 14
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 0

    或者，您可以扩展触发器，以便同时处理删除total_size降至零的未使用存储总数。

    要处理truncate事件，您还需要一个单独的语句级触发器 - 即使它删除了表的所有行，它也不会触发on delete触发器。

    @Adrian Klaver的view好事值得考虑：

    1. 触发器会减慢速度，特别是行级触发器，尤其是那些必须锁定事物的触发器。
    2. 如果表不是很大和/或您不经常需要这些总数，那么最好容忍数据库在这些时间运行新的扫描和聚合的一些额外的时间和精力，而不是减慢其上的所有 CRUD 操作。
    3. 如果您不需要完全新鲜、最新、精确和准确的总数，但您确实需要快速获得它们，您可以这样做materialized view并定期缓存它们。
    • 1

相关问题