Thread: Multiple-action rule surprise

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

> -- 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.da=
ta1 =3D
> new.data1)) =3D 0
>        DO (
>                UPDATE test.table1 SET data1 =3D new.data1 WHERE table1.id=
1 =3D old.id1;
>                UPDATE test.table1 SET flag1 =3D true WHERE table1.id1 =3D=
 old.id1;
>        );
>=20
> -- 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
> --
>=20
> UPDATE test.view1 SET data1 =3D 'bar' WHERE view1.id1 =3D 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>
>=20
> I haven't found anything in the doc or in the list archives explicitly
> addressing this point. Is this a bug or a feature?
>=20

Actually the rule is executing as expecting without executing at all
when if founds no rows matching your condition.

but because you don't specify anything it's executing your query and
ALSO the actions in the rule if the condition were pass...

http://www.postgresql.org/docs/8.0/static/sql-createrule.html
ALSO
ALSO indicates that the commands should be executed in addition to the
original command.

If neither ALSO nor INSTEAD is specified, ALSO is the default.=20

--=20
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On 2005-08-04, "Eugene Shekhtman" <postgre@xenomics.com> wrote:
> 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.

The absolute first thing you must learn about using rules in postgresql
is that _rules are not procedural logic_, i.e. you can't express them as
"if (a) then do B".  Rules rewrite the command _before_ anything is
executed (indeed before anything is even planned).  At the time of
rewriting, there is no way to know whether the WHERE clause of a rule will
be matched, so the rule is always expanded the same way, and the WHERE
clause becomes part of the rewritten command.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services