pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger/rule question
10+ messages / 5 participants
[nested] [flat]
* trigger/rule question
@ 2005-04-27 16:56 Enrico Weigelt <weigelt@metux.de>
0 siblings, 1 reply; 10+ messages in thread
From: Enrico Weigelt @ 2005-04-27 16:56 UTC (permalink / raw)
To: pgsql-sql
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: +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/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: trigger/rule question
@ 2005-04-28 05:13 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-04-28 05:13 UTC (permalink / raw)
To: weigelt@metux.de; pgsql-sql
Hi,
Going through you mail, I assume that you are updating the mtime only after inserting the record. It is always possible to 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: +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 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: trigger/rule question
@ 2005-04-28 09:26 Christoph Haller <ch@rodos.fzk.de>
parent: Enrico Weigelt <weigelt@metux.de>
0 siblings, 1 reply; 10+ messages in thread
From: Christoph Haller @ 2005-04-28 09:26 UTC (permalink / raw)
To: weigelt@metux.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: trigger/rule question
@ 2005-05-02 03:31 Enrico Weigelt <weigelt@metux.de>
parent: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 10+ messages in thread
From: Enrico Weigelt @ 2005-05-02 03:31 UTC (permalink / raw)
To: pgsql-sql
* 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: +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/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: trigger/rule question
@ 2005-05-02 03:41 Enrico Weigelt <weigelt@metux.de>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 10+ messages in thread
From: Enrico Weigelt @ 2005-05-02 03:41 UTC (permalink / raw)
To: pgsql-sql
* 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: +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/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: trigger/rule question
@ 2005-05-02 04:49 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-05-02 04:49 UTC (permalink / raw)
To: weigelt@metux.de; pgsql-sql
Hi,
I have written the following trigger assuming the application can pass NULL value on mtime and it can be handled on the 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: +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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: trigger/rule question
@ 2005-05-02 07:58 Christoph Haller <ch@rodos.fzk.de>
parent: Enrico Weigelt <weigelt@metux.de>
0 siblings, 0 replies; 10+ messages in thread
From: Christoph Haller @ 2005-05-02 07:58 UTC (permalink / raw)
To: weigelt@metux.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Trigger / rule question
@ 2005-11-20 09:18 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 10+ messages in thread
From: Joost Kraaijeveld @ 2005-11-20 09:18 UTC (permalink / raw)
To: pgsql-sql
Hi,
Are a trigger or rule part of a query, that is:
1. Does an insert, update or delete statement return before or after an
"After" trigger (is such a trigger or rule synchronous or a-synchronous?
2. Is there a concept of a rule or trigger that fails? If so, if a
trigger or rule fails, does the insert, update or delete statement
(transaction) fail?
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger / rule question
@ 2005-11-20 11:08 Peter Eisentraut <peter_e@gmx.net>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2005-11-20 11:08 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
Joost Kraaijeveld wrote:
> 1. Does an insert, update or delete statement return before or after
> an "After" trigger (is such a trigger or rule synchronous or
> a-synchronous?
Synchronous
> 2. Is there a concept of a rule or trigger that fails?
> If so, if a trigger or rule fails, does the insert, update or delete
> statement (transaction) fail?
Yes
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger / rule question
@ 2005-11-20 15:36 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 10+ messages in thread
From: Joost Kraaijeveld @ 2005-11-20 15:36 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
Hi Peter,
On Sun, 2005-11-20 at 12:08 +0100, Peter Eisentraut wrote:
> Joost Kraaijeveld wrote:
> > 1. Does an insert, update or delete statement return before or after
> > an "After" trigger (is such a trigger or rule synchronous or
> > a-synchronous?
>
> Synchronous
>
> > 2. Is there a concept of a rule or trigger that fails?
> > If so, if a trigger or rule fails, does the insert, update or delete
> > statement (transaction) fail?
>
> Yes
OK, thanks for the answer.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-11-20 15:36 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-27 16:56 trigger/rule question Enrico Weigelt <weigelt@metux.de>
2005-04-28 09:26 ` Christoph Haller <ch@rodos.fzk.de>
2005-05-02 03:41 ` Enrico Weigelt <weigelt@metux.de>
2005-05-02 07:58 ` Christoph Haller <ch@rodos.fzk.de>
2005-04-28 05:13 Re: trigger/rule question Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-05-02 03:31 ` Enrico Weigelt <weigelt@metux.de>
2005-05-02 04:49 Re: trigger/rule question Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-11-20 09:18 Trigger / rule question Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-20 11:08 ` Re: Trigger / rule question Peter Eisentraut <peter_e@gmx.net>
2005-11-20 15:36 ` Re: Trigger / rule question Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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