Início / dba / Perguntas / 332347
Accepted
Hasan Can Saral
Asked: 2023-10-20 19:13:14 +0800 CST

Qual seria a desvantagem de uma verificação de exclusividade em um gatilho para uma tabela particionada?

  • 772

Eu tinha a seguinte tabela:

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY,
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id)
);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

e quero particioná-lo da transaction_dateseguinte maneira:

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence'),
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT transactions_pkey PRIMARY KEY (id, transaction_date),
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id, transaction_date)
) PARTITION BY RANGE (transaction_date);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

com este particionamento transaction_idnão é verdadeiramente único, mas único por partição, pois a chave exclusiva deve ter a chave de partição, ou seja, transaction_date.

O aplicativo que usa esta tabela não reconhece o particionamento e usa o estado SQL 23505 para executar a atualização em vez de uma inserção.

Estou inserindo aproximadamente 300 mil linhas por dia e o tamanho atual da tabela é de aproximadamente 30 milhões de linhas.

O mesmo ocorre com o seguinte gatilho:

CREATE OR REPLACE FUNCTION transactions_validate() RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF EXISTS (SELECT id FROM transactions WHERE transaction_id = NEW.transaction_id AND id <> NEW.id) THEN
        RAISE EXCEPTION USING ERRCODE = '23505';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER transactions_validate_trigger
    BEFORE INSERT OR UPDATE
    ON transactions
    FOR EACH ROW
EXECUTE PROCEDURE transactions_validate();

Uma má ideia? Quais seriam as implicações de desempenho?

postgresql

1 respostas

  1. Best Answer

    Tente executar as três instruções a seguir em duas sessões simultâneas de banco de dados:

    Sessão 1:

    BEGIN;

INSERT INTO transactions (id, transaction_id, transaction_date)
   VALUES (1, 42, '2020-01-01 00:00:00');

    Sessão 2:

    BEGIN;

INSERT INTO transactions (id, transaction_id, transaction_date)
   VALUES (1, 42, '2023-01-01 00:00:00');

COMMIT;

    Sessão 1:

    COMMIT;

    Contanto que os dois carimbos de data/hora pertençam a partições diferentes, ambas as transações serão bem-sucedidas e violarão a condição de exclusividade desejada.

    Então não, sua solução não é boa, mesmo se você usar gatilhos de restrição diferidos , que estreitam a janela para a condição de corrida. Isso funcionará apenas com o SERIALIZABLEnível de isolamento (que causará erros de serialização que exigirão que você tente novamente as transações) ou bloqueio pesado, o que é ruim para a simultaneidade.

    Normalmente, o caminho a seguir é relaxar as restrições de exclusividade. Caso contrário, você terá que pagar um preço notável no que diz respeito ao desempenho.

    • 1

relate perguntas