Replace ElementPath exclusion constraints with triggers
Refs https://redmine.teklia.com/issues/4234#note-9
Benchmark
I added a parent to a top-level element that has 200K child elements, which should cause 200K calls to the ElementPath triggers, with a database that was causing the issues found in #1521 (closed) and https://redmine.teklia.com/issues/4234.
| Query | master | !2020 (merged) |
|---|---|---|
| Get the current state in one SELECT | 146.035501s | 0.037321s |
| Update the new child's own paths | 0.007515s | 0.003572s |
| Update the child's children's recursively | 350.797377s | 325.720756s |
The large difference on the SELECT is due to the query planner not using any of the GiST indexes for the exclusion constraints since they don't exist. The triggers do not seem to cause much of a difference during path management, and only trigger on INSERT and UPDATE on the paths which only occur during path management, so the only significant change is the removal of the GiST indexes, which we already have done in demo and we know makes things very faster.