Fwd: Behaviour of triggers on replicated and non replicated tables

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 :-(.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"