Re: PL/pgSQL 2

2014-09-01 11:04 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:

> Hi,
>
> For those of you who use PL/pgSQL every day, I'm quite certain you all
> feel there are a number of things you would like to change in the language,
> but realize it cannot be achieved without possibly breaking compatibility,
> at least in theory. Even though you own code would survive the change,
> there might be code somewhere in the world which would break. This is of
> course not acceptable and that's why we have the current status quo of
> development, or at least not far away from a status quo.
>
> So instead of continue to adding optional settings to the config file,
> and instead of killing discussions around what can be done by bringing up
> the backwards-compatibility argument, let's instead fork the language and
> call it plpgsql2. Since no code is yet written in plpgsql2, we can start of
> from a clean sheet, and no good ideas need to be killed due to
> backwards-compatibility concerns.
>
> The interest for such a project is probably limited to a small number of
> companies/people around the world, as most users are probably perfectly
> happy with the current version of plpgsql, as they only use it
> occasionally and not every day like we do at my company.
>
> Just like with plpgsql, once released, plpgsql2 cannot break
> compatibility with future versions, so we only have one chance to carefully
> think though what we would like to change in the language.
>
> From the top of my head, these are Things I personally would want to see
> in plpgsql2:
> + 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.
> + 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.
> + Change all warnings into errors
>

last paragraph is some what I dislike on your proposal. Why:

plpgsql is relative good mix of simplified ADA -- there are no too complex
statement, no too much keywords, it is language that is simple to learn.
Second part of mix is PostgreSQL SQL. It does same things what does in plan
SQL.

Your proposal change it. It is not good idea.

Exactly clean solution is possible now

DELETE FROM tab WHERE xx = somevar;
GET DIAGNOSTICS rc = ROW_COUNT;
IF rc <> 1 THEN
RAISE EXCEPTION
END IF;

It is absolutely clean, absolutely readable. But it is verbose - yes,
agree, maybe too much. But verbosity is basic stone of ADA and plpgsql too.
It is what I like on plpgsql.

What we can do better?

1. we can implement a conditional RAISE

DELETE FROM tab WHERE xx = somevar;
GET DIAGNOSTICS rc = ROW_COUNT;
RAISE EXCEPTION 'some' WHEN rc <> 0;

It is relatively natural and we use similar construct in CONTINUE statement.

2. What can be next? We can implement some idiom (shortcut) for GET
DIAGNOSTICS

DELETE FROM tab WHERE xx = somevar;
RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1;

3. What next? Maybe some notations -

-- ** ensure_exact_one_row
DELETE FROM tab WHERE xx = somevar;

But default will be same as in plain SQL.

Regards

Pavel

p.s. I dislike some flags to SQL statements .. like STRICT it increase a
complexity of PL parser, and it increase a distance between SQL and PLPGSQL
SQL.

>
> These are small changes, probably possible with just a few hundred lines
> of code in total, which also should be the ambition, as larger changes
> would never survive during time as it would require too much efforts to
> keep up with the main project. Secondly, I trust plpgsql mainly because
> it's being used by a lot of people in a lot of production systems, the same
> would not hold true for plpgsql2 for the first years of existence, so we
> who would use it in production systems must understand every single line of
> code changed and feel the risk of possible bugs and their impact are within
> acceptable boundaries.
>
> I can probably think of a few more things, but these are the major
> annoyances.
>
> Please share your wish list of things you would want in plpgsql2 which are
> not possible to implement in plpgsql because they could possibly break
> compatibility.
>
> Regards, Joel
>
>