problem with rules - column values lost

From: Daniel Schuchardt <daniel_schuchardt(at)web(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: problem with rules - column values lost
Date: 2005-03-22 08:52:04
Message-ID: d1ombi$58g$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi List,

there seem to be a bug in the 8.0 Rule System if I update a view and
does not give a column an value.

example

TEST=# \d abzu_ruletest
View "public.abzu_ruletest"
Column | Type | Modifiers
------------+-------------------+-----------
abz_txt | character varying |
abz_id | integer |
abz_proz | real |
abz_betrag | real |
View definition:
SELECT lang_abzu(abzu.abz_id) AS abz_txt, abzu.abz_id, abzu.abz_proz,
abzu.abz_betrag FROM abzu;

TEST=# SELECT * FROM abzu_ruletest;
abz_txt | abz_id | abz_proz | abz_betrag
---------+--------+----------+------------
| 9 | 6 | 3
(1 row)

TEST=# UPDATE "abzu_ruletest" SET "abz_betrag"=3, abz_txt='test' WHERE
"abz_id"=9;
UPDATE 1
TEST=# SELECT * FROM abzu_ruletest;
abz_txt | abz_id | abz_proz | abz_betrag
---------+--------+----------+------------
test | 9 | 6 | 3
(1 row)

TEST=# UPDATE "abzu_ruletest" SET "abz_betrag"=3 WHERE "abz_id"=9;
UPDATE 1
TEST=# SELECT * FROM abzu_ruletest;
abz_txt | abz_id | abz_proz | abz_betrag
---------+--------+----------+------------
| 9 | 6 | 3

As you can see the Value of abz_txt is lost. The reason seems to be the
on Update rule, i fully delete the old record of the child table and
insert a new record there:
(i do not know if a record exists)

RULE :
UPDATE abzu SET abz_id=new.abz_id, abz_proz=new.abz_proz,
abz_betrag=new.abz_betrag WHERE abz_id=old.abz_id; ------------------OK

DELETE FROM abzutxt WHERE abzl_abz_id=old.abz_id; <----------------HERE
INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id,
new.abz_txt);
=============

Definitions / Testcase

CREATE TABLE abzu
(abz_id SERIAL PRIMARY KEY,
abz_proz FLOAT4,
abz_betrag FLOAT4
);

CREATE TABLE abzutxt
(abzl_id SERIAL NOT NULL PRIMARY KEY,
abzl_abz_id INTEGER NOT NULL REFERENCES abzu ON DELETE CASCADE,
/*LANGUAGE CODE VARCHAR*/
abzl_txt VARCHAR(50)
);

CREATE OR REPLACE FUNCTION lang_abzu(INTEGER /*, VARACHAR (LANGUAGE
CODE)*/) RETURNS VARCHAR AS'
BEGIN
RETURN abzl_txt FROM abzutxt WHERE abzl_abz_id=$1 /* AND LANUAGE CODE
= CURRENT_USER_SETTING*/;
END'LANGUAGE plpgsql;

/*Normally everywhere actual Language codes*/

CREATE OR REPLACE VIEW abzu_ruletest AS
SELECT lang_abzu(abz_id) AS abz_txt, * FROM abzu;

CREATE RULE abzu_lang_insert AS ON INSERT TO abzu_ruletest DO INSTEAD
(INSERT INTO abzu (abz_id, abz_proz, abz_betrag) VALUES (new.abz_id,
new.abz_proz, new.abz_betrag);
INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id,
new.abz_txt);
);

CREATE RULE abzu_lang_update AS ON UPDATE TO abzu_ruletest DO INSTEAD
(UPDATE abzu SET abz_id=new.abz_id, abz_proz=new.abz_proz,
abz_betrag=new.abz_betrag WHERE abz_id=old.abz_id;
DELETE FROM abzutxt WHERE abzl_abz_id=old.abz_id;
INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id,
new.abz_txt);
);

INSERT INTO "abzu_ruletest" ("abz_id", "abz_txt", "abz_betrag",
"abz_proz") VALUES (9, 'Test Rule', 5, 6);

UPDATE "abzu_ruletest" SET "abz_betrag"= 3 WHERE "abz_id"=9;

UPDATE "abzu_ruletest" SET "abz_betrag"= 3, abz_txt='Test Rule 2' WHERE
"abz_id"=9;

thanks, Daniel

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message strk 2005-03-22 09:07:40 Re: caches lifetime with SQL vs PL/PGSQL procs
Previous Message Pavel Stehule 2005-03-22 06:32:10 Re: Proposal: OUT parameters for plpgsql