agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Disable Trigger for session only
Date: Mon, 29 Jun 2015 13:43:35 -0700
Message-ID: <5591ADF7.4090509@aklaver.com> (raw)
In-Reply-To: <88967b6b1d22d1cbdace2f0351b4ec6a@biglumber.com>
References: <88967b6b1d22d1cbdace2f0351b4ec6a@biglumber.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (8+ messages) latest in thread
Message-ID: <5591ADF7.4090509@aklaver.com>
Permalink: ../5591ADF7.4090509@aklaver.com/
Also on: postgresql.org/message-id/5591ADF7.4090509@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, greg@turnstep.com
Subject: Re: Disable Trigger for session only
In-Reply-To: <5591ADF7.4090509@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox