pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcommit & rollback
2+ messages / 2 participants
[nested] [flat]
* commit & rollback
@ 2019-10-30 13:45 Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Pepe TD Vo @ 2019-10-30 13:45 UTC (permalink / raw)
To: 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.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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: commit & rollback
@ 2019-11-12 08:11 Arni <Arni.Kromic@Bios-ICT.hr>
parent: Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Arni @ 2019-11-12 08:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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_hostvarchar(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?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-11-12 08:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-30 13:45 commit & rollback Pepe TD Vo <pepevo@yahoo.com>
2019-11-12 08:11 ` Arni <Arni.Kromic@Bios-ICT.hr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox