Thread: Reg: PL/pgSQL commit and rollback

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

On 03/17/2015 06:50 AM, Medhavi Mahansaria wrote:
> Hi,
>
> I am writing a porting a procedure running in oracle to a PL/pgSQL
> function.
>
> I need to use commit and rollback in my function.
>
> I have read that usage of commit and rollback is not possible in
> PL/pgSQL, however savepoints can be used.
>
> even when i use savepoints and rollback to a savepoint in the exception
> block I am getting the following error
>
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.
>
> In oracle:
>
> *CREATE OR REPLACE PROCEDURE abc (STATUS IN NUMBER) AS*
>
> *    CODE    NUMBER;*
> *    MSG     NVARCHAR2(200);*
>
> *BEGIN*
> *        DELETE FROM LOG;*
> *        DELETE FROM TRACKER;*
> *        BEGIN*
> *      IF (STATUS < 1)*
> *      THEN*
> *              <some query>*
>
> *              INSERT INTO TRACKER SELECT 1,SYSDATE FROM DUAL;*
> *              COMMIT;*

The above is your problem, there cannot be a COMMIT in the function.

See here for more detail;

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

> *      END IF;*
> *      EXCEPTION*
> *      WHEN OTHERS THEN*
> *        CODE:=SQLCODE;*
> *        MSG:= SQLERRM;*
> *      ROLLBACK;*
> *              INSERT INTO LOG('CODE AND MESSAGES ARE ' || CODE || ' '
> || MSG);*
> *        COMMIT;*
> *      RAISE_APPLICATION_ERROR(-20001, 'EXCEPTION WHEN EXT SYSTEM ID
> KEY');*
> *    END;*
> *        BEGIN*
> *      IF (STATUS < 5)*
> *      THEN*
> *              <some query>*
> **
> *              INSERT INTO TRACKER SELECT 5,SYSDATE FROM DUAL;*
> *              COMMIT;*
> *      END IF;*
> *      EXCEPTION*
> *      WHEN OTHERS THEN*
> *      CODE:=SQLCODE;*
> *        MSG:= SQLERRM;*
> *        ROLLBACK;*
> *              INSERT INTO LOG('CODE AND MESSAGES ARE ' || CODE || ' '
> || MSG);*
> *        COMMIT;*
> *      RAISE_APPLICATION_ERROR(-20001, 'EXCEPTION WHEN EXT SYSTEM ID
> KEY');*
> *    END;*
> *
> ............... and so on (I have blocks toll STATUS < 200 and all
> follow the same concept)*
> *END;*
> */*
>
>
> How can i acheive the same output/flow in PL/pgSQL?
>
> Can you please share a converted code snippet for my reference.
>
>
> Thanks & Regards
> Medhavi Mahansaria
> Cell:- +91 9620053040
>
> =====-----=====-----=====
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Medhavi Mahansaria wrote:
> I am writing a porting a procedure running in oracle to a PL/pgSQL function.
> 
> I need to use commit and rollback in my function.
> 
> I have read that usage of commit and rollback is not possible in PL/pgSQL, however savepoints can be
> used.
> 
> even when i use savepoints and rollback to a savepoint in the exception block I am getting the
> following error
> 
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.

Instead of explicitly using ROLLBACK, you have to code like this:

BEGIN
   INSERT ... -- may cause an error
EXCEPTION
   WHEN OTHERS
   THEN ...
END

If you get into the exception block, PL/pgSQL will implicitly
have rolled back everything that happened between BEGIN and EXCEPTION.

Yours,
Laurenz Albe

On 17 March 2015 at 15:30, Medhavi Mahansaria
<medhavi.mahansaria@tcs.com> wrote:
> Yes. I have read this document.
>
> But my issue is that even when it throws and exception I need to rollback
> the changes made by that query and move on to the next block.
>
> Is there any way to accomplish that?

Please do not toppost on this list.

The solution is to do what Albe explained: use BEGIN ... EXCEPTION ...

On 03/17/2015 07:30 AM, Medhavi Mahansaria wrote:
> Yes. I have read this document.
>
> But my issue is that even when it throws and exception I need to
> rollback the changes made by that query and move on to the next block.
>
> Is there any way to accomplish that?

"If no error occurs, this form of block simply executes all the
statements, and then control passes to the next statement after END. But
if an error occurs within the statements, further processing of the
statements is abandoned, and control passes to the EXCEPTION list. The
list is searched for the first condition matching the error that
occurred. If a match is found, the corresponding handler_statements are
executed, and then control passes to the next statement after END. If no
match is found, the error propagates out as though the EXCEPTION clause
were not there at all: the error can be caught by an enclosing block
with EXCEPTION, or if there is none it aborts processing of the function.

I have not looked at your original function in depth, but I am pretty
sure all you have to do is remove the COMMIT and ROLLBACK lines to get
what you want. There are also the RAISE_APPLICATION_ERROR lines to deal
with. Take a look at:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-errors-and-messages.html

Also might want to look at:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-porting.html
--
Adrian Klaver
adrian.klaver@aklaver.com

> From: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>
>To: Adrian Klaver <adrian.klaver@aklaver.com>
>Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
>Sent: Tuesday, 17 March 2015, 14:30
>Subject: Re: [GENERAL] Reg: PL/pgSQL commit and rollback
>
>
>
>Yes. I have read this document.
>
>But my issue is that even when it throws
and exception I need to rollback the changes made by that query and move
on to the next block.
>
>Is there any way to accomplish that?
>


Yes, as per the docs in the link:

"When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when
theerror occurred, but all changes to persistent database state within the block are rolled back." 

So you do something like:

BEGIN
  UPDATE .....
EXCEPTION WHEN <whatever> THEN
    .. <whatever>

END;