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