commit & rollback
| От | Pepe TD Vo |
|---|---|
| Тема | commit & rollback |
| Дата | |
| Msg-id | 1282101845.2072668.1572443106316@mail.yahoo.com обсуждение исходный текст |
| Ответы |
Re: commit & rollback
(Arni <Arni.Kromic@Bios-ICT.hr>)
|
| Список | pgsql-admin |
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.
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?
В списке pgsql-admin по дате отправления: