Re: PL/pgSQL 2

On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Joel Jacobson <joel(at)trustly(dot)com> wrote:
>
>> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify
>> exactly 1 row, as that's the most common use-case, and provide
>> alternative syntax to modify multiple or zero rows.
>
> I just embarked on wading through the 99 messages (so far) on this
> thread, so my apologies if this has already been addressed -- but I
> wanted to register a strong objection to making this the default in
> any rewrite. If we want to support a setting or a statement option
> for it, fine; but in my personal experience in a production
> environment with thousands of plpgsql functions, most functions
> written to deal with one row at a time were orders of magnitude
> slower than they needed to be -- I spent a lot of my time rewriting
> them to use set logic so that they could benefit from the
> optimizer's attention. Getting people to write things in a
> declarative style in the first place was difficult because so many
> of the programmers were so attached to the imperative style of
> coding; making it more difficult for people to Do The Right Thing
> is a bad idea IMO.

The common use-case I have in mind is when you have a function which
takes some kind of ID as an input param, which maps to a primary key
in some table, which you want to update.
If the where-clause would be incorrect and the update would update all
rows in the table, that would be a disaster, which is what I want to
prevent.
I think the benefit of a secure and convenient way of updating exactly
1 row outweights the reduced convenience of updating multiple rows
when you really want to update multiple rows.

Compare this to the normal psql prompt. How many million dollars would
you say the total cost would be for mistakes where someone forgets the
WHERE-clause of an UPDATE or a DELETE? :-)
It's the same type of mistake I want to prevent from in a convenient
way, and there is nothing more convenient than the default behavour.
That also means *all* users will get that behaviour even if they don't
explicitly request it, which is a good thing, because then they are
protected against the danger of not knowing how to make sure it
updated/deleted only one row.