Skip site navigation (1) Skip section navigation (2)

Re: Behaviour of triggers on replicated and non replicated tables

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Behaviour of triggers on replicated and non replicated tables
Date: 2011-06-17 18:48:05
Message-ID: BANLkTimFBrQe0WU_Htx-uMyeSJSFjx98VQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
2011/6/16 Luiz K. Matsumura <luiz(at)planit(dot)com(dot)br>:
> Em 16/06/2011 16:39, Robert Haas escreveu:
>
> 2011/6/10 Luiz K. Matsumura <luiz(at)planit(dot)com(dot)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

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

pgsql-bugs by date

Next:From: Антон СтепаненкоDate: 2011-06-17 19:49:01
Subject: Re: could not read block XXXXX in file "base/YYYYY/ZZZZZZ": read only 160 of 8192 bytes
Previous:From: Tom LaneDate: 2011-06-17 17:51:59
Subject: Re: Ident authentication fails due to bind error on server (8.4.8)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group