too smart update (was: left join with smaller table or index on (XXX is not null) to avoid upsert)

On Mon, 19 Jan 2009 01:18:35 -0700
"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Mon, Jan 19, 2009 at 12:53 AM, Grzegorz Jaśkiewicz
> <gryzman(at)gmail(dot)com> wrote:
> > 2009/1/19 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> >> Submit a patch. :)
> >>
> >> 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.

> > as far as my little knowledge about pg goes, that would be just
> > another addition to planner. <daydreaming> Say - when there's
> > more than X % of value Y, and we do set column X to Y, it could
> > add that 'where'. But what if we have more WHERE statements, and
> > they are quite contradictory, etc, etc. It could actually do
> > more damage than good. (yes, I do have quite few more 'against'
> > than for)</daydreaming>

> Yes, but what about a table with an update trigger on it that does
> some interesting bit of housekeeping when rows are updated? It
> might be that you have ten rows, all with the number 4 in them,
> and you update the same field again to 4. With the trigger some

But what should be the expected/standard behaviour?
It seems that unless an update should fire triggers just if columns
get updated... things will start to be a bit non-deterministic.
You'll have to take into account rules etc...

eg. FOUND is set true when conditions are met, not when columns are
changed etc...

--
Ivan Sergio Borgonovo
http://www.webthatworks.it