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

Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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).

I see -- is there a way to dump out the rule expansion to a log file
somehow? I didn't know how the rule was actually getting expanded.

> 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.

Using a combination of a rule to insert the old line into a history
table, and a trigger to update the record_added (functioning as a
"last updated" field), I was able to get history tables working, however,
this means potentially needing to do operations on both table at once,
which complicates things.

For instance, if I want to get a snapshot of my filesystem locations
6 months ago, AFAICT, I have to do the equivalent of

    select * from homes where record_added < (6 months ago);
    select * from homes_log where record_added < 6mos and
      record expired > 6mos;

I can't figure out how to make the two table act as one straight from SQL.
Is there one?

I could probably get something suitable working at the application level,
but I was hoping to make the history transactions as transparent as possible.

If nothing else, I'll just use the first update rule and work out the SQL
for the update() to homes_log.

Thanks,

----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin                   UF/CISE Department |
| E314D CSE Building                            Phone (352) 392-1499 |
| jfh@cise.ufl.edu                      http://www.cise.ufl.edu/~jfh |
----------------------------------------------------------------------
        - If I can't share your intellectual property, -
        -  why can you share my personal information ? -
                      - Vote for Privacy -