Обсуждение: Elide null updates

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.

James

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

Andrew Dunstan wrote:
> 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.
OK I'll try again.

Suppose we do an update.

This will:- write the new image and do the MVCC housekeeping- logically lock the updated row- fire update triggers

Suppose the pre-update trigger elides the update - ALL of the above is 
removed.

Now, one of the objections to making it the default behaviour is that 
the side effects (such as the lock and the trigger) might be desirable, 
or at least that removing them is a change in behaviour.

I'm wondering whether it would be possible to remove the physical update 
but retain the logical side effects,, so this argument about changed 
semantics is removed, and the only issue is whether the cost of 
identifying the noop update is worthwhile given the savings achieved, 
which will be application dependent.

James

(I suspect that if you step back from the implementation of the SQL 
engine as a series of procedural steps on rows - and think of it in 
terms of relational set algebra, then it is entirely defensible to elide 
such an update as a matter of course and that it SHOULD happen - but 
then there is always fun and games around inserting duplicates too, and 
I suspect most of us don't think in algebra terms)

James Mansion <james@mansionfamily.plus.com> writes:
> 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.

How about we wait a few releases and see if anyone uses the trigger,
rather than wasting time now on an argument about integrating an
as-yet-unproven feature?

(You *are* wasting our time, btw, because we already had this
discussion.  Until there's some field experience there is no new
evidence available to change the conclusion.)
        regards, tom lane