Обсуждение: 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
On Tue, Oct 26, 2021 at 12:05 AM Mitar <mmitar@gmail.com> 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.
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;
On Tue, Oct 26, 2021 at 6:36 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
Maybe converting new and old records to json and textPERFORM * 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.
> On 26 Oct 2021, at 9:05, Mitar <mmitar@gmail.com> 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 wouldalready 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.
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 ?
> On 26 Oct 2021, at 16:16, Marcos Pegoraro <marcos@f10.com.br> wrote:
>
>
>> Don’t use this approach with JSON (as opposed to JSONB) type fields though, a single extra space in the JSON
structurewould 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
thatin 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
NEWfields 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.
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
> On Oct 26, 2021, at 12:05 AM, Mitar <mmitar@gmail.com> 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
updatesas:
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
wantfor 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
thetrigger 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
Hi! On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger <mark.dilger@enterprisedb.com> 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 trivialupdates 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
> On Oct 26, 2021, at 1:34 PM, Mitar <mmitar@gmail.com> wrote:
>
> Hi!
>
> On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger
> <mark.dilger@enterprisedb.com> 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
trivialupdates 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
filteredout 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
wantto think about all your various options and decide between them. I am not in a position to make performance
recommendationsfor your schema. However, if updates tend to be target at small sets of rows, and if the rule is used
tofurther 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
shouldthink 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
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
> On Oct 26, 2021, at 3:39 PM, Mitar <mmitar@gmail.com> wrote: > > On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger > <mark.dilger@enterprisedb.com> 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 isclearly available, as it gets returned at the end of the UPDATE statement in the "UPDATE 0" OR "UPDATE 3", but I don'tsee how to access that from the trigger. I might have to submit a patch for that if nobody else knows a way to getit. (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 itthe first way that occurred to me. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Does this perform differently from suppress_redundant_updates_trigger?
> On Oct 26, 2021, at 4:01 PM, Michael Lewis <mlewis@entrata.com> 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 mentioningit. The suppress_redundant_updates_trigger uses memcmp on the old and new rows. I don't know if memcmp will be sufficient inthis case, since json can be binary unequal and yet turn out to be equal once cast to jsonb. I was using the rule andcasting the json column to jsonb before comparing for equality. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi! On Wed, Oct 27, 2021 at 1:16 AM Mark Dilger <mark.dilger@enterprisedb.com> wrote: > If Mitar finds that suppress_redundant_updates_trigger is sufficient, that may be a simpler solution. Thanks for mentioningit. > > The suppress_redundant_updates_trigger uses memcmp on the old and new rows. I don't know if memcmp will be sufficientin this case, since json can be binary unequal and yet turn out to be equal once cast to jsonb. I was using therule 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
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.
Hi!
On Wed, Oct 27, 2021 at 3:56 AM Michael Lewis <mlewis@entrata.com> 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
isbeing 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
rowsit modifies (in particular, an operation that modifies zero rows will still result in the execution of any
applicableFOR 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
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
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
Hi! On Wed, Oct 27, 2021 at 12:56 PM Marcos Pegoraro <marcos@f10.com.br> 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
Hi! On Wed, Oct 27, 2021 at 12:46 AM Mark Dilger <mark.dilger@enterprisedb.com> wrote: > I felt the same way about it, but after glancing quickly through the code and docs nothing jumped out. The informationis 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 wayto 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
Mitar <mmitar@gmail.com> 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
Hi! On Sat, Nov 6, 2021 at 2:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Mitar <mmitar@gmail.com> 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