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