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

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