HP Vertica ERROR 3149: Duplicate primary/unique key detected in join

Hoje fazendo a carga de alguns arquivos para meu DW me deparei com uma mensagem de erro do Vertica dizendo que havia primary keys duplicadas.

Achei muito estranho, então separei os arquivos imaginando que seria algum problema no arquivo que estava sendo carredo e que depois eu voltaria a analisá-los.

Mas quando fui rodar meu dashboard recebi o mesmo em uma das consultas SQL definidas.

Pesquisando vi que o Vertica no momento da carga não verifica as constraints no momento de carga para priorizar a performance, ou seja, você poderia por exemplo incluir dois registros com a mesma primary key.

Mas o erro acontece no momento em que você utiliza esses registros, como por exemplo um JOIN com a tabela que possui uma primary key duplicada.

Simulando o erro

> TABLE public.table1 (id int PRIMARY KEY, val char(1));

> INSERT INTO public.table1 VALUES (1, 'a');

> INSERT INTO public.table1 VALUES (1, 'b');

> CREATE TABLE public.table2 (id int PRIMARY KEY, val char(1));

> INSERT INTO public.table2 VALUES (1, 'c');

> select * from public.table1 a inner join public.table2 b on (a.id = b.id);

ERROR 3149:  Duplicate primary/unique key detected in join [(public.table2 x public.table1) using table2_super and table1_super (PATH ID: 1)]; value [1]

Veja como o Vertica permitiu a inserção de duas primary keys iguais e só reclamou durante um JOIN com a tabela.

Corrigindo o problema

A documentação diz que você deverá executar o comando ANALYZE_CONSTRAINTS nas tabelas para exibir os registros duplicados.

Na nosso exemplo acima, a mensagem de erro já diz o nome de algumas tabelas, então vamos verificá-las.

> SELECT ANALYZE_CONSTRAINTS ('public.table1');

 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values 
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | table1     | id           | C_PRIMARY       | PRIMARY         | ('1')

> SELECT ANALYZE_CONSTRAINTS ('public.table2');

 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values 
-------------+------------+--------------+-----------------+-----------------+---------------
(0 rows)

Analisando os resultados podemos ver que na table1 existem registros com a primary key de valor 1 duplicada.

E cada registro no Vertica tem uma coluna epoch escondida que representa a data epoch de quando o registro foi carregado/inserido. Se os dois registros foram carregados em momentos diferentes então o epoch pode ser usado como um identificador único para deletar um registro e manter o outro. Caso tenham o mesmo epoch você terá que deletar todos os registros e inserir um novo registro manualmente.

> select epoch, * from public.table1;

  epoch  | id | val 
---------+----+-----
 1793982 |  1 | a
 1793982 |  1 | b
(2 rows)

> delete from public.table1 where epoch = 1793982 and id = 1 and val = 'b';

 OUTPUT 
--------
      1
(1 row)

> select epoch, * from public.table1;

  epoch  | id | val 
---------+----+-----
 1793982 |  1 | a
(1 rows)

E agora tudo funcionando como realmente deveria ser

> select * from public.table1 a inner join public.table2 b on (a.id = b.id);

 id | val | id | val 
----+-----+----+-----
  1 | a   |  1 | c
(1 row)