Thread: Rule acting as REPLACE INTO behave strange

Hi.
I am using version 8.3.7 on Fedora Core 4.
I have a rule which mimics REPLACE INTO. The table is defined like this

CREATE TABLE "balans"."bal_price" (
  "acc_id" INTEGER NOT NULL,
  "debit" BIGINT NOT NULL,
  "credit" BIGINT NOT NULL,
  "pero_1" INTEGER,
  "pero_2" INTEGER,
  "pero_3" INTEGER,
  "pero_4" INTEGER,
  "pero_5" INTEGER,
  "beg_date" DATE NOT NULL,
  "end_date" DATE NOT NULL,
  CONSTRAINT "bal_price_balance_uniq_idx" UNIQUE("end_date", "acc_id", "pero_1", "pero_2", "pero_3", "pero_4",
"pero_5","beg_date"),  
  CONSTRAINT "bal_price_chk" CHECK (((((pero_1 IS NOT NULL) OR (pero_2 IS NOT NULL)) OR (pero_3 IS NOT NULL)) OR
(pero_4IS NOT NULL)) OR (pero_5 IS NOT NULL)),  
  CONSTRAINT "balance_chk" CHECK (((debit)::numeric >= (0)::numeric) OR ((credit)::numeric >= (0)::numeric)),
  CONSTRAINT "bal_price_acc_fk" FOREIGN KEY ("acc_id")
    REFERENCES "smetkon"."account"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "bal_price_p1_fk" FOREIGN KEY ("pero_1")
    REFERENCES "smetkon"."pera"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "bal_price_p2_fk" FOREIGN KEY ("pero_2")
    REFERENCES "smetkon"."pera"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "bal_price_p3_fk" FOREIGN KEY ("pero_3")
    REFERENCES "smetkon"."pera"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "bal_price_p4_fk" FOREIGN KEY ("pero_4")
    REFERENCES "smetkon"."pera"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "bal_price_p5_fk" FOREIGN KEY ("pero_5")
    REFERENCES "smetkon"."pera"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;

================================================================

The rule is defined like this

CREATE RULE "bal_price_rl" AS ON INSERT TO "balans"."bal_price"
WHERE (EXISTS ( SELECT 1 FROM balans.bal_price
       WHERE bal_price.acc_id = new.acc_id AND COALESCE(bal_price.pero_1, 0) = COALESCE(new.pero_1, 0)
       AND COALESCE(bal_price.pero_2, 0) = COALESCE(new.pero_2, 0) AND COALESCE(bal_price.pero_3, 0) =
COALESCE(new.pero_3,0)  
       AND COALESCE(bal_price.pero_4, 0) = COALESCE(new.pero_4, 0) AND COALESCE(bal_price.pero_5, 0) =
COALESCE(new.pero_5,0)  
       AND bal_price.beg_date = new.beg_date))
DO INSTEAD (
    UPDATE balans.bal_price SET debit = bal_price.debit + new.debit, credit = bal_price.credit + new.credit
    WHERE bal_price.acc_id = new.acc_id AND COALESCE(bal_price.pero_1, 0) = COALESCE(new.pero_1, 0)
    AND COALESCE(bal_price.pero_2, 0) = COALESCE(new.pero_2, 0) AND COALESCE(bal_price.pero_3, 0) =
COALESCE(new.pero_3,0)  
    AND COALESCE(bal_price.pero_4, 0) = COALESCE(new.pero_4, 0) AND COALESCE(bal_price.pero_5, 0) =
COALESCE(new.pero_5,0)  
    AND bal_price.beg_date = new.beg_date;
);

===============================================================

There is something strange, because the following query causes different result when the table is empty
and when it is not:

  INSERT INTO balans.bal_price(acc_id,pero_1,pero_2,pero_3,pero_4,pero_5,beg_date,end_date,debit,credit)
      SELECT 233,
    CASE WHEN 502<>0 THEN 866 ELSE NULL END,
    CASE WHEN 503<>0 THEN 865 ELSE NULL END,
    CASE WHEN 0<>0 THEN null ELSE NULL END,
    CASE WHEN 0<>0 THEN null ELSE NULL END,
    CASE WHEN 0<>0 THEN null ELSE NULL END,
    '1-7-2009','31-7-2009',1,0;

In the beginning, the table is empty. Then I execute the above query - table has 1 row and column DEBIT has value 2 !!!
If I execute the script again - then DEBIT becomes 3, another execution - becomes 4 and so on.
I do not know how to debug rules in Postgres - the only idea that comes to my mind is "May be the first time both
INSERT
and UPDATE happens". But my rule is defined as "DO INSTEAD" ....
Can someone more experienced than me explain this ?
In the mean time I have replaced the rule with a trigger - and it works like a charm. May be using a trigger is better
(and even faster) than using a rule - I am just curious about this strange behaviour. I will teach something new if I
understand this situation.

Thanks in advance to everyone who answers.
Best wishes,
IVO GELOV

"IVO GELOV" <ivo_gelov@abv.bg> writes:
> I have a rule which mimics REPLACE INTO. The table is defined like this

People keep thinking they can use rules for that :-(

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

There are all sorts of other gotchas if you get past that one.
You might be able to make it work if you use a BEFORE INSERT trigger
instead, but a rule really isn't going to get the job done.

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.

            regards, tom lane

On Mon, 13 Jul 2009 01:31:05 +0300, Tom Lane <tgl@sss.pgh.pa.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