Re: Determining if a table really changed in a trigger

Hi!

On Wed, Oct 27, 2021 at 1:16 AM Mark Dilger
<mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> If Mitar finds that suppress_redundant_updates_trigger is sufficient, that may be a simpler solution. Thanks for mentioning it.
>
> The suppress_redundant_updates_trigger uses memcmp on the old and new rows. I don't know if memcmp will be sufficient in this case, since json can be binary unequal and yet turn out to be equal once cast to jsonb. I was using the rule and casting the json column to jsonb before comparing for equality.

Very interesting, I didn't know about that trigger. Memcmp is OK for
my use case. This is why I am considering *= as well.

I am guessing that if I am already doing a row comparison on every
UPDATE before my AFTER trigger so that I do not run the trigger (the
rule-based approach suggested by Mark), it is probably better to do
the row comparison as a BEFORE trigger which prevents the UPDATE from
even happening. I already pay for the row comparison so at least I
could prevent the disk write as well. Do I understand that correctly?

So the only remaining question is how to prevent my statement trigger
from running if no rows end up being changed by INSERT/UPDATE/DELETE
without having to use REFERENCING.

Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m