Postgres Pro
Downloads
Home   >   mailing lists

Thread: Skip trigger?

Skip trigger?

From
wolfgang@alle-noten.de
Date:
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

Best regards
Wolfgang Hamann

Re: Skip trigger?

From
Peter Devoy
Date:
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...

Re: Skip trigger?

From
Manuel Gómez
Date:
On Sat, Apr 30, 2016 at 1:38 AM,  <wolfgang@alle-noten.de> 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

Re: Skip trigger?

From
Stephen Cook
Date:
On 2016-04-30 02:08, wolfgang@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

Re: Skip trigger?

From
hamann.w@t-online.de
Date:
>> On 2016-04-30 02:08, wolfgang@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

Re: Skip trigger?

From
hamann.w@t-online.de
Date:
>> On Sat, Apr 30, 2016 at 1:38 AM,  <wolfgang@alle-noten.de> 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