(Para el fondo estoy usando Postgres 12.4)
No tengo claro por qué las eliminaciones funcionan cuando hay FK circulares entre dos tablas y ambos FK están configurados en ON DELETE CASCADE.
CREATE TABLE a (id bigint PRIMARY KEY); CREATE TABLE b (id bigint PRIMARY KEY, aid bigint references a(id) on delete cascade); ALTER TABLE a ADD COLUMN bid int REFERENCES b(id) ON DELETE CASCADE ; insert into a(id) values (5); insert into b(id, aid) values (10,5); update a set bid = 10 where id=5; DELETE from a where id=5;La forma en que estoy pensando en esto, cuando elimina la fila en la tabla 'a' con PK id = 5, postgres mira las tablas que tienen una restricción referencial que hace referencia a a (id), encuentra b, intenta eliminar la fila en la tabla b con id = 10, pero luego tiene que mirar las tablas que hacen referencia a b(id), por lo que vuelve a a, y luego debería terminar en un bucle infinito.
Pero este no parece ser el caso. La eliminación se completa sin errores. Tampoco es el caso, como dicen algunas fuentes en línea, que no puede crear la restricción circular. Las restricciones se crean con éxito y ninguna de ellas es aplazable.
Entonces, mi pregunta es: ¿por qué postgres completa esta cascada circular incluso cuando ninguna de las restricciones está configurada como diferible, y si puede hacerlo, entonces cuál es el punto de tener una opción DIFERIBLE?
Las restricciones de clave externa se implementan como disparadores del sistema.
Para ON DELETE CASCADE , este disparador ejecutará una consulta como:
/* ---------- * The query string built is * DELETE FROM [ONLY] <fktable> WHERE $1 = fkatt1 [AND ...] * The type id's for the $ parameters are those of the * corresponding PK attributes. * ---------- */La consulta que se ejecuta es una nueva instantánea de la base de datos, por lo que no puede ver las filas eliminadas por activadores de RI anteriores:
/* * In READ COMMITTED mode, we just need to use an up-to-date regular * snapshot, and we will see all rows that could be interesting. But in * transaction-snapshot mode, we can't change the transaction snapshot. If * the caller passes detectNewRows == false then it's okay to do the query * with the transaction snapshot; otherwise we use a current snapshot, and * tell the executor to error out if it finds any rows under the current * snapshot that wouldn't be visible per the transaction snapshot. Note * that SPI_execute_snapshot will register the snapshots, so we don't need * to bother here. */Esto garantiza que ningún disparador de RI intente eliminar la misma fila por segunda vez y, por lo tanto, se rompa la circularidad.
(Todas las citas tomadas de src/backend/utils/adt/ri_triggers.c ).