Re: PL/pgSQL 2

On Mon, Sep 1, 2014 at 11:24 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> -many.
>
> Look at the *disaster* the few minor changes in python3 were. It's now,
> years after, only starting to get used again.
>
> You're going to have to find a more gradual way of doing this.

I agree this is a valid concern, and is also something I've taken into
account already.

The things I would like to see change, won't break any of my own code,
and probably not much of others either.

Let's examine my list and see what impact if would have to modern
plpgsql code written according to best practices:

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

If you application already aims to modify 1 row exactly at most places
in the code where you have UPDATE/INSERT/DELETE,
then making that the default behaviour of the default syntax would be
an improvement, as you wouldn't need special syntax for the default
use-case.
Instead, by providing a way to do the less common use-case of
modifying <>1 row, using an alternative way, we make it possible to
rewrite any existing code.
The amount of code we need to rewrite is minimized by optimizing the
syntax for the most common use-case.

> + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
> STRICT only works if no rows should be an error, but there is currently no
> nice way if no rows OR exactly 1 row should be found by the query.

This wouldn't have any impact for most applications, except when
things go wrong, and if the query *would* return more than 1 row you
probably want an error to be thrown, that's the most common use-case.
By making it necessary to explicity say you are OK with more than 1
row, we again optimize the syntax for the most common use-case, but
provide an alternative way of allowing the corner-case.

> + Change all warnings into errors
The warnings should of course be errors, if we wouldn't need to worry
about legacy code, which we don't with plpgsql2.

In summary, if you write new code, it will work without any changes in
most of the cases, and in the cases where you need to change it, there
is a very good valid reason why you want to do that anyway, as the
alternative is either insecure or ugly.

Also, a stricter language with fewer alternative obsolete syntax
variations and optional settings means the lines of code and the
complexity can be reduced.

Compared with the changes in python3, the changes between python2 and
python3 *did* break compatibility and all code had to be rewritten. In
contrast, most plpgsql functions won't need to be rewritten, only a
few of them will need to be rewritten, which is acceptable.

Another difference when comparing it to python3 is the possibility to
mix plpgsql and plpgsql2 functions within the same system. With
python3, you can't use existing python2 modules, but new plpgsql2
functions can of course call any existing plpgsql functions.

The "gradual way" of doing this is to add plpgsql2, and then write all
new code in the language, and then port function by function from
plpgsql to plpgsql2, without any deadlines.