Subject: Re: Why does the PL/pgSQL compiler do this?
To: Michael Moore <michaeljmoore@gmail.com>
References: 
 <CACpWLjOkzeKNNLccAnR7EyMYH+4w8SnhEve43rD+VLoXQ4ROEw@mail.gmail.com>
 <CAKFQuwYjBuTC59cnndRBb=42g0Y4DLQDx1Tm0JSROGTq0R0mFA@mail.gmail.com>
 <CACpWLjOVudD-7JEVu=u4UUMWLQeAoQzfAiPwj9xjPnBqWHxKnw@mail.gmail.com>
 <CACpWLjMz+wvWLFQnaiA6pamqG2YJuahSWRhf-ov+Dfd7uXf8pw@mail.gmail.com>
 <0520e6e1-8708-f58a-af0b-5624df7d01cf@aklaver.com>
 <CACpWLjNjuiEp-PnHH=NqXQ=BxX9H4GOnxoQj-9pWuCGP-7YbXQ@mail.gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 postgres list <pgsql-sql@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <98b610a2-6de4-443e-69cb-09edc8052e8f@aklaver.com>
Date: Mon, 31 Oct 2016 18:20:45 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CACpWLjNjuiEp-PnHH=NqXQ=BxX9H4GOnxoQj-9pWuCGP-7YbXQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 10/31/2016 06:09 PM, Michael Moore wrote:
> Thanks Adrian, but is  ROLLBACK  *_ever_* possible in PL/pgSQL? My
> understanding is, "No".

Well not directly. This is where the memory faded. As I understand it 
pl/pgsql uses savepoints under the hood for:

https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

When trying to figure this out in the past I found:

RollbackAndReleaseCurrentSubTransaction();

in

pl_exec.c

So you are correct.

>
> On Mon, Oct 31, 2016 at 4:38 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/31/2016 04:32 PM, Michael Moore wrote:
>
>         I'm still a bit confused. If I replace the ROLLBACK; command with
>         ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK;
>         produce
>         the same error since it is not valid in the LANGUAGE plpgsql.  I
>         understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not
>         the same
>         thing.
>
>
>     I am guessing this:
>
>     https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html
>     <https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html>
>     " A disadvantage is that errors in a specific expression or command
>     cannot be detected until that part of the function is reached in
>     execution. (Trivial syntax errors will be detected during the
>     initial parsing pass, but anything deeper will not be detected until
>     execution.)"
>
>     ROLLBACK might actually be valid at some point, ELEPHANT will not so
>     it caught in the trivial error stage.
>
>
>         On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore
>         <michaeljmoore@gmail.com <mailto:michaeljmoore@gmail.com>
>         <mailto:michaeljmoore@gmail.com
>         <mailto:michaeljmoore@gmail.com>>> wrote:
>
>             Cool, thanks David, I'll give it a read.
>
>
>             On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston
>             <david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>
>         <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>>> wrote:
>
>                 On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore
>                 <michaeljmoore@gmail.com
>         <mailto:michaeljmoore@gmail.com> <mailto:michaeljmoore@gmail.com
>         <mailto:michaeljmoore@gmail.com>>>wrote:
>
>                     Here is the complete function, but all you need to
>         look at
>                     is the exception block. (I didn't write this code)
>         :-)  I
>                     will ask the question after the code.
>                     ​[...]​
>
>                             RETURN TRUE;
>
>                     EXCEPTION WHEN OTHERS THEN
>
>                             RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
>
>                             ROLLBACK;
>
>                             RETURN FALSE;
>
>                     END;
>
>                     $BODY$
>
>                       LANGUAGE plpgsql VOLATILE
>
>                       COST 100;
>
>
>                     So, here is the question. Why does the compiler not
>         catch:
>
>                     1) ROLLBACK; is not a valid PL/pgSQL command
>
>
>                 R
>                 ​eading section ​41.10.2 at the linked page should
>         answer this part.
>
>
>         https://www.postgresql.org/docs/current/static/plpgsql-implementation.html
>         <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html>
>
>         <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html
>         <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html>>
>
>
>                     2) ROLLBACK; and RETURN FALSE; can never be reached
>
>
>
>                 Similar to the above - though "static analysis" is yet a
>         step
>                 beyond even what the syntax checking skipping covered above
>                 would reveal.
>
>                 ​David J.​
>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql