Modificar la tabla de Postgres para tener una restricción de unicidad cuando ya existen registros duplicados.
Estamos utilizando Django ORM respaldado por Postgres.
Tenemos una tabla (“foo”) con registros duplicados que queremos modificar y agregar una restricción única para evitar la duplicación.
Para hacer esto, primero necesitamos borrar los registros duplicados y luego agregar la restricción única (de lo contrario, la adición de la restricción usando “alter table” fallará debido a la duplicación de registros).
Quiero ejecutar la siguiente transacción para implementar el flujo descrito anteriormente:
BEGIN WORK;
DELETE FROM foo
WHERE id IN
(SELECT id
FROM
(SELECT id,
ROW_NUMBER() OVER( PARTITION BY a1, a2
ORDER BY id ) AS row_num
FROM foo ) t
WHERE t.row_num > 1 );
LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM foo
WHERE id IN
(SELECT id
FROM
(SELECT id,
ROW_NUMBER() OVER( PARTITION BY a1, a2
ORDER BY id ) AS row_num
FROM foo ) t
WHERE t.row_num > 1 );
ALTER TABLE "foo" ADD CONSTRAINT "some_constraint_name" UNIQUE ("a1", "a2");
COMMIT WORK;
- La parte de borrado está duplicada para minimizar el tiempo en que se adquiere el bloqueo.
- El bloqueo es para bloquear la inserción de duplicados entre la sentencia de borrado y la sentencia de alteración.
Sin embargo, esto falla con el mensaje “no se puede ALTER TABLE porque tiene eventos de disparador pendientes”, lo cual tiene sentido supongo.
Si es así, ¿qué puedo hacer para lograr la unicidad deseada? No es posible copiar los datos a una nueva tabla debido a la cantidad de datos que contiene la tabla…
davy.ai
Si la declaración de alteración falla debido a eventos de activación pendientes, una solución podría ser desactivar las activaciones antes de ejecutar la declaración de alteración y luego volver a activarlas. Esto se puede hacer utilizando los siguientes comandos:
Con el comando
DISABLE TRIGGER ALL
, se desactivarán todas las activaciones para la tabla, incluyendo aquellas que puedan estar causando el error de eventos de activación pendientes. Luego, podemos ejecutar la declaración de alteración según lo planeado y volver a habilitar las activaciones utilizando el comandoENABLE TRIGGER ALL
.