From: Christoph Haller <ch@rodos.fzk.de>
To: weigelt@metux.de
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: trigger/rule question
Date: Thu, 28 Apr 2005 11:26:39 +0200
Message-ID: <4270AC4F.6D62C9C8@rodos.fzk.de> (raw)
References: <20050427165624.GA4253@nibiru.borg.metux.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, weigelt@metux.de
Subject: Re: trigger/rule question
In-Reply-To: <4270AC4F.6D62C9C8@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox