Re: commit & rollback
От | Arni |
---|---|
Тема | Re: commit & rollback |
Дата | |
Msg-id | d59becfc-9095-1697-27db-760a4de99b9f@Bios-ICT.hr обсуждение исходный текст |
Ответ на | commit & rollback (Pepe TD Vo <pepevo@yahoo.com>) |
Список | pgsql-admin |
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 textLANGUAGE 'plpgsql'COST 100VOLATILEAS $BODY$DECLAREv_errorcode text;v_errormsg varchar(512);v_module varchar(32) = 'pr_merge_staging_tables';v_host varchar(32) = inet_server_addr();beginPERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Starting MERGE Process' );beginv_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 FUNCTIONv_ret = '0';PERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished merging STG_APPLICATION_CDIM' );end if;/*exceptionwhen others thenGET 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 FUNCTIONv_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 FUNCTIONv_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 FUNCTIONv_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 FUNCTIONv_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 FUNCTIONv_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' thenPERFORM ecisdrdm.pr_write_error_log( CURRENT_USER, v_host, v_module, '0', 'Finished MERGE PROCESS - COMMIT WORK' );/* commit work; */COMMIT;elsePERFORM 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----exceptionwhen others thenv_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 successAs I can't seem to find the script, anything else a good thing to do after doing pg_upgrade to prime the DB?
В списке pgsql-admin по дате отправления: