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 <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
