Message-ID: <4270AC4F.6D62C9C8@rodos.fzk.de>
Date: Thu, 28 Apr 2005 11:26:39 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: weigelt@metux.de
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: trigger/rule question
References: <20050427165624.GA4253@nibiru.borg.metux.de>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

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