(For background I'm using Postgres 12.4)
I'm unclear why deletes work when there are circular FKs between two tables and both FKs are set to 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;
The way that I am thinking about this, when you delete the row in table 'a' with PK id = 5, postgres looks at tables that have a referential constraint referencing a(id), it finds b, it tries to delete the row in table b with id = 10, but then it has to look at tables referencing b(id), so it goes back to a, and then it should just end up in an infinite loop.
But this does not seem to be the case. The delete completes without error. It's also not the case, as some sources say online, that you cannot create the circular constraint. The constraints are created successfully, and neither of them is deferrable.
So my question is - why does postgres complete this circular cascade even when neither constraint is set to deferrable, and if it's able to do so, then what is the point of even having a DEFERRABLE option?
Foreign key constraints are implemented as system triggers.
For ON DELETE CASCADE, this trigger will run a query like:
/* ----------
* 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.
* ----------
*/
The query runs is a new database snapshot, so it cannot see rows deleted by previous RI triggers:
/*
* 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.
*/
This makes sure that no RI trigger will try to delete the same row a second time, and thus circularity is broken.
(All quotations taken from src/backend/utils/adt/ri_triggers.c.)