Eliminaciones en cascada en PostgreSQL

Tengo una database con algunas docenas de tablas interconectadas con keys externas. En circunstancias normales, quiero que el comportamiento pnetworkingeterminado ON DELETE RESTRICT para esas restricciones. Pero cuando intentaba compartir una instantánea de la database con un consultor, necesitaba eliminar algunos datos confidenciales. Desearía que mi memory de un command DELETE FROM Table CASCADE no hubiera sido pura alucinación.

Lo que terminé haciendo fue volcar la database, escribir un script para procesar el volcado añadiendo cláusulas ON DELETE CASCADE también todas las restricciones de key externa, restaurar desde eso, realizar mis eliminaciones, volcar de nuevo, eliminar ON DELETE CASCADE y finalmente restaurar de nuevo. Eso fue más fácil que escribir la consulta de borrado que hubiera necesitado para hacer esto en SQL: eliminar partes enteras de la database no es una operación normal, por lo que el esquema no está exactamente adaptado.

¿Alguien tiene una mejor solución para la próxima vez que surja algo como esto?

No necesita volcar y restaurar. Debería poder soltar la restricción, rebuildla con cascada, hacer sus eliminaciones, soltarla de nuevo y rebuildla con restricción.

 CREATE TABLE "header" ( header_id serial NOT NULL, CONSTRAINT header_pkey PRIMARY KEY (header_id) ); CREATE TABLE detail ( header_id integer, stuff text, CONSTRAINT detail_header_id_fkey FOREIGN KEY (header_id) REFERENCES "header" (header_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); insert into header values(1); insert into detail values(1,'stuff'); delete from header where header_id=1; alter table detail drop constraint detail_header_id_fkey; alter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id) REFERENCES "header" (header_id) on delete cascade; delete from header where header_id=1; alter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id) REFERENCES "header" (header_id) on delete restrict; 

Puede crear las restricciones de key externa como DEFERRABLE. Luego podrá deshabilitarlos temporalmente mientras friega los datos y los vuelve a habilitar cuando termina. Eche un vistazo a esta pregunta .

 TRUNCATE table CASCADE; 

Soy un principiante de Postgres, así que no estoy seguro de cuál es la desventaja para TRUNCATE vs. DROP.

Es posible que desee examinar el uso de esquemas con PostgreSQL. Lo hice en proyectos anteriores para permitir que diferentes grupos de personas o desarrolladores tengan sus propios datos. Luego puede usar sus scripts para crear múltiples copys de su database solo para tales situaciones.

@ Tony: No, los esquemas pueden ser útiles, y de hecho, los usamos para dividir los datos en nuestra database. Pero estoy hablando de tratar de eliminar datos confidenciales antes de permitir que un consultor tenga una copy de la database. Quiero que esos datos se hayan ido.

No creo que necesites procesar el file de volcado de esa manera. Realice un volcado / restauración de transmisión y procese eso. Algo como:

 createdb -h scratchserver scratchdb createdb -h scratchserver sanitizeddb pg_dump -h liveserver livedb --schema-only | psql -h scratchserver sanitizeddb pg_dump -h scratchserver sanitizeddb | sed -e "s/RESTRICT/CASCADE/" | psql -h scratchserver scratchdb pg_dump -h liveserver livedb --data-only | psql -h scratchserver scratchdb psql -h scrachserver scratchdb -f delete-sensitive.sql pg_dump -h scratchserver scratchdb --data-only | psql -h scratchserver sanitizeddb pg_dump -Fc -Z9 -h scratchserver sanitizedb > sanitizeddb.pgdump 

donde almacena todos sus files SQL DELETE en delete-sensitive.sql. La database / pasos de sanitizeddb puede eliminarse si no le importa que el asesor obtenga un file de database con las keys externas CASCADE en lugar de las keys externas RESTRICT.

También podría haber mejores forms dependiendo de la frecuencia con la que necesite hacer esto, cuán grande es la database y qué porcentaje de datos es confidencial, pero no puedo pensar en una forma más sencilla de hacerlo una o dos veces para un tamaño razonable database. Necesitarás una database diferente después de todo, así que, a less que ya tengas un clúster slony, no puedes evitar el ciclo de volcado / restauración, que puede llevar mucho time.

TRUNCATE simplemente elimina los datos de la tabla y abandona la estructura