ERROR : invalid transaction termination : PostgreSQL v12

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&gt;&quot; 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.klaver@aklaver.com

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.klaver@aklaver.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&gt;&quot; 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.klaver@aklaver.com

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

On Mon, Nov 23, 2020 at 10:03 AM Jagmohan Kaintura <jagmohan@tecorelabs.com>
wrote:

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.

Please don't top-post on the Postgres lists by the way (reply with all
previous conversation copied below).

The only way this would happen that I am aware of is if you called begin
before your batch function.

It doesn't works putting that block inside additional BEGIN END

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
BEGIN
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 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 20);

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;
raise info ' I came here %',v_batch_count;
END;
COMMIT;
END LOOP;
raise info ' I came here %',v_batch_count;
v_log_count := v_log_count + 1;
v_log_count);
END;
$BODY$;

while calling

INFO: I came here 20
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function test_transaction() line 48 at COMMIT

On Tue, Nov 24, 2020 at 12:17 AM Michael Lewis <mlewis@entrata.com> wrote:

On Mon, Nov 23, 2020 at 10:03 AM Jagmohan Kaintura <
jagmohan@tecorelabs.com> wrote:

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.

Please don't top-post on the Postgres lists by the way (reply with all
previous conversation copied below).

The only way this would happen that I am aware of is if you called begin
before your batch function.

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

On Mon, Nov 23, 2020 at 6:52 PM Jagmohan Kaintura <jagmohan@tecorelabs.com>
wrote:

It doesn't works putting that block inside additional BEGIN END

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
BEGIN

This isn't what I suggested. How are you connecting to the database to call
your TEST_TRANSACTION function? Some clients will issue BEGIN silently to
allow rollback. If you have a BEGIN that is called before the function
starts, then the function cannot call commit. The function must be called
while NOT in a transaction already.

Try removing security definer ... It should work.

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html