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
DO INSTEAD 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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