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 th
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
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",
>
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.
>
> Th
>
> 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 te
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
> ...
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
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
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 w
> 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 solut
Does this perform differently from suppress_redundant_updates_trigger?
https://www.postgresql.org/docs/current/functions-trigger.html
> 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
> o
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,
> 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 (th
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 state
> 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 trig
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
> 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 valu
>
>
> 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
> 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_fu
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 distin
>
>
> 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_
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 eac
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
24 matches
Mail list logo
