Re: PL/pgSQL 2

Joel Jacobson <joel(at)trustly(dot)com> wrote:
> 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.

>> 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.

In that case FOUND works just fine.  A primary key value can't have
more than one matching row.

> 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.

By the time you find out that the number of rows affected is every
row in the table, you have horribly bloated the table and all its
indexes.  Causing a DML statement to abort when it sees a second
row is a completely different issue than what I (and I suspect most
others on the list) thought we were talking about, and would need
to affect far more than the PL.

> 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.

I don't.

> 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? :-)

Dunno, but that also tends to suggest a solution that isn't limited
to a PL would be beneficial.

> 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.

I think that changing the default behavior of SQL from set oriented
to something else is a horrible idea.  I absolutely, unequivocally
oppose that at the SQL or plpgsql level as harmful.  I understand
the need to check for this in various cases, and in fact the
application framework I designed at my previous job had Java
methods for doing DML with such a check included, named
InsertOneRow(), UpdateOneRow(), and DeleteOneRow().  Very useful.
If we can agree on a way to allow users to do the same in plpgsql,
fine -- but certainly not as the default default (word
intentionally repeated).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company