Re: Behaviour of triggers on replicated and non replicated tables - Mailing list pgsql-bugs
From | Luiz K. Matsumura |
---|---|
Subject | Re: Behaviour of triggers on replicated and non replicated tables |
Date | |
Msg-id | 4DFAC9E7.5040900@planit.com.br Whole thread Raw |
In response to | Behaviour of triggers on replicated and non replicated tables ("Luiz K. Matsumura" <luiz@planit.com.br>) |
List | pgsql-bugs |
Em 16/06/2011 19:17, Christopher Browne escreveu: > 2011/6/10 Luiz K. Matsumura<luiz@planit.com.br>: >> Hi, >> I need help to know if the follow scenario is a expected behaviour, a bug of >> postgres or a bug of slony: >> >> Postgres v8.4.8 >> Slony-I v 2.0.5 >> >> I have table replicated with slony and that do some updates in another table >> not replicated. >> >> The trigger on replicated table was enabled on the slave database with the >> command: >> >> ALTER TABLE table1 ENABLE ALLWAYS TRIGGER trigger1; >> >> And this trigger is working fine as expected. >> >> The strange behaviour is that trigger do a update in another table not >> replicated, let´s say table2, and >> the triggers of this table is not fired. >> A unexpected behaviour IMHO, if I do >> >> ALTER TABLE table2 ENABLE ALWAYS TRIGGER trigger2; >> >> Then the trigger2 is fired now when trigger1 do a update in table2. >> >> My doubt is: since table2 is not replicated why they triggers dont fire even >> by a update command in >> a trigger of a replicated table ? > The *normal* behaviour that Slony has is that triggers on replicated > tables should, normally, not fire on a replica. > > That's typically the right thing, as typically, the trigger fired on > the master, and shouldn't need to fire again. A pretty usual case is > with foreign key triggers. Reiterating, if the foreign key trigger is > checked on the master, there's not much sense in checking it again on > the replica. > > There's why the "default" is that triggers aren't set to ENABLE ALWAYS. > > Evidently you have another scenario, where you know you need to run > the triggers even on a subscriber. > > I'm not 100% sure that I'm understanding the "doubt"... > > If the trigger is enabled on table2, then it's going to run whenever > something updates table2; that's pretty well independent of any > replication infrastructure. > > It's not clear to me whether your "trigger1" is specifically doing > some update to table2. > > If the trigger on table1 is indeed firing, and trigger "trigger1" > calls a function that updates data in table2, then I'd fully expect > the trigger "trigger2" on table2 to, at that point, fire. > > I can see a good reason for that sequence of events to break down, > namely if the function for "trigger1" doesn't actually find any data > to touch in table2. > > For instance, if the function for trigger1 includes the query: > > update table2 set id = id * 2; > > that would *usually* be expected to update all the tuples in table2, > and fire the trigger "trigger2" once for each tuple that is updated. > > But supposing table2 happens to be empty, then that UPDATE request > will find no tuples to operate on, and so the trigger "trigger2" won't > fire as much as once. > > That's where the "deep details" lie; whether trigger2 fires depends on > what the function that trigger1 fires actually does. > > If you have a bug, or an unexpected data pattern, then perhaps > trigger2 doesn't fire even though you expected it to. That's one of > the troublesome risks you find when programming with triggers. > > I had a "trigger bug" come in this week. I discovered that there was > quite a bad bug in a trigger function, and this bug had persisted for > nearly a year before someone finally did something that exercised it. > I felt rather dumb when I saw it, as, in retrospect, it was an obvious > error. Sadly, a lot of our mistakes fall from things that, in > retrospect, are "obvious errors." > > Triggers are weird enough that intuition fails pretty easily :-(. > > Hi Christopher , thanks for reply. My english is not so good, so please pardon me if I not explain the problem clearly. I will try to explain better with a more pratical example (just a simplified example) CREATE TABLE table1 ( id serial NOT NULL, idtable2 integer NOT NULL, qtty integer NOT NULL, CONSTRAINT pk_table1 PRIMARY KEY (id) ) CREATE TABLE table2 ( id serial NOT NULL, qtty integer NOT NULL, qtty_used integer NOT NULL, lused boolean DEFAULT false, CONSTRAINT pk_table2 PRIMARY KEY (id) ) CREATE OR REPLACE FUNCTION trigger1() RETURNS trigger AS $BODY$ BEGIN IF tg_op = 'DELETE' THEN UPDATE table2 SET qtty_used= qtty_used- old.qtty WHERE id = old.idtable2; ELSIF tg_op = 'INSERT' THEN UPDATE vd.pdvendai SET qtty_used= qtty_used+ new.qtty WHERE id = new.idtable2; ELSIF tg_op = 'UPDATE' THEN UPDATE vd.pdvendai SET qtty_used= qtty_used+ new.qtty - old.qtty WHERE id = new.idtable2; END IF. IF tg_op = 'DELETE' THEN RETURN old ; ELSE RETURN new ; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER trg_table1 AFTER INSERT OR UPDATE OR DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE trigger1(); CREATE OR REPLACE FUNCTION trigger2() RETURNS trigger AS $BODY$ BEGIN new.lused = ( new.qtty_used>= new.qtty ); RETURN new; END $BODY$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER trg_table2 BEFORE INSERT OR UPDATE ON table2 FOR EACH ROW EXECUTE PROCEDURE trigger2(); And then create a replication of table1 and enable the trigger trg_table1 in all databases: ALTER TABLE table1 ENABLE ALLWAYS TRIGGER trg_trigger1; Then suppose in table2 of slave database I have this data table2 id | qtty | qtty_used | lused ---+------+-----------+-------- 1 | 10 | 0 | false If I do a insert in master database : INSERT INTO table1 (idtable2,qtty ) VALUES ( 1, 10 ) the result on slave database table2 after replication of data in table1 is table2 id | qtty | qtty_used | lused ---+------+-----------+-------- 1 | 10 | 10 | false <-- lused not changed to true So the trigger trg_table2 not fire by default when the origin is from a replication. But my sense is that since table2 is not in replication the trigger trg_table2 must fire in this scenario. -- Luiz K. Matsumura * *
pgsql-bugs by date: