Início / coding / Perguntas / 79596885
Accepted
dan-kli
Asked: 2025-04-29 00:13:46 +0800 CST

Bloquear uma linha em um gatilho

  • 772

Tenho duas tabelas PostgreSQL: objectse metadata. Cada objeto tem uma sizepropriedade que representa seu tamanho em bytes e pode ser armazenado em um local exclusivo com storage_id. Nos metadados, a total_sizepropriedade de todos os objetos de cada armazenamento com um dado storage_idé mantida. Tabelas simplificadas:

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
);

Para manter o total_sizena metadatatabela, tenho gatilhos que sempre que um objeto é inserido ou excluído, ele total_sizeé atualizado, ou seja, aqui está o gatilho para inserção:

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();

O problema é que inserções/exclusões simultâneas podem sobrescrever o comando, o total_sizeque leva a dados inválidos. Como posso alterar o gatilho para que ele contenha um bloqueio em nível de linhaSET total_size = total_size + NEW.size quando o comando for executado? Eu estava pensando na FOR UPDATEinstrução, mas isso requer um comando SELECT. Posso usar PERFORM ... FOR UPDATE;para bloquear a linha?

sql

1 respostas

  1. Best Answer

    Posso usar PERFORM ... FOR UPDATE;para bloquear a linha?

    Você poderia, mas isso apenas duplicaria o bloqueio que você updatejá adquiriu por conta própria, como imediatamente apontado por @Iłya Bursov . Você pode ver isso em pg_locks:
    demo em db<>fiddle

    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');
    modo relação sou eu
    RowExclusiveLock t_pkey t
    RowExclusiveLock t t

    Uma está na mesa, a outra no índice, reforçando sua chave primária .

    Conforme sugerido por @Frank Heikens , você pode querer trocar o updatepor um upsert , para garantir também que suas inserções iniciais sejam tratadas com segurança, e adicionar um coalesce()para cobrir quaisquer atualizações e exclusões, conforme recomendado por @Charlieface :

    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();

    Para deixá-lo como aftergatilho, você precisará alterar a restrição FK. Aproveite e considere uma CHECKrestrição para garantir que ninguém insira objetos de tamanho negativo:

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

    A diferibilidade permite que o gatilho tenha tempo para adicionar a nova storage_idrestrição antes que ela seja validada. Sem isso, o gatilho teria que ser disparado before.

    demonstração no db<>fiddle

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

    Agora os metadados foram preenchidos automaticamente:

    table metadata;
    id_de_armazenamento tamanho_total
    4d3d8298-0f2a-415f-b9a5-2784dafed066 1
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 1

    Adicionando um objeto ao armazenamento existente:

    insert into objects 
select gen_random_uuid()
     , storage_id
     , 9 as size
from objects
limit 1 --any existing storage will do
returning*;
    ID do objeto id_de_armazenamento tamanho
    b60b5742-45e0-4118-97ab-fd60b915e9c2 4d3d8298-0f2a-415f-b9a5-2784dafed066 9 
    table metadata;
    id_de_armazenamento tamanho_total
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 1
    4d3d8298-0f2a-415f-b9a5-2784dafed066 10
    novos objetos aumentaram o tamanho total

    Atualizando o tamanho de qualquer objeto arbitrário:

    update objects 
set size=5
where ctid=(select min(ctid)from objects)
returning*;
    ID do objeto id_de_armazenamento tamanho
    35176f69-0f1f-4051-b903-9bb436c26b23 4d3d8298-0f2a-415f-b9a5-2784dafed066 5 
    table metadata;
    id_de_armazenamento tamanho_total
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 1
    4d3d8298-0f2a-415f-b9a5-2784dafed066 14 
    delete from objects
where ctid=(select min(ctid)from objects)
returning*;
    ID do objeto id_de_armazenamento tamanho
    bfda9791-2e11-4998-b98f-5b674b647a16 63d59ab2-de3a-442f-b05d-7708fb8d61dd 1 
    table metadata;
    id_de_armazenamento tamanho_total
    4d3d8298-0f2a-415f-b9a5-2784dafed066 14
    63d59ab2-de3a-442f-b05d-7708fb8d61dd 0

    Opcionalmente, você pode expandir o gatilho para também remover os totais de armazenamento não utilizado que total_sizeforam reduzidos a zero.

    Para manipular um truncateevento, você também precisa de um gatilho de nível de instrução separado - mesmo que isso exclua todas as linhas de uma tabela, ele não dispara on deletegatilhos.

    @Adrian Klaver é viewuma boa coisa a se considerar:

    1. Os gatilhos tornam as coisas lentas, especialmente os de nível de linha, principalmente aqueles que precisam bloquear coisas.
    2. Se a tabela não for muito grande e/ou você não precisar desses totais com muita frequência, pode ser uma ideia melhor tolerar algum tempo e esforço adicionais para que o banco de dados execute uma nova varredura e agregue nesses momentos, do que tornar lentas todas as operações CRUD nele.
    3. Se você não precisa de totais completamente novos, atualizados, precisos e exatos, mas precisa deles rapidamente, você pode fazer isso materialized viewe armazená-los em cache periodicamente.
    • 1

relate perguntas