Rule acting as REPLACE INTO behave strange

From: "IVO GELOV" <ivo_gelov(at)abv(dot)bg>
To: pgsql-general(at)postgresql(dot)org
Subject: Rule acting as REPLACE INTO behave strange
Date: 2009-07-12 18:30:34
Message-ID: op.uwywc8ctgon57j@ivo
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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_4 IS 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Simon Riggs 2009-07-12 18:36:24 Re: Checkpoint Tuning Question
Previous Message Sam Mason 2009-07-12 18:15:56 Re: xpath() subquery for empty array