@aman Thanks for this! This actually turned up something very valuable. By default, Hibernate depends on the database-level cascade. However, for some database dialects that don’t support this (like MySQL running with the MyISAM storage engine—which doesn’t even support foreign keys), it has a fallback: issuing multiple delete statements, as I showed when running against MySQL.
So the short answer to your question is that we need to ensure that the foreign key between Patient -> Person is setup with the ondelete property set to cascade. This should be addressed through a Liquibase changeset, which should be applied to (at a minimum) the patient table, the concept_numeric table, the concept_complex table, and the test_ordertable (these tables are all similarly joined sub-classes of other classes and so will have the same referential problem).
Now the more interesting part: OpenMRS actually goes out of it’s way to ensure that we are not using MyISAM as the storage engine. And the InnoDB storage engine supports foreign keys and even foreign keys with ondelete set to cascade. So why hasn’t this been an issue before? Well, Hibernate defaults to using the MyISAM dialect for MySQL databases. Consequently, we’ve been running InnoDB tables, but using the optimizations for MyISAM rather than InnoDB. This results in sub-optimal behaviour, like the multiple delete thing and Hibernate not disabling foreign key checks where appropriate (since MyISAM doesn’t support foreign keys). But it’s not broken.
This brings up a question, which I think is for the community as a whole: should we switch (in 2.4.0 and beyond only) to using the InnoDB dialect for Hibernate or leave things as they are? If so, we should create a changeset to address this not just for PostgreSQL, but for MySQL as well. If not, well I guess we’re ok. Thoughts?
@burke @dkayiwa @mseaton @mogoodrich @mksd
(Parenthetically, I’d also ask why DrugOrders, unlike TestOrders are not setup to cascade-delete when the parent order is deleted, but I suspect this is just an oversight because deleting is a very rare action).