Re: Unexpected Behavior Using a Rule With Multiple Actions (Long)

"James F. Hranicky" <jfh@cise.ufl.edu> writes:
> What I'd really like to do is this:

>     create rule homes_update as on update to homes where OLD.fspath !=
>       NEW.fspath do instead
>     (
>             update homes_table set record_expired = now() where fsname
>               = OLD.fsname and record_expired is null;

>             insert into homes_table values (NEW.fsname, NEW.fspath);

>     );

> However, when I do that, it seems the insert fails to execute, or fails
> silently in some way.

The reason it doesn't work is that the INSERT is done conditionally on
the existence of view rows satisfying the rule's WHERE and the original
query's WHERE.  For example, given

update homes set fspath = 'mach1:/exp/h02' where fsname = 'h02';

the second part of the rule expands to something like

    INSERT INTO homes_table
    SELECT
        'h02',            -- substituted for NEW.fsname
        'mach1:/exp/h02'    -- substituted for NEW.fspath
    FROM homes OLD
    WHERE OLD.fspath != 'mach1:/exp/h02'    -- rule WHERE
    AND OLD.fsname = 'h02';            -- original WHERE

The trouble is that after the UPDATE done by the first part of the
rule, there are no view rows satisfying the WHERE conditions (you've
set record_expired to non-null in all the homes_table rows that might
have matched).

Basically, rules are macros that get substituted into the given query.
If you do anything that's even slightly self-referential then you are
likely to get confused.  It's a lot easier to wrap your mind around a
trigger --- the extra notational complexity of having to write a trigger
function is more than made up for by conceptual simplicity.

My suggestion is to do this with triggers and a separate history table.
Say,

    homes (fsname primary key, fspath, record_added default now());

    homes_log (fsname, fspath, record_added, record_expired);

and a trigger that does an insert into homes_log on any update or delete
of homes.

            regards, tom lane