The Rule Challenge
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.]
2010-06-22 at 03:43:46
Rules work all right for views (ie, ON SELECT DO INSTEAD SELECT cases). There’s no question that every other case sucks. The *real* question is how to do better?
2010-06-22 at 04:41:57
That is indeed the real question, but it’s one for another blog post rather than this one :-)
2010-06-22 at 12:30:56
I’m not principally a PostgreSQL folk, DB2 is my main squeeze, but I do follow it a bit. The whole rules thing is something only PG did, and that was Stonebraker’s doing. One would have to read up his rationale way back when. From what I can find, View support is the justification for using rules, since that is the only way to get it. There have been a few links from the PostgreSQL site to rule posts such as this recently. I wonder why rules have become a topic du jour?
The challenge as written isn’t provable; the counterexample is exhaustive enumeration of all non-trivial rules.
As to better, one of those linked posts asserts that triggers are better. I would agree with that.
2010-06-22 at 17:36:48
We’ve had more than the usual number of rules questions on IRC recently, at least that’s my impression; that would probably explain the multiple recent blog posts. (And I only wrote this one because someone on IRC bugged me about it.)