Dante's questions

Eu sei que posso bloquear uma tabela inteira usando LOCK TABLE, mas estou curioso para saber se posso obter o mesmo resultado usando a FOR UPDATEinstrução, supondo que todas as transações que atualizam essa tabela selecionam uma linha específica dessa tabela usando FOR UPDATEno início de sua transação (ou talvez tendo uma tabela separada chamada lockscom uma coluna exclusiva nomeada lock_namee usando SELECT lock_name FROM locks WHERE lock_name = 'foo' FOR UPDATEpara adquirir um bloqueio antes de cada transação)?

Estou fazendo essa pergunta porque preciso salvar uma estrutura de dados de árvore em uma tabela de banco de dados usando uma lista de adjacência ou uma tabela de fechamento. Quando vários usuários tentam adicionar um novo nó ou realocar uma subárvore, preciso selecionar algumas linhas e fazer alguns cálculos e, em seguida, atualizar a tabela com base nesses cálculos e essa operação não é possível a menos que eu bloqueie a tabela inteira.

Tenho duas tabelas em um banco de dados Postgres 17: sitee page. Cada site pode ter várias páginas e cada página pode ter uma página pai:

CREATE TABLE site (
  id INT PRIMARY KEY
);
CREATE TABLE page (
  id INT PRIMARY KEY,
  site_id INT references site(id),
  parent_id INT references page(id)
);

Agora suponha que eu tenha dois sites:

INSERT INTO site (id) VALUES (1);
INSERT INTO site (id) VALUES (2);

E uma página raiz para cada site:

INSERT INTO page (id, site_id, parent_id) VALUES (1, 1, NULL);
INSERT INTO page (id, site_id, parent_id) VALUES (2, 2, NULL);

Como evitar a inserção de uma nova página para o site 2 com uma página pai que pertence ao site 1?

INSERT INTO page (id, site_id, parent_id) VALUES (3, 2, 1);

Como posso fatorar a instrução SELECT comum na função a seguir?

CREATE OR REPLACE PROCEDURE delete_comment(cid integer[]) AS $$
BEGIN
    DELETE FROM comment_tree_path
    WHERE descendant IN (SELECT descendant 
                         FROM comment_tree_path 
                         WHERE ancestor = ANY(cid));
    DELETE FROM comment 
    WHERE comment_id IN (SELECT descendant 
                         FROM comment_tree_path 
                         WHERE ancestor = ANY(cid));
END;
$$ LANGUAGE plpgsql;