3 ms·

how do you do deletions from the closure table?

• phamilton 4y ago
  We have a foreign key constraint in place that prevent any org with children from being deleted. So first, all children must either be orphaned or moved to another parent. We have a trigger on updates that will rematerialize an org's closure rows and then do so for all their descendants. That puts all the former children into a correct state. Then when we delete the organization, we have foreign keys from the closure table to the organizations table and we allow cascading deletes. Our closure table itself contains "evidence" that enables correctness constraints. So a row can only exist if a) it represents a direct parentage (ancestor is the direct parent) or b) ancestor is a parent of my ancestor. This is all enforced via foreign keys, so if the foreign keys are set up correctly an errant row isn't possible. Behind all this is a proptest (property based testing is awesome for stuff like this). We generate arbitrary org forests, perform random mutations (changing parents, deleting orgs, gaining children, etc) and then assert that the closures table is accurate. We have very high confidence that the data is correct because of this test suite.