Message-ID: <5591ADF7.4090509@aklaver.com>
Date: Mon, 29 Jun 2015 13:43:35 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: Greg Sabino Mullane <greg@turnstep.com>, pgsql-sql@postgresql.org
Subject: Re: Disable Trigger for session only
References: <88967b6b1d22d1cbdace2f0351b4ec6a@biglumber.com>
In-Reply-To: <88967b6b1d22d1cbdace2f0351b4ec6a@biglumber.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 06/29/2015 01:34 PM, Greg Sabino Mullane wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>> "gmb" asks:
>
>> I'm in a position where the most logical/effective way of doing an update
>> (data fix) is this:
>> ALTER TABLE temp DISABLE TRIGGER trigname;
>> UPDATE temp ..... DO SOME STUFF....
>> ALTER TABLE temp DISABLE TRIGGER trigname;
>
> Presume you meant ENABLE here.
>
>> It cannot be guaranteed that the above happens as a single transaction.
>>
>> I'm aware that session_replication_role can be used as alternative to
>> disable triggers, and have been using it in other scenarios. But in this
>> case i'd like to choose which trigger to disable (I want other triggers on
>> table temp to still occur).
>>
>> Is there any other alternatives to this ?
>
> You can use session_replication_role (srr). One of its settings is 'local', which
> basically means "act the exact same as the default, 'origin', but with
> a different name". Thus, you can teach the trigger you want to get disabled
> to short-circuit if srr is set to local. Inside plpgsql it would look something
> like this:
>
> ...
> DECLARE
>    myst TEXT;
> BEGIN
>    SELECT INTO myst setting FROM pg_settings WHERE name = 'session_replication_role';
>    IF myst = 'local' THEN
>      RETURN;
>    END IF;
>
> ...normal trigger code here...
> END;
> ...
>
> Then, just issue a SET session_replication_role = 'local', and the trigger will
> not do anything for that session only:
>
> BEGIN;
> SET LOCAL session_replication_role = 'local';
> UPDATE temp ..... DO SOME STUFF....
> COMMIT;

Wow, that is a whole lot cleaner solution then what I came up with. I 
will have to remember that for future use.

>
>
>> If I encapsulate the "disable trigger/update/enable trigger" in BEGIN/COMMIT
>> to handle as single transaction, are there guarantees that the disabling of
>> the trigger will not have an effect on other sessions ?
>
> It will cause heavy locking but should otherwise have no effect. But using
> session_replication_role is a cleaner solution, IMHO.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201506291631
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAlWRq6oACgkQvJuQZxSWSsh9uwCfe9K+xSYIMthcV9xM7EJh/eQb
> vEQAnjo4Quo4Rq9WC50Yuh6aCTHgPlGn
> =Ap56
> -----END PGP SIGNATURE-----
>
>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql