From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Disable Trigger for session only
In-Reply-To: <1435563798887-5855658.post@n5.nabble.com>
Content-type: text/plain; charset=UTF-8
Date: Mon, 29 Jun 2015 20:34:11 -0000
Message-ID: <88967b6b1d22d1cbdace2f0351b4ec6a@biglumber.com>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


-----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;


> 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-----


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