Thread: 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@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 -
"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
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 -