ERROR : invalid transaction termination : PostgreSQL v12 - Mailing list pgsql-general
From | Jagmohan Kaintura |
---|---|
Subject | ERROR : invalid transaction termination : PostgreSQL v12 |
Date | |
Msg-id | CA+cYFtuQkq_PR+EXocnEL-nxDXd_uz5Wf7Eb2SLrQN6gjA-ipA@mail.gmail.com Whole thread Raw |
Responses |
Re: ERROR : invalid transaction termination : PostgreSQL v12
Re: ERROR : invalid transaction termination : PostgreSQL v12 |
List | pgsql-general |
Hi Team,
We have many BATCH JOBS in Oracle which we are committing after processing few Records. These batch Jobs process in some subsets and call transaction control statements COMMIT in case of Success and ROLLBACK in case of failure.
While converting to POstgreSQL we converted in Same Format with COMMIT and ROLLBACK. But while executing it ended up with below error message.
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function inline_code_block line 29 at COMMIT
CONTEXT: PL/pgSQL function inline_code_block line 29 at COMMIT
While reviewing the Transaction Management in PostgreSQL "https://www.postgresql.org/docs/12/plpgsql-transactions.html" it speaks about a format which is not Supported.
Transaction control is only possible in
CALL
or DO
invocations from the top level or nested CALL
or DO
invocations without any other intervening command. For example, if the call stack is CALL proc1()
→ CALL proc2()
→ CALL proc3()
, then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1()
→ SELECT func2()
→ CALL proc3()
, then the last procedure cannot do transaction control, because of the SELECT
in between. My Call has : CALL Batch Job => SELECT function Used in SQL Statements ==> Call Procedure. We have transaction control in "CALL Batch Job" only.
Pseudo Code is like : Highlighted in BOLD is a function call. It's failing when getting executed as we are using functions into this procedure.
Can any help on this matter , how I can implement Batch Jobs as we wanted to commit in few intervals of 20000 records ?
We can't remove this function from the statement as its value is dependent on column value.
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION(
)
LANGUAGE 'plpgsql'
SECURITY DEFINER
AS $BODY$
DECLARE
G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) := 'SYSTEM';
G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) := 'BATCH';
G_CNTR_LOADING_EXPIRED TMS_CONTAINER_LOADING.STATUS_CODE%TYPE := '90';
G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '80';
G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '95';
v_num_day numeric;
v_batch_count numeric;
v_log_count numeric := 0;
v_local_batch_count numeric;
BEGIN
v_batch_count := 0;
LOOP
update tms_container_loading
set status_code = G_CNTR_LOADING_EXPIRED
, last_update_tm = clock_timestamp()::timestamp(0)
, last_update_user_an = G_LAST_UPDATE_USER_SYSTEM
, last_update_module_code = G_LAST_UPDATE_MODULE_BATCH
where tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0))) = 1
and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED and ctid in (select a.ctid from tms_container_loading where tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0))) = 1
and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED LIMIT 20000);
EXIT WHEN NOT FOUND; /* apply on SQL */
GET DIAGNOSTICS v_local_batch_count = ROW_COUNT; v_batch_count := v_batch_count + v_local_batch_count;
COMMIT;
END LOOP;
v_log_count := v_log_count + 1; CALL Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB', v_log_count);
COMMIT;
END;
$BODY$;
)
LANGUAGE 'plpgsql'
SECURITY DEFINER
AS $BODY$
DECLARE
G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) := 'SYSTEM';
G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) := 'BATCH';
G_CNTR_LOADING_EXPIRED TMS_CONTAINER_LOADING.STATUS_CODE%TYPE := '90';
G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '80';
G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '95';
v_num_day numeric;
v_batch_count numeric;
v_log_count numeric := 0;
v_local_batch_count numeric;
BEGIN
v_batch_count := 0;
LOOP
update tms_container_loading
set status_code = G_CNTR_LOADING_EXPIRED
, last_update_tm = clock_timestamp()::timestamp(0)
, last_update_user_an = G_LAST_UPDATE_USER_SYSTEM
, last_update_module_code = G_LAST_UPDATE_MODULE_BATCH
where tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0))) = 1
and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED and ctid in (select a.ctid from tms_container_loading where tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0))) = 1
and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED LIMIT 20000);
EXIT WHEN NOT FOUND; /* apply on SQL */
GET DIAGNOSTICS v_local_batch_count = ROW_COUNT; v_batch_count := v_batch_count + v_local_batch_count;
COMMIT;
END LOOP;
v_log_count := v_log_count + 1; CALL Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB', v_log_count);
COMMIT;
END;
$BODY$;
Best Regards,
Jagmohan
Senior Consultant, TecoreLabs.
pgsql-general by date: