Re: PL/pgSQL 2

On 9/2/14 4:26 PM, Kevin Grittner wrote:
> Joel Jacobson <joel(at)trustly(dot)com> wrote:
>> 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.

No, but your code can have a bug. INTO rejecting any queries returning
more than one row helps, though, but having to write RETURNING TRUE
INTO _OK; is not pretty either.

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

Updating even two rows instead of one can have catastrophic effects.

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

Yeah, it doesn't necessarily need to be the default default (and I see a
lot of people saying it shouldn't be). Even having a per-query modifier
would be better than the current behaviour.

.marko