Rule acting as REPLACE INTO behave strange

Поиск
Список
Период
Сортировка
От IVO GELOV
Тема Rule acting as REPLACE INTO behave strange
Дата
Msg-id op.uwywc8ctgon57j@ivo
обсуждение исходный текст
Ответы Re: Rule acting as REPLACE INTO behave strange
Список 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_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

В списке pgsql-general по дате отправления:

Предыдущее
От: Simon Riggs
Дата:
Сообщение: Re: Checkpoint Tuning Question
Следующее
От: Viktor Rosenfeld
Дата:
Сообщение: How can I find out the space used on disk for a table/index