Re: [GENERAL] Skip trigger?

2016-05-01 Thread hamann . w 

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

2016-05-01 Thread hamann . w 
>> 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?

2016-04-30 Thread Stephen Cook 
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?

2016-04-30 Thread Manuel Gómez 
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?

2016-04-30 Thread Peter Devoy 
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

[GENERAL] Skip trigger?

2016-04-30 Thread wolfgang 
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general