From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)trustly(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: PL/pgSQL 2 |
Date: | 2014-09-02 12:30:53 |
Message-ID: | CAFj8pRDk__tt1J5GPiAmyCb3rqiakT84t9J5arrZEvcLdSf3kw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
2014-09-02 14:16 GMT+02:00 Joel Jacobson <joel(at)trustly(dot)com>:
> On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > 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.
>
> All three suggestions are either too verbose, ugly or hackish.
>
It is main problem for me. I am thinking so verbosity is important. If it
is ugly, cannot to say. It is subjective.
> I write too much code every day in PL/pgSQL to find any other solution
> than the cleanest and simplest to be acceptable.
> I reckon there are those who mostly use the language to create
> aggregated reports or to run some kind of batch jobs.
> But I use it almost exlusively for OLTP, and then you most often
> update a single row, and if 0 or >1 rows are affected, it's an error.
>
It is valid only for UPDATE, not for DELETE. You can delete with FK and it
is common operation.
> Therefore, I wish the syntax for the most common use case to be as
> clean as possible, and there is nothing cleaner than plain UPDATE.
>
> Also, when showing a beginner the power of PL/pgSQL, it cannot be
> acceptable to have to write two rows to do something as simple as an
> update. All the suggestions above range between 2-3 rows (for DELETE,
> but I guess the syntax would be the same for UPDATE).
>
> For an in-depth discussion on this subject, please see
> http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/
>
It is way how to do COBOL from plpgsql. I am against it. Start to develop
new language what will support fast development, but it is wrong way for
plpgsql - and It is out my interest
>
> I have no good ideas though on what the syntax would look like to
> allow zero rows or multiple rows for an UPDATE though.
>
> It's much harder to come up with things to *add* to a syntax than what
> obvious ugliness you want to *remove*.
>
> If I had to guess though, I would think something in the end of the
> UPDATE command like a new keyword, could work. It wouldn't mess up the
> syntax too much, and wouldn't require an extra line of code.
>
> I strongly feel we should give a plain UPDATE without any extra lines
> of code or special syntax a default behaviour, which is different from
> "accept any number of affected rows".
> My definitive vote is to throw an error if not exactly 1 row was
> affected, and to provide a nice syntax to allow the other use cases.
> Right now it's the other way around, we never throw an error, and
> *always* have to check how many rows were affected. That means we
> *always* get both more lines of code and also uglier code in our
> applications, than we would if we optimized for the most common use
> case.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2014-09-02 12:38:31 | Re: PL/pgSQL 2 |
Previous Message | Heikki Linnakangas | 2014-09-02 12:29:25 | Re: PL/pgSQL 2 |