Re: Elide null updates

James Mansion wrote:
> I saw on a summary for 8.4 that there's a generic function for use as 
> a before row trigger that will elide null updates (ie replacement with 
> an identical row).
>
> I can see that this is great - but I was wondering if it should be 
> more integrated and turned on in the db schema.
>
> Trivially, doing so would mean that there is less of an issue trying 
> to integrate with a custom user before trigger, although that's really 
> no more than an inconvenience.
>
> I understand that there is an argument for not making it the default 
> behaviour given that trigger execution and locking are both affected 
> if we do elide the update completely, but it occured to me that while 
> I might want the trigger and locking behaviour, I probably never want 
> the actual database image copy to happen. Doing so will needlessly 
> bloat the database file and give the vacuum procedure work to do - and 
> it seems interfere with the new optimisations relating to pages that 
> are all visible in all transactions.
>
> Would it be possible to determine a null update cheaply and retain the 
> locking and trigger execution, but elide the actual row copy - and in 
> particular the associated impact in terms of setting status flags etc?
>
> I guess this would need to be handled at a lower level than the 
> trigger approach - and would need an option that is integrated into 
> the schema, so we can elide the copy, and optionally the trigger 
> execution, and optionally the lock.
>
>

I don't follow what you're saying.

If an update is skipped by a trigger, nothing new is written to disk, 
and there should be nothing to vacuum from it. That's why this trigger 
can speed up certain update queries enormously.


cheers

andrew