Início / dba / Perguntas / 335456
Accepted
Luke Hutchison
Asked: 2024-02-02 16:40:28 +0800 CST

Como atualizar uma linha do Postgres atomicamente, com um valor computado que depende dos novos valores

  • 772

Tenho a seguinte tabela no Postgres:

CREATE TABLE avg (
    user_id INT,
    numer DOUBLE PRECISION,
    denom DOUBLE PRECISION,
    frac DOUBLE PRECISION
);

Quero realizar o seguinte e estou tendo dificuldade em colocar isso em uma consulta. Basicamente quero poder atualizar algumas médias atomicamente, atualizando separadamente o numerador e o denominador, e depois calcular e atualizar a média fracna mesma operação, com base nos novos valores:

  1. Dados os parâmetros ae b, quero encontrar a linha para um determinado user_id, então quero fazer numer += ae denom += bcalcular frac = numer / denoma partir dos novos valores de numere e denom, em seguida, atualizar os valores de numer, denome fracnesta linha.
  2. Porém, se não houver nenhuma linha para o solicitado user_id, ou se houver uma linha, mas os valores de numere denomforem nulos, então quero assumir que o valor dessas somas é zero, ou seja, uma nova linha deve ser inserida em a tabela, ou uma operação de atualização deve ser executada se houver uma linha, mas os valores forem nulos, com o resultado sendo definido numer = a, denom = be frac = a / bpara o user_id.
  3. Quero garantir que tudo isso aconteça atomicamente.

Pode haver vários numeradores e denominadores em uma determinada tabela, daí a necessidade de ser capaz de inserir (se a linha não existir) ou atualizar (se a linha existir, mas os valores iniciais de um par numerador/denominador específico forem nulo).

postgresql

1 respostas

  1. Best Answer

    A melhor prática é não armazenar dados redundantes em um banco de dados. Minha solução seria

    CREATE TABLE real_avg (
    user_id bigint PRIMARY KEY,
    numer double precision,
    denom double precision
);

CREATE VIEW avg
AS SELECT user_id, numer, denom,
          numer / denom AS frac
   FROM real_avg;

    Então você pode realizar a operação desejada da seguinte maneira:

    INSERT INTO avg (user_id, numer, denom)
   VALUES (1, 100, 50)
ON CONFLICT (user_id) DO UPDATE
SET numer = avg.numer + EXCLUDED.numer,
    denom = avg.denom + EXCLUDED.denom;

    Se você insiste em usar uma coluna de tabela redundante, use uma coluna gerada que seja atualizada automaticamente:

    CREATE TABLE real_avg (
    user_id bigint PRIMARY KEY,
    numer double precision,
    denom double precision,
    frac double precision GENERATED ALWAYS AS (numer / denom) STORED
);
    • 1

relate perguntas