Unexpected Behavior Using a Rule With Multiple Actions (Long) - Mailing list pgsql-general
From | James F. Hranicky |
---|---|
Subject | Unexpected Behavior Using a Rule With Multiple Actions (Long) |
Date | |
Msg-id | 20010904042140.B0733DCC4@mail.cise.ufl.edu Whole thread Raw |
Responses |
Re: Unexpected Behavior Using a Rule With Multiple Actions (Long)
|
List | pgsql-general |
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 -
pgsql-general by date: