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