Re: ERROR : invalid transaction termination : PostgreSQL v12

Hi ,
The Block is only failing immediately at First COMMIT only. It's not
supporting COMMIT. I have removed some portion of code before the second
COMMIT.

On Mon, Nov 23, 2020 at 9:19 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 11/23/20 12:36 AM, Jagmohan Kaintura wrote:
> > 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
> >
> > While reviewing the Transaction Management in PostgreSQL
> > "https://www.postgresql.org/docs/12/plpgsql-transactions.html
> > <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$;
>
> I'm still trying to figure out transaction management in procedures, so
> bear with me. Not sure what the purpose of the second COMMIT is? Also
> wonder if it is no the cause of the issue?
>
>
> > --
> > *Best Regards,*
> > Jagmohan
> > Senior Consultant, TecoreLabs.
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.