Unexpected Behavior Using a Rule With Multiple Actions (Long)

I'm attempting to set up a table that keeps it's own transaction history.
Specifically, the table looks like this:

create table homes_table
(
fsname varchar(30),
fspath varchar(40),
record_added datetime default now(),
record_expired datetime default null
);

Eventually, I'll add an index like so:

create unique index homes_table_idx on homes_table
(fsname, record_expired);

but for debugging purposes, I'm leaving it out for now. In effect, the
primary key for the table is (fsname, record_expired). If (fsname,
record_expired) is the "key", then fspath could be considered the "value".
For any table, there could be one or more columns in the "value" part of
the table.

To "delete" a record, you simply update the record_expired field with the
current date. To "update" a record, mark the record_expired field with the
current date and insert the new information .

AFAICT, this calls for a view and rules defining insertion, updating, and
deletion to the view, like so:

create view homes as select fsname, fspath, record_added from
homes_table where record_expired is null;

create rule homes_insert as on insert to homes do
insert into homes_table values (NEW.fsname, NEW.fspath);

create rule homes_delete as on delete to homes do instead
update homes_table set record_expired = now() where fsname
= OLD.fsname and record_expired is null;

So far, all of this appears to work fine. It's when I get to the update rule
that I have problems. I've gotten the following to work as I want:

create rule homes_update as on update to homes where OLD.fspath !=
NEW.fspath do instead
(
insert into homes_table values (NEW.fsname, NEW.fspath);

update homes_table set record_expired = now() where fsname
= OLD.fsname and fspath != NEW.fspath and record_expired
is null;

);

Here's some sample output:

insert into homes values ('h02', 'mach0:/exp/h02');

select * from homes;
select * from homes_table;

fsname | fspath | record_added
--------+----------------+------------------------
h02 | mach0:/exp/h02 | 2001-09-03 23:46:44
(1 row)

fsname | fspath | record_added | record_expired
--------+----------------+------------------------+----------------
h02 | mach0:/exp/h02 | 2001-09-03 23:46:44 |
(1 row)

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

select * from homes;
select * from homes_table;

UPDATE 1
fsname | fspath | record_added
--------+----------------+------------------------
h02 | mach1:/exp/h02 | 2001-09-03 23:46:44-04
(1 row)

fsname | fspath | record_added | record_expired
--------+----------------+---------------------+---------------
h02 | mach1:/exp/h02 | 2001-09-03 23:46:44 |
h02 | mach0:/exp/h02 | 2001-09-03 23:46:44 | 2001-09-03 23:46:44
(2 rows)

However, in order to get this to work properly, I had to specify

fspath != NEW.fspath

in the update rule or both the old row and the new row would get updated.
This is no big deal when there's only one column to track in the "value"
part of the table, but if there are several columns in the "value" part
of the table, the SQL gets more complicated.

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.

Sample output from this configuration:

insert into homes values ('h02', 'mach0:/exp/h02');

select * from homes;
select * from homes_table;

fsname | fspath | record_added
--------+----------------+------------------------
h02 | mach0:/exp/h02 | 2001-09-03 23:51:02-04
(1 row)

fsname | fspath | record_added | record_expired
--------+----------------+------------------------+----------------
h02 | mach0:/exp/h02 | 2001-09-03 23:51:02-04 |
(1 row)

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

select * from homes;
select * from homes_table;

UPDATE 0
fsname | fspath | record_added
--------+--------+--------------
(0 rows)

fsname | fspath | record_added | record_expired
--------+----------------+------------------------+---------------
h02 | mach0:/exp/h02 | 2001-09-03 23:51:02 | 2001-09-03 23:51:02
(1 row)

Question 1: Anyone have any ideas as to why this isn't working?
----------

In trying to debug the problem, I attempted to add "select into" lines
in the rule like so:

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;

select * into temp homes_table_temp_0 from homes_table;

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

select * into temp homes_table_temp_1 from homes_table;
);

however, I got

ERROR: parser: parse error at or near "select"

Question 2: Are "select into" statement not allowed in rules?
----------

Any and all help would be most appreciated.

----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin UF/CISE Department |
| E314D CSE Building Phone (352) 392-1499 |
| jfh(at)cise(dot)ufl(dot)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 -