| 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: | Whole Thread | Raw Message | 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
| 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 |