Re: Disable Triggers

From: Geoffrey <lists(at)serioustechnology(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Disable Triggers
Date: 2008-04-09 19:08:59
Message-ID: 47FD144B.2030405@serioustechnology.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> I see the following in the documentation for pg_trigger related
>> to tgenabled: "Controls in which session_replication_role modes the
>> trigger fires. O = trigger fires in "origin" and "local" modes,
>> D = trigger is disabled, R = trigger fires in "replica" mode, A =
>> trigger fires always."
>
>> My question is: When tgenabled is set to "D", how does that setting
>> interact with session_replication_role and, is there a way to use
>> tgenabled with a setting of "D" to prevent a particular trigger
>> from firing. Using ALTER TABLE to disable the trigger won't work
>> because the whole table is locked during the transaction and I only
>> want the disabled trigger to apply to the current transaction in the
>> current session.
>
> If you simply want to ignore all triggers, just use a 'replica' role.
> When done, switch it back to 'origin' (or your default, which should
> be origin).
>
> If you want to fire only a single trigger, set it to 'always' mode and
> switch to 'replica'. If you want to fire all triggers *except* a
> certain trigger, set that trigger to replica mode and leave the
> session_replication_mode unchanged (default/origin).
>
> You should be using ALTER TABLE and not worry about changing tgenabled
> yourself, in case it wasn't obvious. You should be able to make permanent
> changes and then just use session_replication_role to control how it acts
> in a particular transaction.

The issue at hand (I work with the OP), is that our current application
disables all triggers quite often. Enter Slony, we want to replicate.
So, what we need to do is, disable ALL triggers EXCEPT slony triggers.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Figueiredo Jr. 2008-04-09 19:23:55 Re: connecting VB to postgreSQL
Previous Message Greg Sabino Mullane 2008-04-09 18:56:48 Re: Disable Triggers