Re: minimal update

On Fri, Nov 02, 2007 at 11:49:38AM -0400, Andrew Dunstan wrote:
> For some time I have been working on removing some inefficiencies
> from a large DW-type app.  This app does a large daily batch update,
> and this is what is the major bottleneck.  One of the things I have
> been doing is to remove unnecessary updates (which are particualrly
> expensive in our index-rich setting).  Several times now I have
> wished that there was a switch on the UPDATE command that said "do
> minimal instead of maximal updating".  i.e., don't update records
> with identical replacements.  At the moment I have to write things
> like:
> 
>    update tname set foo = bar ...  where foo is null or foo <> bar
>    ...

One way I've done this is make RULEs which basically drop non-updating
"UPDATEs" on the floor.

CREATE RULE foo_drop_empty_updates AS   ON UPDATE TO foo   WHERE ROW(OLD.*)::foo IS NOT DISTINCT FROM ROW(NEW.*)::foo
DOINSTEAD NOTHING;
 

It's pretty easy to automate rule creation, but since Postgres doesn't
have DDL triggers, it's also a bit of a foot gun.

By the way, the above has what I think of as an infelicity in 8.2.5,
namely that you need non-obvious contortions to get it to work.  I'm
thinking OLD IS NOT DISTINCT FROM NEW should Just Work(TM).

> This becomes more than tedious when the update might be setting thirty 
> or forty fields, and I have to write such tests for each of them.  It 
> would be so much nicer to be able to write something like:
> 
>    update tname minimally set foo = bar ...
> 
> Is this an insane idea, or would it be possible, practical and useful?

I don't know about the sanity, but I've done it a couple of places :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate