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