viernes, 31 de enero de 2014

Registros duplicados en PostgreSQL (y otros sistemas)

Introducción


¿Has tenido que localizar registros repetidos en una base de datos?

--CREATE TABLE test(first INTEGER, second TEXT, third TEXT);
 first | second | third 
-------+--------+----------
     0 | zero   | REPEATED
     0 | zero   | REPEATED
     0 | zero   | REPEATED
-------+--------+----------

O peor aún, ¿has tenido que borrar registros diferentes con la Primary Key repetida?

CREATE TABLE test(first INTEGER, second TEXT, third TEXT, PRIMARY KEY (first, second));
 first | second | third 
-------+--------+-----------
     0 | zero   | OK
     0 | zero   | REPEATED1
     0 | zero   | REPEATED2
-------+--------+-----------

Estarás pensando que eso es imposible... pues créeme, aunque no pueda demostrártelo, pasa. Y si te pasa, vas a querer saber como arreglarlo.

Algunas de las cosas que comento aquí son específicas de PostgreSQL, pero otras son estándar o utilizables en otros sistemas de bases de datos como SQLite.

Registros o Primary Key repetidos


Si en SQL se define una tabla sin Primary Key, se pueden almacenar registros repetidos en ella.

La experiencia me ha demostrado que incluso habiendo definido una Primary Key en una tabla, los servidores PostgreSQL (al menos en su versión 8.3) tienen algún fallo por el que se corrompe el índice utilizado para representarla, llegando a aparecer varios registros con la misma Primary Key.

No puedo reproducir el fallo, pero si buscas en Internet cosas como:
  • postgresql corrupt primary key
  • postgresql duplicate primary key
te saldrán bastantes enlaces, en los que podrás leer que hay varias personas a las que también les ha ocurrido.

¿Cómo localizar esos registros?


Recuerda esta consulta SQL estándar:

SELECT todas_las_columnas_de_la_primary_key_y_otra_columna_cualquiera_mas_si_la_hay
FROM tabla
GROUP BY todas_las_columnas_de_la_primary_key_y_otra_columna_cualquiera_mas_si_la_hay
HAVING (COUNT(*) > 1)
;

En caso de que la tabla no tenga Primary Key, habrá que agrupar por todas las columnas de la tabla.

La idea es simple: la consulta sólo mostrará aquellos registros donde las columnas por las que se agrupe presenten valores repetidos. Como la Primary Key está incluida en el GROUP BY, el COUNT debería ser siempre 1.

¿Cómo borrar todas las ocurrencias repetidas menos una?


Lo más normal, cuando se tengan registros repetidos, será quedarse con una única ocurrencia. El problema está en distinguir los registros, por ejemplo para borrarlos. Para ello, en PostgreSQL se puede echar mano de las columnas de sistema:

PostgreSQL 9.2.6 Documentation - Chapter 5. Data Definition - 5.4. System Columns
  • Si la tabla tiene OID, es muy fácil distinguir los registros, ya que se trata de un identificador interno distinto para cada fila (a mi no se me ha dado el caso de que se repita, pero a saber).
  • Siempre se puede recurrir al CTID (incluso si la tabla no tiene OID). El CTID indica la posición física de una fila de la tabla. Este valor puede variar en cuanto se realicen modificaciones sobre la fila o la tabla, por lo que NO se debe considerar un valor persistente, siendo útil sólo a corto plazo.
Esas columnas están ocultas, pero se puede acceder a ellas si se citan de forma explícita.

Si lo que se ha duplicado es la Primary Key pero no todo el registro, debería ser posible borrar directamente los registros que no interesen con una sentencia DELETE poniendo en la cláusula WHERE tantas condiciones como se necesiten para identificar unívocamente las filas:

DELETE FROM test WHERE (first = 0) AND (second = 'zero') AND (third <> 'OK');

Si lo que se ha duplicado es el registro completo, es más complicado:


--PostgreSQL.
--Para borrar todos los repetidos menos uno usando CTID.
DELETE FROM test
WHERE ctid NOT IN (SELECT min(ctid) FROM test GROUP BY first, second, third)
;

--PostgreSQL o SQLite.
--Para borrar todos los repetidos menos uno usando OID.
DELETE FROM test
WHERE oid NOT IN (SELECT min(oid) FROM test GROUP BY first, second, third)
;

Según el caso, otra posibilidad podría ser borrar todos los repetidos e insertar luego una única ocurrencia, dado que los registros están completamente repetidos (son iguales).

Problemas a tener en cuenta


Si se ha definido una tabla sin Primary Key ni restricciones UNIQUE, en teoría no puede haber Foreign Keys apuntando a ella, por lo que no puede haber registros dependientes. Sin embargo, sí podría haber TRIGGERs por ejemplo.

Sin embargo, si el problema es que se han duplicado Primary Keys, sí podría haber haber registros dependientes, por lo que al borrar hay que tener eso en cuenta. No puedo asegurar que pasará en tal caso con los registros dependientes, pero casi seguro que se borrarán al borrar la primera ocurrencia que se encuentre en la tabla principal.

Incluso al borrar, el sistema puede hacer uso de índices en vez de barrer la tabla completa. Esto puede ser un problema añadido, ya que si el índice que implementa la Primary Key está corrupto, el DELETE no funcionará como es de esperar. Por ello, es interesante provocar una sentencia que fuerce un barrido directo sobre la tabla evitando el uso de índices.

Una cláusula GROUP BY que contenga alguna columna que no sea de la PRIMARY KEY y no esté indexada debería provocar dicho barrido. En PostgreSQL se puede utilizar la sentencia EXPLAIN para comprobar si se está utilizando algún índice o no. Además, en PostgreSQL se puede desactivar temporalmente el uso de índices con:

SET enable_indexscan TO OFF;

Ejemplos


En este caso, no puedo demostrar gran cosa porque no es posible reproducir el fallo de duplicar valores en la Primary Key. De todos modos, he puesto algunos ejemplos para ilustrar algunas de las cosas citadas en el artículo:


El ejemplo en SQLite puedes probarlo pegando el código en esta web:


Conclusiones


A veces pasan cosas que no deberían pasar, y en esos casos, no está de más tener algo de información para salir del atolladero.

La forma de localizar repetidos es sencilla, pero para identificarlos y borrarlos hay que tener mucho cuidado y prestar atención a los detalles particulares de la situación.

Enlaces


Enlaces de interés relacionados con este artículo:
En esos enlaces, entre otras cosas hay más detalles sobre el tema de este artículo, otras soluciones a los mismos problemas y otras consultas útiles para PostgreSQL.

(Actualizado 06/02/2015)


5 comentarios: