Обсуждение: 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