Re: trigger/rule question

Enrico Weigelt wrote:
> 
> Hi folks,
> 
> for database synchronization I'm maintaining an mtime field in
> each record and I'd like to get it updated automatically on
> normal writes (insert seems trivial, but update not), but it
> must remain untouched when data is coming in from another node
> (to prevent sync loops).
> 
> I first tried it with rules on update, but I didnt find any trick
> to prevent infinite recoursion. If I'd replace update by delete
> and reinsert, I'll probably run into trouble with constaints and
> delete rules.
> 
> Triggers dont seem to have this problem, but require an function
> call per record, while a rule solution would only rewrite the
> actual query.
> 
> But still I've got the unsolved problem, how to decide when to
> touch the mtime and when to pass it untouched. I didnt find any
> trick to explicitly bypass specific triggers yet.
> 
> Any ideas ?
> 
> thx
> --

I assume this still refers to 
[SQL] RULE for mtime recording 
from last Friday. 
I gave it another thought and 
I am now having something which seems to work. 
The trick is interpose a view to avoid the 
rule recursion: 

CREATE SEQUENCE inode_id_seq ;
CREATE TABLE inode
(   inode_id OID NOT NULL DEFAULT NEXTVAL('inode_id_seq'),   mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE joo
(   bar TEXT
) INHERITS ( inode );

CREATE VIEW joo_view AS SELECT * FROM joo ;

INSERT INTO joo(bar) VALUES ( 'A.R.M.D.' );

INSERT INTO joo(bar,mtime) VALUES ( 'A.L.A.F.' , '2004-04-28
09:43:22.204429' );

SELECT * FROM JOO ;inode_id |           mtime            |   bar    
----------+----------------------------+----------       1 | 2005-04-28 11:20:33.012668 | A.R.M.D.       2 | 2004-04-28
09:43:22.204429| A.L.A.F.
 
(2 rows)



CREATE OR REPLACE RULE joo_update_mtime_is_null 
AS ON UPDATE TO joo_view 
DO INSTEAD 
UPDATE joo SET bar = NEW.bar,               mtime = CASE WHEN OLD.mtime = NEW.mtime                            THEN
current_timestamp                           ELSE NEW.mtime                            END 
 
WHERE bar = OLD.bar ; 



UPDATE joo_view SET bar = ' H T H ' WHERE bar = 'A.R.M.D.' ;

UPDATE joo_view SET bar = ' S T S ',
mtime = '2003-04-28 09:43:22.204429'
WHERE bar = 'A.L.A.F.' ;

SELECT * FROM JOO ;inode_id |           mtime            |   bar   
----------+----------------------------+---------       1 | 2005-04-28 11:23:23.04613  |  H T H        2 | 2003-04-28
09:43:22.204429|  S T S 
 
(2 rows)

Another rule to deal with INSERT, and that's it. 
At least I think. 
Does it help? 

Regards, Christoph