Re: Behaviour of triggers on replicated and non replicated tables

Em 16/06/2011 19:17, Christopher Browne escreveu:
> 2011/6/10 Luiz K. Matsumura<luiz(at)planit(dot)com(dot)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
*
*