Thread: trigger/rule question
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 -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +4936207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de --------------------------------------------------------------------- Realtime Forex/Stock Exchange trading powered by postgresSQL:)) http://www.fxignal.net/ ---------------------------------------------------------------------
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
Hi, Going through you mail, I assume that you are updating the mtime only after inserting the record. It is always possibleto check the mtime filed value of the inserted record and take action based on it in the trigger. Is it possible to send me detail about the trigger? Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Enrico Weigelt Sent: Wednesday, April 27, 2005 10:26 PM To: pgsql-sql Subject: [SQL] trigger/rule question 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 -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +4936207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de --------------------------------------------------------------------- Realtime Forex/Stock Exchange trading powered by postgresSQL:)) http://www.fxignal.net/ --------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
* Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com> wrote: Hi, > Going through you mail, I assume that you are updating the mtime > only after inserting the record. An "normal" update (=done by an application or user) should also update the mtime. But there's an replication subsystem, which writes should go through untouched. > It is always possible to check the mtime filed value of the inserted > record and take action based on it in the trigger. yeah, but how to detect whether the application has explicitly written it ? The only chance I currently have in mind is to use some session dependent data, i.e. username or some persistant storage (could be easily done ie. w/ plphp) for this decision. The sync subsystem has to do some "special" login (ie. separate user or setting the session wide variable) before doing its work. I would be happier to let a rule do this, so there's not an extra function per written row. But all my experiments ran into infinite recoursion trouble. > Is it possible to send me detail about the trigger? The trigger isn't existing yet. I'm currently maintaining the mtime updates within the application, but I wanna get away from that. It probably would be interesting, if a normal application couldn't touch the mtime at all. cu -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +4936207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de --------------------------------------------------------------------- Realtime Forex/Stock Exchange trading powered by postgresSQL:)) http://www.fxignal.net/ ---------------------------------------------------------------------
* Christoph Haller <ch@rodos.fzk.de> wrote: Hi, > I assume this still refers to > [SQL] RULE for mtime recording > from last Friday. ehm, yeah. I forgot that I've already asked this stuff ... hmmpf. seems I'm not getting younger ;-) <snip> > 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: <big_snip /> correct me if I'm wrong: you dont let the application write to the actual storage table, but instead to a view, which a modified write to the actual storage, where also the reads get their data from. okay, that's really an idea worth to think about :) insert should work the same way. but how to implement delete ? (the application should only see one table, so in our case the view). if we user "DO INSTEAD", we wont get anything to delete (AFAIK), so we cannot intercept here. the only chance seems to leave out "INSTEAD" and live with duplicate data. Did I miss anyting ? cu -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +4936207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de --------------------------------------------------------------------- Realtime Forex/Stock Exchange trading powered by postgresSQL:)) http://www.fxignal.net/ ---------------------------------------------------------------------
Enrico Weigelt wrote: > > * Christoph Haller <ch@rodos.fzk.de> wrote: > > Hi, > > > I assume this still refers to > > [SQL] RULE for mtime recording > > from last Friday. > > ehm, yeah. I forgot that I've already asked this stuff ... > hmmpf. seems I'm not getting younger ;-) > > <snip> > > 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: > > <big_snip /> > > correct me if I'm wrong: > > you dont let the application write to the actual storage table, but > instead to a view, which a modified write to the actual storage, where > also the reads get their data from. Exactly. > > okay, that's really an idea worth to think about :) > > insert should work the same way. but how to implement delete ? > (the application should only see one table, so in our case the view). Exactly. > if we user "DO INSTEAD", we wont get anything to delete (AFAIK), so > we cannot intercept here. the only chance seems to leave out "INSTEAD" > and live with duplicate data. No. What's wrong with (referring to my previous post) CREATE OR REPLACE RULE joo_delete AS ON DELETE TO joo_view DO INSTEAD DELETE FROM joo WHERE bar = OLD.bar ; DELETE FROM joo_view WHERE bar = '...' ; works perfectly for me Or did I miss something here? Regards, Christoph > > Did I miss anyting ? > > cu > -- > --------------------------------------------------------------------- > Enrico Weigelt == metux IT service > phone: +49 36207 519931 www: http://www.metux.de/ > fax: +49 36207 519932 email: contact@metux.de > --------------------------------------------------------------------- > Realtime Forex/Stock Exchange trading powered by postgresSQL :)) > http://www.fxignal.net/ > --------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Hi, I have written the following trigger assuming the application can pass NULL value on mtime and it can be handled onthe server side by the trigger. CREATE TABLE TEST3 ( ID INT4 NOT NULL, DDATE TIMESTAMP ) CREATE OR REPLACE FUNCTION Updatemtime() RETURNS TRIGGER AS $Updatemtime$ DECLARE dDate timestamp; BEGIN dDate = 'now'; IF COALESCE(NEW.DDATE , dDate ) = dDate THEN NEW.DDATE = dDate; END IF; RETURN NEW; END; $Updatemtime$ LANGUAGE 'plpgsql'; CREATE TRIGGER Updatemtime BEFORE INSERT ON TEST3 FOR EACH ROW EXECUTE PROCEDURE Updatemtime(); INSERT INTO TEST3 VALUES( 1 , NULL ); INSERT INTO TEST3 VALUES( 2 , '2005-05-01'); select * from TEST3 I will continue work on this and let you know if I can find another better solution for this issue. Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Enrico Weigelt Sent: Monday, May 02, 2005 9:02 AM To: pgsql-sql Subject: Re: [SQL] trigger/rule question * Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com> wrote: Hi, > Going through you mail, I assume that you are updating the mtime > only after inserting the record. An "normal" update (=done by an application or user) should also update the mtime. But there's an replication subsystem, which writes should go through untouched. > It is always possible to check the mtime filed value of the inserted > record and take action based on it in the trigger. yeah, but how to detect whether the application has explicitly written it ? The only chance I currently have in mind is to use some session dependent data, i.e. username or some persistant storage (could be easily done ie. w/ plphp) for this decision. The sync subsystem has to do some "special" login (ie. separate user or setting the session wide variable) before doing its work. I would be happier to let a rule do this, so there's not an extra function per written row. But all my experiments ran into infinite recoursion trouble. > Is it possible to send me detail about the trigger? The trigger isn't existing yet. I'm currently maintaining the mtime updates within the application, but I wanna get away from that. It probably would be interesting, if a normal application couldn't touch the mtime at all. cu -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +4936207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de --------------------------------------------------------------------- Realtime Forex/Stock Exchange trading powered by postgresSQL:)) http://www.fxignal.net/ --------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org