Re: left join with smaller table or index on (XXX is not null) to avoid upsert

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> But seriously, it's doing what you told it to do. There might be
> corner cases where you need a trigger to fire for a row on change, and
> short-circuiting could cause things to fail in unexpected ways.

The other argument against doing this by default is that with
non-stupidly-written applications, the cycles expended to check for
vacuous updates would invariably be wasted. Even if the case did
come up occasionally, it's not hard at all to foresee that the extra
checking could be a net loss overall.

But having said that: 8.4 will provide a standard trigger that
short-circuits vacuous updates, which you can apply to tables in which
you think vacuous updates are likely. It's your responsibility to place
the trigger so that it doesn't interfere with any other trigger
processing you may have.

regards, tom lane