Listmail wrote:
<snip>
>
>
> 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.
Agreed.
I have narrowed the problem cases down to a subset I think should be
mentioned in the docs.
DO ALSO rules involving NEW are fundamentally dangerous to the integrity
of data because NEW is not guaranteed to be internally consistent. DO
INSTEAD rules are fine (there is only one NEW), as are any DO ALSO rules
involving OLD.
We already protect against programmers using unsafe and non-standard
quote escapes. I have sent in my cases to a number of other people,
some of which are deeply involved in PostgreSQL development, and the
initial behavior was not properly predicted by any of them. This is why
I say that if this is the defined behavior of rules, that a clear and
obvious warning needs to be placed in the docs that this is dangerous
and in every case I can think of, not something you want to use a rule for.
Imagine, for example, that we have an application that is built. Uses
DO ALSO rules with NEW to replicate user-supplied data from one table to
an audit trail or the like, Everything works fine until someone decides
to load up a database with random data. The programmer did not foresee
this and put his trust in PostgreSQL's features for data integrity.
Given the comments I found in the docs, I suspect that people *are*
using DO ALSO rules frequently when these are dangerous. Since this can
cause problems based on user-supplied input, this is a problem.
One of the things that causes me to favor PostgreSQL for all my projects
is the strong emphasis on data integrity by the community, perhaps
better than any other RDBMS out there. Being unwilling to warn clearly
and loudly about unsafe features does undermine that commitment.
>
> 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...
>
For 90% of what I do, I use the local copy of the docs. My concern is
that (at least in 8.1) there is no obvious warning about DO ALSO rules
using NEW to be inherently nondeterministic. I checked the online 8.2
docs and while there was the bit about the expression substitution,
there still was not a warning about this behavior being fundamentally
nondeterministic. I would like to see a note in the section comparing
triggers to rules explaining that this subset of rules is not deterministic.
Best Wishes,
Chris Travers