Re: Determining if a table really changed in a trigger

Поиск
Список
Период
Сортировка
От Miles Elam
Тема Re: Determining if a table really changed in a trigger
Дата
Msg-id CAALojA8UDWgUemDREMgPvNNuTV0ysV2-4T9cCCLmf86GNN2LqA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Determining if a table really changed in a trigger  (Marcos Pegoraro <marcos@f10.com.br>)
Список pgsql-general
Дерево обсуждения
On Tue, Oct 26, 2021 at 6:36 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

Maybe converting new and old records to json and text 
PERFORM * FROM (select ID, row_to_json(O.*)::text Old_Values, row_to_json(N.*)::text New_Values from old_table o full outer join new_table N using(ID) where Old_Values is distinct from New_Values) as differences LIMIT 1;

I have done this, but with casting to jsonb, which already supports the equality operator. Saved an extra cast to text. Also allows for easily excluding a column or two before comparing.

I never noticed a performance problem, but I was using this technique to see if a row had substantively changed, and if so, to cancel the write and subsequent trigger invocations by returning NULL in the before-trigger.

The trade off of conversions to jsonb by not writing and performing subsequent processing/writes due to later triggers was an obvious win for me, but your mileage may vary depending on your use case.

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

Предыдущее
От: Marcos Pegoraro
Дата:
Сообщение: Re: Determining if a table really changed in a trigger
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: Determining if a table really changed in a trigger