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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
