Re: Disable Trigger for session only

-----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(at)turnstep(dot)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-----