Re: Determining if a table really changed in a trigger

On Tue, Oct 26, 2021 at 6:36 AM Marcos Pegoraro <marcos(at)f10(dot)com(dot)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.