Multiple-action rule surprise

Поиск
Список
Период
Сортировка
От Eugene Shekhtman
Тема Multiple-action rule surprise
Дата
Msg-id S370854AbVHDScG/20050804183206Z+40218@ams003.ftl.affinity.com
обсуждение исходный текст
Ответы Re: Multiple-action rule surprise  (Jaime Casanova <systemguards@gmail.com>)
Список pgsql-bugs
PostgreSQL version: 8.0.3
OS: Win32 (Win 2003 Server)

There is something strange and counterintuitive about the way that
multiple-action PostgreSQL rules work. In the following rule definition

CREATE RULE _rulename_ AS ON _event_ TO _table_
    WHERE _condition_
    DO (
        _command1_;
        _command2_;
        ...;
    );

the condition-testing logic is NOT equivalent to

if ( _condition_ ) {
    _command1_;
    _command2_;
    ...;
}

as one would assume, but rather more like

if ( _condition_ ) {
    _command1_;
}
if ( _condition_ ) {
    _command2_;
}
...

It seems that the _condition_ is checked before each of the actions
in the rule. Thus, if _command1_ causes the _condition_ to become
false, _command2_ will not be executed. Here is a complete example:

<SQL>

CREATE SCHEMA test;

CREATE TABLE test.table1
(
    id1 int4 NOT NULL,
    data1 text,
    flag1 bool DEFAULT false
);

CREATE OR REPLACE VIEW test.view1 AS
    SELECT table1.id1, table1.data1, table1.flag1
    FROM test.table1;

CREATE OR REPLACE RULE upd AS ON UPDATE TO test.view1
    DO INSTEAD NOTHING;

-- I know this rule is awkward. Please bear with me.
CREATE OR REPLACE RULE upd_if AS ON UPDATE TO test.view1
    DO (
        UPDATE test.table1 SET data1 = new.data1 WHERE table1.id1 = old.id1;
        UPDATE test.table1 SET flag1 = true WHERE table1.id1 = old.id1;
    );

INSERT INTO test.table1 (id1, data1) VALUES (1, 'foo');
SELECT * FROM test.view1;
--   id1   data1   flag1
--  ----------------------
--    1     foo    FALSE
--

UPDATE test.view1 SET data1 = 'bar' WHERE view1.id1 = 1;
SELECT * FROM test.view1;
--   id1   data1   flag1
--  ----------------------
--    1     bar    TRUE
--
-- So far so good...

-- Now I add to the "upd_if" rule
-- a condition that checks if a similar record already exists
CREATE OR REPLACE RULE upd_if AS ON UPDATE TO test.view1
    WHERE ((SELECT count(*) AS count1 FROM test.table1 WHERE table1.data1 =
new.data1)) = 0
    DO (
        UPDATE test.table1 SET data1 = new.data1 WHERE table1.id1 = old.id1;
        UPDATE test.table1 SET flag1 = true WHERE table1.id1 = old.id1;
    );

-- Start with fresh data
DELETE FROM test.table1;
INSERT INTO test.table1 (id1, data1) VALUES (1, 'foo');
SELECT * FROM test.view1;
--   id1   data1   flag1
--  ----------------------
--    1     foo    FALSE
--

UPDATE test.view1 SET data1 = 'bar' WHERE view1.id1 = 1;
SELECT * FROM test.view1;
--   id1   data1   flag1
--  ----------------------
--    1     bar    FALSE
--
-- Only the first of the 2 commands in the "upd_if" rule was executed!
-- The second command is silently ignored.
--
</SQL>

I haven't found anything in the doc or in the list archives explicitly
addressing this point. Is this a bug or a feature?

Gene

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: FWD: bug report: index is not a btree
Следующее
От: Jaime Casanova
Дата:
Сообщение: Re: Multiple-action rule surprise