Update Trigger latency utilizing the IS DISTINCT FROM syntax

Поиск
Список
Период
Сортировка
От
Тема Update Trigger latency utilizing the IS DISTINCT FROM syntax
Дата
Msg-id 20131031152710.5a830134ae84016b0174832fdc1a3173.6c52abd660.wbe@email11.secureserver.net
обсуждение исходный текст
Список pgsql-performance
<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>if we have the following trigger:</div><div><br
/></div><div><b>CREATETRIGGER admin_update_trigger<br /> BEFORE UPDATE ON admin_logger_overflow<br /> FOR EACH ROW<br
/> WHEN ((old.start_date_time IS DISTINCT FROM new.start_date_time))<br />  EXECUTE PROCEDURE
update_logger_config();</b></div><div><br/></div><div>and the database call issues an: <b>update admin_logger_overflow
setstop_date_time = '2013-10-31 15:00:00'::timestamp where admin_update_id = 1;</b> Does the trigger fire? No,
Right?<br/></div><div><br /></div><div>if the next database call issues an: <b>update admin_logger_overflow set
start_date_time= '2013-10-31 13:59:58'::timestamp where admin_update_id = 1;</b> Does the trigger fire? Yes, No
doubt<br/></div><div><br /></div><div>but if the very next database call issues an: <b> update admin_logger_overflow
setstart_date_time = '2013-10-31 13:59:58'::timestamp, stop_date_time = '2013-10-31 16:29:37'::timestamp where
admin_update_id= 1;</b></div><div>where the start_date_time timestamp value is identical to the one in the prior update
statement,is it true that the admin_update_trigger is still being fired because the WHEN IS DISTINCT FROM condition
stillhas to be evaluated and depending upon its condition </div><div>the determination is made if the EXECUTE PROCEDURE
callis going to happen or not? Yes, Right?<br /></div><div><br /></div><div>We have processes that perform thousands
andthousands of these updates and these data ingest processes are taking a measurable performance hit when the trigger
isbeing fired repeatedly, as opposed to when this trigger is removed from the ingest workflow. </div><div><br
/></div><div>Doesremoving the start_date_time column from the update column list when the value is redundant circumvent
thetrigger call from happening, and thus reducing the performance hit on these update statements?</div><div><br
/></div><div>thanks</div><div><br/></div><div><br /></div></span> 

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
Следующее
От: Ben Hoyt
Дата:
Сообщение: Re: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)