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