Thread: Behaviour of triggers on replicated and non replicated tables
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 ? Best Regards, Luiz K. Matsumura
2011/6/10 Luiz K. Matsumura <luiz@planit.com.br>: > 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 ta= ble > 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=B4s 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 e= ven > by a update command in > a trigger of a replicated table ? I'm confused. If you enable the trigger on table2, it's going to fire when someone updates table2. Whether or not the update is coming from another trigger or directly from the user has nothing to do with it. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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 ta= ble > 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=B4s 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 e= ven > 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. =A0A pretty usual case is with foreign key triggers. =A0Reiterating, 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: =A0 update table2 set id =3D 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. =A0That's one of the troublesome risks you find when programming with triggers. I had a "trigger bug" come in this week. =A0I 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. =A0Sadly, a lot of our mistakes fall from things that, in retrospect, are "obvious errors." Triggers are weird enough that intuition fails pretty easily :-(. --=20 When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
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
*
*
Em 16/06/2011 16:39, Robert Haas escreveu: > 2011/6/10 Luiz K. Matsumura<luiz@planit.com.br>: >> 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 ? > I'm confused. If you enable the trigger on table2, it's going to fire > when someone updates table2. Whether or not the update is coming from > another trigger or directly from the user has nothing to do with it. > Thanks for reply Robert. This is the point, when the trigger of table2 was create with a command like this CREATE TRIGGER trg_table2 BEFORE INSERT OR UPDATE ON table2 FOR EACH ROW EXECUTE PROCEDURE trg_table2(); The trigger is enabled by default as expected. If I do a update on table2 the trigger fire normally. But when a replicated table fire a trigger that do the same update, now the trigger on table2 don´t fire. I don´t know if this is slony disabling all triggers unless REPLICA and ALLWAYS trrigers even over not replicated tables or this is managed by postgresql -- Luiz K. Matsumura * *
2011/6/16 Luiz K. Matsumura <luiz@planit.com.br>: > Em 16/06/2011 16:39, Robert Haas escreveu: > > 2011/6/10 Luiz K. Matsumura <luiz@planit.com.br>: > > 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 ta= ble > 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=B4s 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 e= ven > by a update command in > a trigger of a replicated table ? > > I'm confused. If you enable the trigger on table2, it's going to fire > when someone updates table2. Whether or not the update is coming from > another trigger or directly from the user has nothing to do with it. > > Thanks for reply Robert. > > This is the point, when the trigger of table2 was create with a command l= ike > this > > CREATE TRIGGER trg_table2 > =A0 BEFORE INSERT OR UPDATE > =A0 ON table2 > =A0 FOR EACH ROW > =A0 EXECUTE PROCEDURE trg_table2(); > > The trigger is enabled by default as expected. If I do a update on table2 > the trigger fire normally. > But when a replicated table fire a trigger that do the same update, now t= he > trigger on table2 don=B4t fire. > I don=B4t know if this is slony disabling all triggers=A0 unless REPLICA = and > ALLWAYS trrigers even over not replicated > tables or this is managed by postgresql OK, I see. That's got something to do with what Slony does internally, which unfortunately I'm not qualified to comment on, not being a Slony guy. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company