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