I’ve made this challenge on IRC several times, and I think on the mailing lists too, but I’m putting it up here for reference.
I’m not a fan of rules. I’ve used them in production code in the past, though I’d avoid doing so again, and (unlike most people who end up using them) have spent a fair bit of time analyzing their behaviour and effects. I’ve come to the following conclusion:
All non-trivial rules are incorrect.
I define “non-trivial” as follows:
- Any rule with multiple action statements is non-trivial.
- Any rule using DO ALSO is non-trivial.
- Any rule with a WHERE clause is non-trivial.
This pretty much only leaves rules which rewrite into at most one simple statement, which I’ll concede can sometimes be written correctly (but often are not).
I define “incorrect” as “produces surprising or destructive behaviour in response to ordinary SQL statements”. In general this requires SQL statements not forseen by the rule writer; it’s easy to write rules that work only for certain statements, but doing this in real code amounts to laying a minefield for your future developers.
My challenge is simple: show me a counterexample to the above claims. Any takers?
[EDIT: to clarify, by “rule with a WHERE clause” I mean a conditional rule with WHERE used in the rule definition, not merely a rule whose action statement has a WHERE clause in it.]