Обсуждение: Disable Trigger for session only
Hi I' pretty sure I know the answer, but trying my luck. 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; Some notes: It cannot be guaranteed that the above happens as a single transaction. It is possible that this occurs at the same time as other session posting inserts/updates to table TEMP. I'm seeing data which suggests that trigger trigname did not occur when in fact it should have ( i.e. the above update procedure is not relevant ). Does this make sense taking into account that multiple sessions posts to the table at once ? 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 ? Will appreciate any input -- View this message in context: http://postgresql.nabble.com/Disable-Trigger-for-session-only-tp5855658.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 06/29/2015 12:43 AM, gmb wrote: > Hi > > I' pretty sure I know the answer, but trying my luck. > > 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; > > Some notes: > It cannot be guaranteed that the above happens as a single transaction. > It is possible that this occurs at the same time as other session posting > inserts/updates to table TEMP. It can if wrapped in BEGIN/COMMIT or is there reason that is not being done? > > I'm seeing data which suggests that trigger trigname did not occur when in > fact it should have ( i.e. the above update procedure is not relevant ). > Does this make sense taking into account that multiple sessions posts to the > table at once ? Not without knowing what the trigger procedure does? > > 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 ? > > Will appreciate any input > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote >> >> Some notes: >> It cannot be guaranteed that the above happens as a single transaction. >> It is possible that this occurs at the same time as other session posting >> inserts/updates to table TEMP. > > It can if wrapped in BEGIN/COMMIT or is there reason that is not being > done? Sorry , what I meant to say was that as this stage this is not implemented in a single transaction (with BEGIN/COMMIT). Adrian Klaver-4 wrote >> I'm seeing data which suggests that trigger trigname did not occur when >> in >> fact it should have ( i.e. the above update procedure is not relevant ). >> Does this make sense taking into account that multiple sessions posts to >> the >> table at once ? > > Not without knowing what the trigger procedure does? The trigger being disabled is used to post summarized numeric values to a summary table. Actually what I'm trying to do here is to reset the values in the detail table to zero without updating the summary tables. Afterwards I'm updating from a zero value which means that the difference will be posted to the summary table. This kind of data fix is required where data on the summary tables was not posted as excepted for whatever reason. I guess my question is: 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 ? -- View this message in context: http://postgresql.nabble.com/Disable-Trigger-for-session-only-tp5855658p5855697.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 06/29/2015 07:13 AM, gmb wrote: > Adrian Klaver-4 wrote >>> >>> Some notes: >>> It cannot be guaranteed that the above happens as a single transaction. >>> It is possible that this occurs at the same time as other session posting >>> inserts/updates to table TEMP. >> >> It can if wrapped in BEGIN/COMMIT or is there reason that is not being >> done? > > Sorry , what I meant to say was that as this stage this is not implemented > in a single transaction (with BEGIN/COMMIT). > > > Adrian Klaver-4 wrote >>> I'm seeing data which suggests that trigger trigname did not occur when >>> in >>> fact it should have ( i.e. the above update procedure is not relevant ). >>> Does this make sense taking into account that multiple sessions posts to >>> the >>> table at once ? >> >> Not without knowing what the trigger procedure does? > > The trigger being disabled is used to post summarized numeric values to a > summary table. > Actually what I'm trying to do here is to reset the values in the detail > table to zero without updating the summary tables. Afterwards I'm updating > from a zero value which means that the difference will be posted to the > summary table. This kind of data fix is required where data on the summary > tables was not posted as excepted for whatever reason. > > > I guess my question is: > 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 ? That I do not know. A thought did come to mind though. That is to add a reset boolean column(default ='f') to your detail table and make the trigger procedure aware of it. Then when you are resetting the values to zero have reset = 't' and have the trigger procedure ignore those rows. Then do the 'normal' update to update the summary table. > > > > -- > View this message in context: http://postgresql.nabble.com/Disable-Trigger-for-session-only-tp5855658p5855697.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
-----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-----
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
Adrian Klaver-4 wrote > That I do not know. A thought did come to mind though. That is to add a > reset boolean column(default ='f') to your detail table and make the > trigger procedure aware of it. Then when you are resetting the values to > zero have reset = 't' and have the trigger procedure ignore those rows. > Then do the 'normal' update to update the summary table. Thanks Adrian, this will be a workable solution and I'll take it into account. One problem is that many will view the addition of another column for this purpose to be unjustified. -- View this message in context: http://postgresql.nabble.com/Disable-Trigger-for-session-only-tp5855658p5855844.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Greg Sabino Mullane wrote > Presume you meant ENABLE here. Yup, sorry. Greg Sabino Mullane wrote > 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: Thanks Greg, again you assisted with a neat trick. I've definitely found that making use of session_replication_role instead is much more efficient and have been using it in similar cases. Issue here was the one where multiple triggers exist on the table which can by resolved using your suggestion. -- View this message in context: http://postgresql.nabble.com/Disable-Trigger-for-session-only-tp5855658p5855854.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.