Re: Determining if a table really changed in a trigger
Hi! On Sat, Nov 6, 2021 at 2:43 PM Tom Lane wrote: > Mitar writes: > > Anyone? Any way to determine the number of affected rows in a statement > > trigger? > > Check the size of the transition relation. Yes, this is what we are currently doing, but it looks very inefficient if you want just the number, no? Or even if you want to know if it is non-zero or zero. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: Determining if a table really changed in a trigger
Mitar writes: > Anyone? Any way to determine the number of affected rows in a statement > trigger? Check the size of the transition relation. regards, tom lane
Re: Determining if a table really changed in a trigger
Hi! On Wed, Oct 27, 2021 at 12:46 AM Mark Dilger wrote: > I felt the same way about it, but after glancing quickly through the code and > docs nothing jumped out. The information is clearly available, as it gets > returned at the end of the UPDATE statement in the "UPDATE 0" OR "UPDATE 3", > but I don't see how to access that from the trigger. I might have to submit > a patch for that if nobody else knows a way to get it. (Hopefully somebody > will respond with the answer...?) Anyone? Any way to determine the number of affected rows in a statement trigger? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: Determining if a table really changed in a trigger
Hi! On Wed, Oct 27, 2021 at 12:56 PM Marcos Pegoraro wrote: >> 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. > > That is not true Sorry to be imprecise. In this thread I am interested in statement triggers, so I didn't mention this explicitly here. So statement triggers do not have NEW and OLD. But you can combine it with a row-level rule and this works then well together. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: Determining if a table really changed in a trigger
> > 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. > > That is not true create table test(i integer); create function test_old_new() returns trigger language plpgsql as $$ begin raise notice '% - %', old.i, new.i; return new; end;$$; CREATE TRIGGER testvalue BEFORE UPDATE OF i ON test FOR EACH ROW WHEN (((new.i)::integer = 5::integer)) EXECUTE PROCEDURE test_old_new(); > insert into test values(4) 1 row affected in 52 ms > update test set i = 6 1 row affected in 93 ms > update test set i = 5 6 - 5 ->raise notice of procedure test_old_new was called only when new.i = 5 1 row affected in 48 ms > >
Re: Determining if a table really changed in a trigger
Mitar schrieb am 26.10.2021 um 09:05: > I would like to test inside trigger_function if the table really > changed. I have tried to do: > > PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL > (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; > IF FOUND THEN > ... changed ... > END IF; > > But this fails if the table contains a JSON field with the error: > > could not identify an equality operator for type json > If you use jsonb (which is recommended over json anyway), then this would work. I would probably use a row level trigger instead of a statement level trigger Then compare the two records using IS DISTINCT FROM if new is distinct from old then end if; > I want to skip trivial updates (those which have not changed anything). It seems you might want to use the pre-defined function suppress_redundant_updates_trigger() https://www.postgresql.org/docs/current/functions-trigger.html Might be faster than a self written trigger. Regards Thomas
Re: Determining if a table really changed in a trigger
Hi! On Wed, Oct 27, 2021 at 3:56 AM Michael Lewis wrote: > If you end up with no rows changing from an insert or delete, something seems > awry. Unless you mean 0 rows affected. Isn't this the same? Isn't the number of rows affected the same as the number of rows changing? For example: DELETE FROM my_table where i=100; would not change anything in your example. But probably this is just terminology I have used badly. > Do after statement triggers still execute? I suppose they very well might. I have run the following and it seems statement triggers still execute even if nothing changes: postgres=# create table my_table (i integer, j json); CREATE TABLE postgres=# insert into my_table select gs::integer, '{"key":1}'::json from generate_series(1,3) gs; INSERT 0 3 postgres=# create function my_table_func () returns trigger as $$ declare have_rows boolean; begin raise warning 'trigger called'; if (tg_op = 'INSERT') then select true into have_rows from new_values limit 1; if have_rows then raise warning 'rows have changed'; end if; elsif (tg_op = 'UPDATE' or tg_op = 'DELETE') then select true into have_rows from old_values limit 1; if have_rows then raise warning 'rows have changed'; end if; end if; return null; end $$ language plpgsql; CREATE FUNCTION postgres=# create trigger my_table_trig_insert after insert on my_table referencing new table as new_values for each statement execute function my_table_func(); CREATE TRIGGER postgres=# create trigger my_table_trig_update after update on my_table referencing old table as old_values for each statement execute function my_table_func(); CREATE TRIGGER postgres=# create trigger my_table_trig_delete after delete on my_table referencing old table as old_values for each statement execute function my_table_func(); CREATE TRIGGER postgres=# update my_table set j = '{"key":2}'::jsonb; WARNING: trigger called WARNING: rows have changed UPDATE 3 postgres=# update my_table set j = '{"key":2}'::jsonb; WARNING: trigger called WARNING: rows have changed UPDATE 3 postgres=# create trigger z_min_update before update on my_table for each row execute function suppress_redundant_updates_trigger(); CREATE TRIGGER postgres=# update my_table set j = '{"key":2}'::jsonb; WARNING: trigger called UPDATE 0 postgres=# update my_table set j = '{"key":3}'::jsonb; WARNING: trigger called WARNING: rows have changed UPDATE 3 postgres=# delete from my_table where i = 100; WARNING: trigger called DELETE 0 postgres=# insert into my_table select * from my_table where i = 100; WARNING: trigger called INSERT 0 0 > Would the statement even execute if no rows get updated and that is prevented > with before update? I would assume null is being returned rather than old if > the trigger finds the row to be identical. It looks like a statement trigger is always called, but checking REFERENCING matches affected rows as returned by the psql shell. Also notice how the number of affected rows is non-zero for trivial update before the use of suppress_redundant_updates_trigger, both through REFERENCING and through the psql shell. That matches also documentation: > ..., a trigger that is marked FOR EACH STATEMENT only executes once for any > given operation, regardless of how many rows it modifies (in particular, an > operation that modifies zero rows will still result in the execution of any > applicable FOR EACH STATEMENT triggers). So it would be really cool to be able to access the number of affected rows inside a trigger without the use of REFERENCING. Given that WHEN condition of a statement trigger is currently mostly useless (because the condition cannot refer to any values in the table) maybe providing something like AFFECTED variable in there would be the way to go? So one could write: CREATE TRIGGER my_trigger AFTER UPDATE ON my_table FOR EACH STATEMENT WHEN AFFECTED <> 0 EXECUTE FUNCTION my_table_func(); Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: Determining if a table really changed in a trigger
If you end up with no rows changing from an insert or delete, something seems awry. Unless you mean 0 rows affected. Do after statement triggers still execute? I suppose they very well might. Would the statement even execute if no rows get updated and that is prevented with before update? I would assume null is being returned rather than old if the trigger finds the row to be identical.
Re: Determining if a table really changed in a trigger
Hi! On Wed, Oct 27, 2021 at 1:16 AM Mark Dilger 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
Re: Determining if a table really changed in a trigger
> On Oct 26, 2021, at 4:01 PM, Michael Lewis wrote: > > Does this perform differently from suppress_redundant_updates_trigger? > > https://www.postgresql.org/docs/current/functions-trigger.html 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. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Determining if a table really changed in a trigger
Does this perform differently from suppress_redundant_updates_trigger? https://www.postgresql.org/docs/current/functions-trigger.html
Re: Determining if a table really changed in a trigger
> On Oct 26, 2021, at 3:39 PM, Mitar wrote: > > On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger > wrote: >> 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? I felt the same way about it, but after glancing quickly through the code and docs nothing jumped out. The information is clearly available, as it gets returned at the end of the UPDATE statement in the "UPDATE 0" OR "UPDATE 3", but I don't see how to access that from the trigger. I might have to submit a patch for that if nobody else knows a way to get it. (Hopefully somebody will respond with the answer...?) > 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 ... There is no reason to prefer my spelling of that over yours. I didn't put much thought into it, but rather just wrote it the first way that occurred to me. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Determining if a table really changed in a trigger
Hi! On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger 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
Re: Determining if a table really changed in a trigger
> On Oct 26, 2021, at 1:34 PM, Mitar wrote: > > Hi! > > On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger > wrote: >> I can't tell from your post if you want the trivial update to be performed, >> but if not, would it work to filter trivial updates as: > > No, I want to skip trivial updates (those which have not changed > anything). But my trigger is per statement, not per row. So I do not > think your approach works there? So this is why I am then making a > more complicated check inside the trigger itself. 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: rules=# create table my_table (i integer, j json); CREATE TABLE rules=# insert into my_table rules-# select gs::integer, '{"key":1}'::json rules-# from generate_series(1,3) gs; INSERT 0 3 rules=# create function my_table_func () returns trigger as $$ rules$# declare rules$# have_rows boolean; rules$# begin rules$# select true into have_rows from old_values limit 1; rules$# if have_rows then rules$# raise warning 'rows have changed'; rules$# else rules$# raise warning 'no rows changed'; rules$# end if; rules$# return null; rules$# end rules$# $$ language plpgsql; CREATE FUNCTION rules=# create trigger my_table_trig after update on my_table rules-# referencing old table as old_values rules-# for each statement rules-# execute function my_table_func(); CREATE TRIGGER rules=# update my_table set j = '{"key":2}'::jsonb; 2021-10-26 13:51:58.139 PDT [34352] WARNING: rows have changed 2021-10-26 13:51:58.139 PDT [34352] CONTEXT: PL/pgSQL function my_table_func() line 7 at RAISE WARNING: rows have changed UPDATE 3 rules=# create rule filter_trivial_updates as on update to my_table rules-# where new.i = old.i rules-# and new.j::jsonb = old.j::jsonb rules-# do instead nothing; CREATE RULE rules=# update my_table set j = '{"key":2}'::jsonb; 2021-10-26 13:51:58.143 PDT [34352] WARNING: no rows changed 2021-10-26 13:51:58.143 PDT [34352] CONTEXT: PL/pgSQL function my_table_func() line 9 at RAISE WARNING: no rows changed UPDATE 0 rules=# update my_table set j = '{"key":3}'::jsonb; 2021-10-26 13:51:58.143 PDT [34352] WARNING: rows have changed 2021-10-26 13:51:58.143 PDT [34352] CONTEXT: PL/pgSQL function my_table_func() line 7 at RAISE WARNING: rows have changed UPDATE 3 Note that there is a performance cost to storing the old rows using the REFERENCING clause of the trigger, so you'll want to think about all your various options and decide between them. I am not in a position to make performance recommendations for your schema. However, if updates tend to be target at small sets of rows, and if the rule is used to further filter out trivial updates, this might be cheap. 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. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Determining if a table really changed in a trigger
Hi! On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger wrote: > I can't tell from your post if you want the trivial update to be performed, > but if not, would it work to filter trivial updates as: No, I want to skip trivial updates (those which have not changed anything). But my trigger is per statement, not per row. So I do not think your approach works there? So this is why I am then making a more complicated check inside the trigger itself. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: Determining if a table really changed in a trigger
> On Oct 26, 2021, at 12:05 AM, Mitar wrote: > > Hi! > > I have a trigger like: > > CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW > TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE > FUNCTION trigger_function; > > I would like to test inside trigger_function if the table really > changed. I have tried to do: > > PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL > (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; > IF FOUND THEN > ... changed ... > END IF; > > But this fails if the table contains a JSON field with the error: > > could not identify an equality operator for type json > > The table has an unique index column, if that helps. I can't tell from your post if you want the trivial update to be performed, but if not, would it work to filter trivial updates as: CREATE RULE filter_trivial_updates AS ON UPDATE TO my_table WHERE new.i = old.i AND new.j = old.j AND ... DO INSTEAD NOTHING; You could replace the i, j, ... above with whichever columns you have, and specify the casts and equality operators you want for the json column (such as a cast to jsonb and equality.) The advantage here, if you do it right, is that the trigger doesn't have to check whether the row has changed, because the trigger will only fire when a change has occurred. You might try it and compare the performance against other solutions. The general idea is shown here: rules=# create table my_table (i integer, j json); CREATE TABLE rules=# insert into my_table rules-# select gs::integer, '{"key":1}'::json rules-# from generate_series(1,3) gs; INSERT 0 3 rules=# create function my_table_func () returns trigger as $$ rules$# begin rules$# raise warning '[old.i=%, old.j=%] => [new.i=%, new.j=%]', rules$# old.i, old.j, new.i, new.j; rules$# return new; rules$# end rules$# $$ language plpgsql; CREATE FUNCTION rules=# create trigger my_table_trig before update on my_table rules-# for each row execute function my_table_func(); CREATE TRIGGER rules=# update my_table set j = '{"key":2}'::jsonb; WARNING: [old.i=1, old.j={"key":1}] => [new.i=1, new.j={"key": 2}] WARNING: [old.i=2, old.j={"key":1}] => [new.i=2, new.j={"key": 2}] WARNING: [old.i=3, old.j={"key":1}] => [new.i=3, new.j={"key": 2}] UPDATE 3 rules=# create rule filter_trivial_updates as on update to my_table rules-# where new.i = old.i rules-# and new.j::jsonb = old.j::jsonb rules-# do instead nothing; CREATE RULE rules=# update my_table set j = '{"key":2}'::jsonb; UPDATE 0 rules=# update my_table set j = '{"key":3}'::jsonb; WARNING: [old.i=1, old.j={"key": 2}] => [new.i=1, new.j={"key": 3}] WARNING: [old.i=2, old.j={"key": 2}] => [new.i=2, new.j={"key": 3}] WARNING: [old.i=3, old.j={"key": 2}] => [new.i=3, new.j={"key": 3}] UPDATE 3 — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Determining if a table really changed in a trigger
Hi! Thank you everyone for your responses. I investigated them. I have also found composite type operators [1]. There is no way to tell the EXCEPT operator to use *= as its equality operator? *EXCEPT would seem to be a useful operator to have. :-) I am not sure about performance though. EXCEPT is generally fast, but probably because it can use indices, not sure how fast *= is, given that it is comparing binary representations. What is experience with this operator of others? Mitar [1] https://www.postgresql.org/docs/current/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: Determining if a table really changed in a trigger
> On 26 Oct 2021, at 16:16, Marcos Pegoraro wrote: > > >> Don’t use this approach with JSON (as opposed to JSONB) type fields though, >> a single extra space in the JSON structure would already lead to a >> difference, as would other formatting differences. >> > I don´t think two equal values being converted to json will be different in > any way. If row_to_json of both are different, I suppose both record really > are different, no ? For row_to_json, as it’s the system that combines the fields in a row into a JSON structure and it probably would do that in the same way each time. The OP however has a field of type JSON in their table, and that can contain the same information between the OLD and NEW fields formatted in a slightly different way. For example: => with x as ( select '{ "x": 1, "y": 2 }'::json union all select '{ "y": 2, "x": 1 }'::json ) select row(x.json)::text, md5(row(x.json)::text) from x; row | md5 +-- ("{ ""x"": 1, ""y"": 2 }") | 84df40e8660dcf371d89dbf5d6a61c3d ("{ ""y"": 2, ""x"": 1 }") | abd6db88c2526be6ea97570aeec7e020 (2 rows) Whereas: => with x as ( select '{ "x": 1, "y": 2 }'::jsonb union all select '{ "y": 2, "x": 1 }'::jsonb ) select row(x.jsonb)::text, md5(row(x.jsonb)::text) from x; row| md5 --+-- ("{""x"": 1, ""y"": 2}") | d5a6dbdec7a5bfe0dc99e090db30322e ("{""x"": 1, ""y"": 2}") | d5a6dbdec7a5bfe0dc99e090db30322e (2 rows) Alban Hertroys -- There is always an exception to always.
Re: Determining if a table really changed in a trigger
> > > Don’t use this approach with JSON (as opposed to JSONB) type fields > though, a single extra space in the JSON structure would already lead to a > difference, as would other formatting differences. > > I don´t think two equal values being converted to json will be different in any way. If row_to_json of both are different, I suppose both record really are different, no ?
Re: Determining if a table really changed in a trigger
> On 26 Oct 2021, at 9:05, Mitar wrote: > > Hi! > > I have a trigger like: > > CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW > TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE > FUNCTION trigger_function; > > I would like to test inside trigger_function if the table really > changed. I have tried to do: > > PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL > (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; > IF FOUND THEN > ... changed ... > END IF; > > But this fails if the table contains a JSON field with the error: > > could not identify an equality operator for type json Perhaps if you store an extra column containing a hash (for example MD5) of the row contents (minus the hash column, obviously)? You can put an index on the hash and match between OLD and NEW tables which ones changed. When calculating the hash, you would have to specify the column names to exclude the hash itself, so something like this: md5(row(col1, col2, col3)::text) The row-to-text conversion already takes care of converting JSONB(!) to text. Don’t use this approach with JSON (as opposed to JSONB) type fields though, a single extra space in the JSON structure would already lead to a difference, as would other formatting differences. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: Determining if a table really changed in a trigger
On Tue, Oct 26, 2021 at 6:36 AM Marcos Pegoraro 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.
Re: Determining if a table really changed in a trigger
> > > PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL > (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; > IF FOUND THEN > ... changed ... > END IF; > > 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;
Re: Determining if a table really changed in a trigger
On Tue, Oct 26, 2021 at 12:05 AM Mitar wrote: > > But this fails if the table contains a JSON field with the error: > > could not identify an equality operator for type json > Thus it is not possible to use whole row comparisons. You will need to write the code to manually check equality on each column. To check the json column you will probably want to cast to jsonb (if it isn't already) and then cast that to text and use a text equality check. Since you are doing a statement trigger that means writing "SELECT col1, col2, etc...". > The table has an unique index column, if that helps. > > That would be assumed since you need to be able to identify records whose contents might otherwise be identical. David J.
Determining if a table really changed in a trigger
Hi! I have a trigger like: CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_function; I would like to test inside trigger_function if the table really changed. I have tried to do: PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; IF FOUND THEN ... changed ... END IF; But this fails if the table contains a JSON field with the error: could not identify an equality operator for type json The table has an unique index column, if that helps. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m