Hi! On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger <mark.dil...@enterprisedb.com> 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
