Rules mess with queries. For data copying/archiving kinds of tasks,
triggers are a better bet, like you suggested in your original post.
Let me put that a different way: rules can *only* be used where data
integrity is not at stake. My own thinking is that it might be time to
make an official recommendation that they are only safe for views.
NEW and OLD mean different things in a PL/pgSQL context and a Rules
context.
In PL/pgSQL NEW and OLD are values, in Rules (which specifically mess
with
queries) they are expressions.
The fact that the same words mean different things in different contexts
is a bit unfortunate but not as messy as say using "NEWEXPR" in the
Rules context would be.
Since we now have UPDATE/INSERT/DELETE RETURNING, one could imagine the
rules using these to access the actual rows and not the expressions...
But there is a perfectly valid argument against that :
- There already is a mechanism designed specifically for this purpose
(triggers).
- It works perfectly.
- Rules are supposed to rewrite queries to do stuff like views.
It should be mentioned in the docs, though : someone with an account on
the PG site should copypaste this mail exchange in the comments field...
