Re: PL/pgSQL 2

On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>> No, but your code can have a bug.
>
> So the main use case is to allow buggy functions which are deployed
> to production without adequate testing to be detected? Bugs like
> not getting the primary key column(s) right? I think it would be
> great to have some way to generate an error if a given statement
> doesn't affect exactly one row, but the above is a pretty weak
> argument for making it a default behavior.

Instead of writing unit tests for such trivial things as updating one row
and testing if it got updated, it's better to make such unit tests
asserts instead,
which is exactly what we achieve if we provide a syntax to throw an error if
not exactly 1 row was affected.

>> Updating even two rows instead of one can have catastrophic effects.
>
> That's a different problem than Joel just said was his main
> concern. I was pointing out that the solution he was proposing was
> a very poor solution to the problem he said he was trying to solve.
> Can you imagine the damage if a function that updated every row in
> a table whenever anyone tried to update a single row by primary key
> made it past testing and staging phases into production? Depending
> on the table, it might not need to run more than a few times before
> the bloat ate all disk space and your production environment was
> totally hosed to the point of needing to delete everything from
> $PGDATA and restore from your last known good backup.

Sorry for being unclear, I didn't mean to suggest the main concern is
updating *all* rows.
The main concern is when you have a rather complex UPDATE WHERE clause,
aiming to update exactly one row. Some of the expressions might be
assertions, to just double-verify the values and to make it stand-out
you are checking
those expressions.