Início / dba / Perguntas / 291767
Accepted
bluenote10
Asked: 2021-05-19 02:24:35 +0800 CST

Como lidar com duplicatas em um UPDATE em uma chave primária no Postgres?

  • 772

Vamos supor um cenário com as entidades Persone Companyuma tabela PersonCompanyStocksque modela quantas ações uma pessoa possui de uma determinada empresa (cardinalidade N:M). Por exemplo:

person | company | num_stocks
-----------------------------
Alice  | foo     | 300
Bob    | foo     | 100
Bob    | bar     | 200

Esta tabela usa (person, company)como chave primária para garantir entradas exclusivas e chaves estrangeiras para a respectiva tabela pessoa/empresa (estou usando apenas IDs de string para simplificar).

Agora vamos supor que a empresa barcompra a empresa foo. Queremos atualizar a tabela de forma que ela se torne:

person | company | num_stocks
-----------------------------
Alice  | bar     | 300
Bob    | bar     | 300

Observar apenas Aliceo registro de 's sugere usar uma abordagem ingênua como:

UPDATE
  PersonCompanyStocks
SET
  company = "bar"
WHERE
  company = "foo"

No entanto, esta atualização falha duplicate key value violates unique constraint ...porque Bobjá existe uma linha com a chave ("Bob", "bar"). INSERTO Postgres de For suporta ON CONFLICT DO ..., mas parece que não há equivalente para UPDATE. E claramente também temos que lidar com a fusão adequada do num_stockvalor das duas linhas.

Qual é a melhor estratégia para abordar este problema? Eu só vejo uma solução relativamente feia:

  • Uma consulta para determinar as duplicatas.
  • Um UPDATEpara mesclar as duplicatas na linha final.
  • Um DELETEpara remover as duplicatas ofensivas.
  • O acima UPDATEpara fazer a renomeação em linhas sem duplicatas.

Isso parece complexo e provavelmente é propenso a condições de corrida. O Postgres oferece algum truque para resolver isso de forma mais elegante?

postgresql primary-key

2 respostas

  1. Best Answer

    Precisamos mover ações entre empresas, certo? O que significa que precisamos adicionar estoques aos usuários existentes e alterar a empresa para novos usuários. Ou, o mesmo, podemos excluir todas as ações da empresa "foo" e insert .. on conflictnovas linhas para a empresa "bar"

    with rows as (                                             
  delete from PersonCompanyStocks 
    where company = 'foo'
    returning person, num_stocks
)
insert into PersonCompanyStocks (person, company, num_stocks) 
  select person, 'bar', num_stocks from rows
  on conflict(person,company) do update set 
    num_stocks = PersonCompanyStocks.num_stocks + excluded.num_stocks;

    Transacional, sem condições de corrida aqui graças ao bloqueio de linha durante a exclusão.

    • 4

  2. Seu procedimento é bom. Para evitar condições de corrida, faça tudo em uma única transação e SELECT ... FOR UPDATEtodas as linhas que você pretende modificar (bloqueio pessimista).

    • 1

relate perguntas