Re: [GENERAL] Skip trigger?
>> On Sat, Apr 30, 2016 at 1:38 AM, wrote: >> > I have a table with a row update trigger that is quite slow. >> > The trigger finction basically sets some bits in a "changed" column >> > depending on which values really changed. >> > For some bulk updates it can be determined in advance that the >> > trigger function will not have any effect. >> > Is there any way to run an update query and specify that it should not >> > activate the trigger. >> > I know that I can disable the trigger and reenable it later; >> > however other concurrent updates mights need it >> >> Indeed the main issue is how you want to handle concurrency. ALTER >> TABLE statements to disable triggers works and is transactional, but >> locks the table, which may be undesired. Here are some useful >> pointers: >> http://blog.endpoint.com/2015/07/selectively-firing-postgres-triggers.html >> Hi Manuel, many thanks ... this seems to be just what I was looking for. I will give it a try tomorrow Regards Wolfgang Hamann -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Skip trigger?
>> On 2016-04-30 02:08, wolfg...@alle-noten.de wrote: >> > Hi, >> > >> > I have a table with a row update trigger that is quite slow. >> > The trigger finction basically sets some bits in a "changed" column >> > depending on which values really changed. >> > For some bulk updates it can be determined in advance that the >> > trigger function will not have any effect. >> > Is there any way to run an update query and specify that it should not >> > activate the trigger. >> > I know that I can disable the trigger and reenable it later; >> > however other concurrent updates mights need it >> >> >> I always disable the trigger, run the update, and enable the trigger >> within a transaction. This locks the table and will prevent other >> sessions from doing updates without the trigger (I run it during >> off-hours if it is going to take more time than is acceptable). >> Hi Stephen, this is what I do now occasionally. However, I would like to be able to run some things right away. The solutions in http://blog.endpoint.com/2015/07/selectively-firing-postgres-triggers.html look very promising Regards Wolfgang Hamann -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Skip trigger?
On 2016-04-30 02:08, wolfg...@alle-noten.de wrote: > Hi, > > I have a table with a row update trigger that is quite slow. > The trigger finction basically sets some bits in a "changed" column > depending on which values really changed. > For some bulk updates it can be determined in advance that the > trigger function will not have any effect. > Is there any way to run an update query and specify that it should not > activate the trigger. > I know that I can disable the trigger and reenable it later; > however other concurrent updates mights need it I always disable the trigger, run the update, and enable the trigger within a transaction. This locks the table and will prevent other sessions from doing updates without the trigger (I run it during off-hours if it is going to take more time than is acceptable). -- Stephen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Skip trigger?
On Sat, Apr 30, 2016 at 1:38 AM, wrote: > I have a table with a row update trigger that is quite slow. > The trigger finction basically sets some bits in a "changed" column > depending on which values really changed. > For some bulk updates it can be determined in advance that the > trigger function will not have any effect. > Is there any way to run an update query and specify that it should not > activate the trigger. > I know that I can disable the trigger and reenable it later; > however other concurrent updates mights need it Indeed the main issue is how you want to handle concurrency. ALTER TABLE statements to disable triggers works and is transactional, but locks the table, which may be undesired. Here are some useful pointers: http://blog.endpoint.com/2015/07/selectively-firing-postgres-triggers.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Skip trigger?
If your other inserts can wait you could maybe combine DISABLE TRIGGER with LOCK TABLE? http://www.postgresql.org/docs/current/static/sql-lock.html Someone else may know a better solution... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
