problem with rules - column values lost
От | Daniel Schuchardt |
---|---|
Тема | problem with rules - column values lost |
Дата | |
Msg-id | d1ombi$58g$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: problem with rules - column values lost
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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 ONDELETE 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 ASSELECT 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
В списке pgsql-hackers по дате отправления: