[prev in list] [next in list] [prev in thread] [next in thread] 

List:       slony1-general
Subject:    Re: [Slony1-general] Triggers on subscribed tables not firing from
From:       Sven Willenberger <sven () dmv ! com>
Date:       2005-11-30 14:24:21
Message-ID: 1133360661.16516.10.camel () lanshark ! dmv ! com
[Download RAW message or body]

On Tue, 2005-11-29 at 18:42 -0500, Jan Wieck wrote:
> On 11/29/2005 5:46 PM, Sven Willenberger wrote:
> 
> > On Tue, 2005-11-29 at 17:20 -0500, Jan Wieck wrote:
> >> On 11/29/2005 3:28 PM, Sven Willenberger wrote:
> >> 
> >> > On Tue, 2005-11-29 at 15:09 -0500, Sven Willenberger wrote:
> >> >> Slony 1.1.2, FreeBSD 6.0, Postgresql 8.0.4
> >> >> 
> >> >> I currently have a table that I am replicating to a subscriber node
> >> >> containing customer information. I have added a trigger to the
> >> >> subscriber version of this table to fire after insert that should create
> >> >> a new table using information from the newly inserted record; this
> >> >> trigger does not seem to fire.
> >> >> 
> >> >> Does Slony bypass user triggers on the subscriber node's tables? If so,
> >> >> is there any way to bypass this behavior? If not, any ideas why this
> >> >> trigger may not be firing?
> >> >> 
> >> >> Sven
> >> >> 
> >> >> _______________________________________________
> >> > 
> >> > Please disregard; there were apparently a batch of records that were
> >> > being updated rather than inserted (that had already existed prior to
> >> > the replication scheme being enabled); further testing with adding new
> >> > records reveals that the after update triggers on subscriber nodes in
> >> > fact do work.
> >> > 
> >> > I apologize for the noise.
> >> 
> >> I hope that you did not create the trigger in the subscriber database 
> >> via direct DDL after Slony-I was installed. On a Slony-I node all DDL 
> >> must be run via the slonik command EXECUTE SCRIPT.
> > 
> > My set up is as follows:
> > 
> > Origin: 
> > CustomerTable
> > 
> > Subscriber:
> > CustomerTable
> > After Insert on CustomerTable execute trigger -> this trigger creates a
> > new table which does not exist on origin.
> > 
> > So yes, I did create the trigger on the replicated CustomerTable after
> 
> And I thought we had made it clear in the documentation that Slony-I is 
> putting the system catalog of a subscriber into a corrupted state that 
> makes DDL statements like that playing Russian Roulette *sigh*.
> 
> The correct solution to this is EXECUTE SCRIPT (EXECUTE ONLY ON=...) 
> together with STORE TRIGGER.
> 
> 
> Jan

And I came away with the assumption that this was important when trying
to maintain a consistent schema state between a table on an origin and
subscriber node (when, in fact I only need the data to be consistent).
However, in light of the way that postgresql does store table
information in its catalogue tables, I can see why it may be important
to have the replication system be aware of the trigger, even it is to
exist in a "non-normal" location (i.e. subscriber node). Additionally,
as the documentation makes mention that "perforamance-enhancing" indexes
can be added directly to subscriber nodes (via psql) rather than using
EXECUTE SCRIPT, I became a little vague as to what degree of table
modification would need EXECUTE SCRIPT versus direct DDL manipulation.

That being said, I went ahead and removed the trigger and used slonik to
EXECUTE SCRIPT (EXECUTE ONLY ON =) to add the trigger to the subscriber
node followed by STORE TRIGGER to make it visible and that seems to have
duplicated the setup I had in a "slony safe" manner.

Thanks for the corrections and suggestions.

Sven

Sven


[prev in list] [next in list] [prev in thread] [next in thread]