Re: commit & rollback

[Arni <Arni.Kromic@xxxxxxxxxxx>]

You can't control transactions within a function. However you can do it within a procedure so the obvious solution would be to convert the main function to a procedure. You need PG 11 for that though, there were no stored procedures before it.

Pozdrav,
Arni

On 30/10/2019 14.45, Pepe TD Vo wrote:

Hello experts,

I have a main functions call the child functions.  If one of the child function break, I want the main function stop and tell me what the error.  From my test, if I break one from the main function and no other functions execute. That's fine, but if I break the child, the main function notice the error and still execute the rest of function, which I want it stop and tell me what caused the error as same as the one break from the main function.  How do I commit and rollback the function.

here is my main function script:

CREATE OR REPLACE FUNCTION ecisdrdm.pr_merge_staging_tables(

OUT v_ret text)

    RETURNS text

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE 

AS $BODY$

   

DECLARE

   v_errorcode      text;

   v_errormsg       varchar(512);

   v_module         varchar(32) = 'pr_merge_staging_tables';

   v_host varchar(32) = inet_server_addr();

begin

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Starting MERGE Process' );

begin

v_ret = '0';

if v_ret = '0' then

---

--- Perform the merge on APPLICATION_CDIM

---

     

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_APPLICATION_CDIM' );

PERFORM ecisdrdm.pr_mig_stg_application_cdim();

-- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION

v_ret = '0';

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_APPLICATION_CDIM' );

end if;

/*

exception

    when others then

GET STACKED DIAGNOSTICS v_ret = PG_EXCEPTION_CONTEXT;

RAISE INFO 'Error Name:%',SQLERRM;

        RAISE INFO 'Error State:%', SQLSTATE;

        RAISE INFO 'Error Context:%', v_errorcode;

*/

end;

----

---  2. STG_BNFT_CURR_FACT

----

   

if v_ret = '0' then

---

--- Perform the merge on BNFT_CURR_FACT

---

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_BNFT_CURR_FACT' );

PERFORM ecisdrdm.pr_mig_stg_bnft_curr_fact();

-- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION

v_ret = '0';

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_BNFT_CURR_FACT' );

end if;

----

--  3. STG_BNFT_FACT

----

   

if v_ret = '0' then

---

--- Perform the merge on BNFT_FACT

---

     

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_BNFT_FACT' );

PERFORM ecisdrdm.pr_mig_stg_bnft_fact();

-- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION

v_ret = '0';

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_BNFT_FACT' );

end if;

----

--  4. STG_BNFT_HIST_ACTN_LDIM

----

   

if v_ret = '0' then

---

--- Perform the merge on BNFT_HIST_ACTN_LDIM

---

     

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_BNFT_HIST_ACTN_LDIM ' );

PERFORM ecisdrdm.pr_mig_stg_bnft_hist_actn_ldim ();

-- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION

v_ret = '0';

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_BNFT_HIST_ACTN_LDIM ' );

    end if;

----

--  5. STG_CNTRY_ST_CDIM

----

   

if v_ret = '0' then

---

--- Perform the merge on CNTRY_ST_CDIM

---

     

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0','Started merging STG_CNTRY_ST_CDIM ' );

PERFORM ecisdrdm.pr_mig_stg_cntry_st_cdim();

-- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION

v_ret = '0';

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_CNTRY_ST_CDIM ');

end if;

----

--  6. STG_FRM_CDIM

----

   

if v_ret = '0' then

---

--- Perform the merge on FRM_CDIM

---

     

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Started merging STG_FRM_CDIM ' );

PERFORM ecisdrdm.pr_mig_stg_frm_cdim();

-- TODO: INVESTIGATE HOW TO RECEIVE THE OUT PARAMETER RETRUNED FROM FUNCTION

v_ret = '0';

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_FRM_CDIM ' );

end if;

---

--- This does the final commit or rollback for the entire merge process.

---

if v_ret = '0' then

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished MERGE PROCESS - COMMIT WORK' );

/* commit work; */

COMMIT;

else

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished MERGE PROCESS - ROLLBACK WORK ' );

/* rollback work; */

ROLLBACK;

RAISE NOTICE 'PROGRAM_ERROR';

end if;

----

--- Exception error handler

----

exception

   when others then

        v_errorcode := SQLSTATE;

        v_errormsg  := SQLERRM;

PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, v_errorcode, v_errormsg);

END;

$BODY$

SECURITY DEFINER;

if work fine without "rollback;" and when I put "rollback;" in, I get "invalid transaction termination without tell me which child function's error.  Now, I put it back the way it is w/o commit and rollback.  All I get is "Finished MERGE PROCESS - ROLL BACK".... 

the weird thing is every single change in the main function, all still stop working.

 I can't seem to find  anything else a good thing to do with commit and rollback, would you please tell me what cause the problem.  Now all stop only the last statement "merge process - roll back" stated.

thank you.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.

To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.

*** Genuinely rich *** Faithful talent *** Sharing success

As I can't seem to find the script, anything else a good thing to do after doing pg_upgrade to prime the DB?