Multiple-action rule surprise

From: "Eugene Shekhtman" <postgre(at)xenomics(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Multiple-action rule surprise
Date: 2005-08-04 18:32:02
Message-ID: S370854AbVHDScG/20050804183206Z+40218@ams003.ftl.affinity.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jaime Casanova 2005-08-04 19:35:12 Re: Multiple-action rule surprise
Previous Message Janet Hempstead 2005-08-04 14:24:59 BUG #1807: cannot build postgresql: libascii_and_mic.so.0.0: No such file or directory