[2.10.2] Ignore dependencies having onDelete="SET NULL" when computing commit order for removal operations

[DoobleD]

Feature Request

Q	A
New Feature	yes
RFC	?
BC Break	no

Summary

TLDR: Doctrine could find a valid delete commit order in some circular references cases if it were ignoring dependencies with onDelete="SET NULL".

I have 3 entities with the following relationships:

• A contains a OneToMany to B with cascade={"remove"}.
• B contains a OneToMany to C with cascade={"remove"}.
• A also contains a OneToOne to C with no inverse side, nullable=true and onDelete="SET NULL".

When I remove A via the entity manager, I get a foreign key violation error because the commit order Doctrine computes is B, C, A. Indeed B can't be deleted first since it is referenced by C.

I expected the order to be C, B, A instead. That wouldn't violate FK constraints. I eventually realized I have a circular reference (A references C and C references A via B) and that Doctrine doesn't know how to deal with it.

The thing is, the circular reference is not really one when deleting, because of onDelete="SET NULL". If Doctrine were to ignore such dependencies on delete, the commit order would end up correct.

Is there any chance this could be implemented?

Disclaimer: I'm not very familiar with Doctrine and DB engines in general, there could be downsides I'm not seeing.

More precisely what happens

UnitOfWork::getCommitOrder correctly lists the dependencies for each nodes:

• Node C with dep A,
• Node B with dep C,
• Node A with deps B and C (in that order, because B is the first entity encountered which is the owning side of A).

The order in which nodes are listed (C, B, A), comes from UnitOfWork::doRemove's recursion putting the most deeply nested child first in the deletion list, and working back towards the parents.

Note in particular the dep A in C. It comes from the OneToOne relationship between A and C (the one with onDelete="SET NULL").

A bit later in CommitOrderCalculator::visit (which computes the actual commit order from the dependency list):

• C is processed first.
• Then A (being a dependency of C).
• Then B (being the first dependency of A).
• C, the dependency of B, is in progress already so there's no new recursion on it.
• B is added to the sorted list.
• Being done with B we bubble back to A but its next dependency C is in progress already so we bubble back to C.
• C is added to the sorted list (it has no other dependency).
• Finally A, of which all dependencies have been visited already, is added to the sorted list.

Thus the order of CommitOrderCalculator::sortedNodeList ends up being: B, C, A. That order is reversed then again is used in reversed (i.e. original order).

When dependencies are first listed, Doctrine could ignore the dep A in C because that dep has onDelete="SET NULL". The whole process would then compute a valid commit order C, B, A.

[DoobleD]

Another way of making this work would be to support deferrable constraints, but Doctrine seems to have issues with that.

[DoobleD]

Another (probably bad) workaround is to add onDelete="CASCADE" on the ManyToOne link to B in C. That seems to work, though I keep cascade={"remove"} on the OneToMany side because I need lifecycle events.

[DoobleD]

I ended up removing all onDelete annotations and set deferred constraints on all my foreign keys instead.

This is much simpler to manage, and hopefully #3423 will get solved soon (there seem to be a PR with recent activity at least).

[mpdude]

Could you please test if #10547 solves the problem for you?