Hi! On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger <mark.dilger@xxxxxxxxxxxxxxxx> wrote: > The trigger "my_table_trig" in the example is a per row trigger, but it exists only to demonstrate that the rule has filtered out the appropriate rows. You can use the rule "my_table_rule" as written and a per statement trigger, as here: Oh, very interesting. I thought that this is not possible because WHEN condition on triggers does not have NEW and OLD. But this is a very cool way to combine rules with triggers, where a rule can still operate by row. Thank you for sharing this! > Note that there is a performance cost to storing the old rows using the REFERENCING clause of the trigger Yea, by moving the trivial update check to a rule, I need REFERENCING only to see if there were any changes at all. This seems a bit excessive. Is there a way to check if any rows have been affected by an UPDATE inside a per statement trigger without using REFERENCING? > Note that I used equality and inequality rather than IS DISTINCT FROM and IS NOT DISTINCT FROM in the design, but you should think about how NULL values (old, new, or both) will behave in the solution you choose. I have just now tested the following rule: CREATE RULE filter_trivial_updates AS ON UPDATE TO my_table WHERE NEW *= OLD DO INSTEAD NOTHING; and it looks like it works well. It sidesteps the issue around equality operator for type json and also just compares nulls as just another value (which I would like). Not sure how it is performance wise in comparison with listing all columns and using the regular equality operator. I also notice that you check if a table has any rows with: SELECT true INTO have_rows FROM old_values LIMIT 1; IF have_rows THEN ... Is this just a question of style or is this a better approach than my: PERFORM * FROM old_values LIMIT 1; IF FOUND THEN ... Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
