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

On Mon, Jan 19, 2009 at 12:53 AM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
> 2009/1/19 Scott Marlowe <scott.marlowe@gmail.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 other
processing gets kicked off and some maintenance script picks up those
values and does something.  If the db autoshort-circuited like you
want, the trigger would never fire.  According to the strictest
interpretation, setting a value from 4 to 4 is still a change.  But
the database just changed the rules underneath you.

It's a prime example of fixing a problem created by not knowing how
the database works, and creating a possible problem for people who do
know how it works.