Re: Rule acting as REPLACE INTO behave strange

On Mon, 13 Jul 2009 01:31:05 +0300, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> You can't do this. The problem in your case is that the INSERT happens,
> and then the rule fires and finds the inserted row, which it happily
> updates. See the manual's explanation:
> http://www.postgresql.org/docs/8.3/static/rules-update.html
> People keep thinking they can use rules for that.

In my opinion, it is caused by the misleading keyword INSTEAD. Most of
the people which do not speak English natively (including me) probably
would decide that this keyword means "throw away the original query and
use the one supplied by the rule". Executing original query and fire up
a rule AFTER that seems to me more like a trigger ....

> Oh, one bit of other advice: don't even *think* of identifying a
> particular row to be updated-rather-than-inserted using a criterion that
> doesn't correspond to a primary key value. Those coalesce conditions
> you're using are guaranteed to make you cry later, because they mean
> that you could have several rows in the table that your logic thinks
> are the same row. You really need a backup constraint to enforce
> that there is only one such row.

Actually I use all columns from the primary key in the rule qualification.
COALESCE() is used because of the ternary binary logic - since my columns
can contain NULLs, and "NULL = NULL" evaluates to NULL - I have to convert
NULLs to zeroes. I have a constraint - to enforce at least one of those
NULL-able columns to be non-NULL. But you are right - it does not save me
from duplicate rows :-( But the BEFORE INSERT trigger does save me :-)

Thanks for your time and the fast response. PostgreSQL is great DBMS and
its community is nice,too.

Best wishes,
IVO GELOV