Início / dba / Perguntas / 341085
Accepted
Eduard Sukharev
Asked: 2024-07-22 19:49:04 +0800 CST

UNIQUE CONSTRAINT em duas colunas, mas apareceram duplicatas: como rastrear e corrigir? [duplicado]

  • 772

Temos tabela para usuários:

CREATE UNIQUE INDEX uk_users_login_socnet ON public.users USING btree (login, socnet)

Tivemos manutenção em nosso servidor, durante a qual foi feito backup e replicado para outro servidor usando pg_basebackup(concluído por nossos administradores de sistema, supostamente sem erros e problemas).

Desde então, temos erros em nossos logs como os seguintes:

ERROR:  duplicate key value violates unique constraint "uk_users_login_socnet"
DETAIL:  Key (login, socnet)=([email protected], 1) already exists. (UPDATE users SET extra = COALESCE(extra, '') || hstore('udid', '') || hstore('udid_last_update', '1721646485') WHERE id = 1234567;)

Enquanto isso, a consulta a seguir não retorna resultados:

SELECT u1.login, u1.socnet
FROM users u1
INNER JOIN users u2 ON u1.login = u2.login AND u1.socnet = u2.socnet

enquanto é EXPLAIN ANALYZE:

Hash Join (cost=2817129.95..6386837.44 rows=9847200 width=18) (actual time=13654.755..31681.796 rows=15584155 loops=1)
    
Hash Cond: (((u1.login)::text = (u2.login)::text) AND (u1.socnet = u2.socnet))
    
-> Seq Scan on users u1 (cost=0.00..2490077.18 rows=15678918 width=18) (actual time=0.026..8806.271 rows=15582973 loops=1)
    
-> Hash (cost=2490077.18..2490077.18 rows=15678918 width=18) (actual time=13653.093..13653.093 rows=15582973 loops=1)
    
Buckets: 524288 Batches: 32 Memory Usage: 28510kB
    
-> Seq Scan on users u2 (cost=0.00..2490077.18 rows=15678918 width=18) (actual time=0.186..10887.626 rows=15582973 loops=1)
    
Planning time: 1.749 ms
    
Execution time: 32166.739 ms

Mas esta consulta:

SELECT t1.login, t1.socnet, t2.login, t2.socnet
FROM users AS t1
LEFT JOIN (SELECT id, login, socnet FROM users) AS t2
ON t2.login = t1.login AND t1.socnet = t2.socnet
WHERE t1.id != t2.id

mostra uma lista de linhas duplicadas com iguais logine socnet, o que para mim não faz sentido. Aqui está o respectivo EXPLAIN ANALYZE:

Hash Join (cost=2817129.95..6411455.44 rows=9847199 width=36) (actual time=17015.349..33466.957 rows=1182 loops=1)
    
Hash Cond: (((t1.login)::text = (users.login)::text) AND (t1.socnet = users.socnet))
    
Join Filter: (t1.id <> users.id)
    
Rows Removed by Join Filter: 15583110
    
-> Seq Scan on users t1 (cost=0.00..2490077.18 rows=15678918 width=22) (actual time=0.034..9902.685 rows=15583110 loops=1)
    
-> Hash (cost=2490077.18..2490077.18 rows=15678918 width=22) (actual time=14344.722..14344.722 rows=15583110 loops=1)
    
Buckets: 524288 Batches: 32 Memory Usage: 30951kB
    
-> Seq Scan on users (cost=0.00..2490077.18 rows=15678918 width=22) (actual time=0.024..11382.363 rows=15583110 loops=1)
    
Planning time: 1.764 ms
    
Execution time: 33467.260 ms

A versão do servidor PostgreSQL é 9.6

Minhas perguntas são:

  • O que está acontecendo? Por que tenho dados duplicados em UNIQUE CONSTRAINT?
  • Por que tenho resultados diferentes para INNER JOINe LEFT JOIN?
  • O que pode ter acontecido com a estrutura do banco de dados e como depurá-la? Onde procurar dicas sobre o que está quebrado?
  • Como corrigir isso para um estado consistente?
postgresql

1 respostas

  1. Best Answer

    Você sofreu corrupção de dados porque o servidor standby promovido estava rodando em uma máquina com uma configuração diferente. Em particular, a biblioteca C deve ter uma versão diferente (provavelmente uma delas glibc < 2.28, a outra glibc >= 2.28). A definição dos agrupamentos nessas duas versões era diferente, portanto, um índice em uma expressão de string foi corrompido no modo de espera.

    Veja este e este artigo para uma explicação mais detalhada.

    Para corrigir a corrupção, você precisa reconstruir todos os índices em strings. Se a reconstrução dos índices levar a um erro, porque você já possui valores duplicados em uma tabela, será necessário excluir os dados até que a inconsistência seja corrigida.

    Certifique-se sempre de replicar entre máquinas com a mesma versão da biblioteca C.

    • 2

relate perguntas