Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1khTCf-0004GA-WE for pgsql-admin@arkaria.postgresql.org; Tue, 24 Nov 2020 07:57:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1khTCe-00067B-8W for pgsql-admin@arkaria.postgresql.org; Tue, 24 Nov 2020 07:57:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1khTCd-000673-UV for pgsql-admin@lists.postgresql.org; Tue, 24 Nov 2020 07:57:32 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1khTCW-0000Nj-Pr for pgsql-admin@lists.postgresql.org; Tue, 24 Nov 2020 07:57:31 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 707F7380491; Tue, 24 Nov 2020 08:57:23 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id Zh8_GmUYICtq; Tue, 24 Nov 2020 08:57:23 +0100 (CET) Received: from [192.168.178.86] (p5b249d69.dip0.t-ipconnect.de [91.36.157.105]) by rs.plausibolo.de (Postfix) with ESMTPSA id 0F66A3801A4; Tue, 24 Nov 2020 08:57:23 +0100 (CET) Subject: Re: ERROR : invalid transaction termination : PostgreSQL v12 To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Message-ID: <7685663c-4fb4-2875-9164-a44007ca9568@jakobs.com> Date: Tue, 24 Nov 2020 08:57:22 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="KQ4GSssHhpg8DSZMkKx8UEhsweBBu8n1Q" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --KQ4GSssHhpg8DSZMkKx8UEhsweBBu8n1Q Content-Type: multipart/mixed; boundary="lRDCUXPLy0l9uyRCXL1hOJCY4wx2vmGw9"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <7685663c-4fb4-2875-9164-a44007ca9568@jakobs.com> Subject: Re: ERROR : invalid transaction termination : PostgreSQL v12 References: In-Reply-To: --lRDCUXPLy0l9uyRCXL1hOJCY4wx2vmGw9 Content-Type: multipart/alternative; boundary="------------EC97A5F712A35ED008E8D2D8" Content-Language: de-DE This is a multi-part message in MIME format. --------------EC97A5F712A35ED008E8D2D8 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Hi Jagmohan, What is the point of COMMITting after a few records? Why not let the=20 whole batch run through. If there are any errors, PostgreSQL will=20 rollback the whole transaction anyway. As opposed to Oracle, PostgreSQL won't commit some records, while others = failed within the same transaction. As soon as a single operation within = a transaction fails, it's dead and all subsequent operations will fail. Regards, Holger Am 23.11.20 um 18:05 schrieb Jagmohan Kaintura: > Hi Team, > > We have many BATCH JOBS in Oracle which we are committing=C2=A0after=20 > processing few Records. These batch Jobs process in some subsets and=20 > call transaction control statements COMMIT in case of Success and=20 > 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: =C2=A0invalid transaction termination > CONTEXT: =C2=A0PL/pgSQL function inline_code_block line 29 at COMMIT > > While reviewing the Transaction Management in PostgreSQL=C2=A0=20 > "https://www.postgresql.org/docs/12/plpgsql-transactions.html=20 > " it=20 > speaks=C2=A0about a format which is not Supported. > > Transaction control is only possible in |CALL|=C2=A0or |DO|=C2=A0invoca= tions=20 > from the top level or nested |CALL|=C2=A0or |DO|=C2=A0invocations witho= ut any=20 > other intervening command. For example, if the call stack is |CALL=20 > proc1()|=C2=A0=E2=86=92 |CALL proc2()|=C2=A0=E2=86=92 |CALL proc3()|, t= hen the second and third=20 > procedures can perform transaction control actions. But if the call=20 > stack is |CALL proc1()|=C2=A0=E2=86=92 |SELECT func2()|=C2=A0=E2=86=92 = |CALL proc3()|, then the=20 > last procedure cannot do transaction control, because of the=20 > |SELECT|=C2=A0in between. > > My Call has :=C2=A0 CALL Batch Job =3D> SELECT function Used in SQL=20 > Statements=C2=A0 =3D=3D> Call Procedure.=C2=A0 We have transaction cont= rol in "CALL=20 > Batch Job" only. > > Pseudo Code is like : Highlighted in BOLD is a function call. It's=20 > failing when getting executed as we are using functions into this=20 > procedure. > *Can any help on this matter , how I can implement Batch Jobs as we=20 > wanted to commit in few intervals of 20000 records ?* > We can't remove this function from the statement as its value is=20 > dependent on column value. > > CREATE OR REPLACE PROCEDURE TEST_TRANSACTION( > ) > LANGUAGE 'plpgsql' > =C2=A0 =C2=A0 SECURITY DEFINER > AS $BODY$ > DECLARE > =C2=A0 =C2=A0 G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) :=3D 'SYSTEM'; > =C2=A0 =C2=A0 G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) :=3D 'BATCH'; > > =C2=A0 =C2=A0 G_CNTR_LOADING_EXPIRED TMS_CONTAINER_LOADING.STATUS_CODE%= TYPE :=3D '90'; > =C2=A0 =C2=A0 G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_CODE%TYPE :=3D= '80'; > =C2=A0 =C2=A0 G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE = :=3D '95'; > > =C2=A0 =C2=A0 v_num_day numeric; > =C2=A0 =C2=A0 v_batch_count numeric; > =C2=A0 =C2=A0 v_log_count numeric :=3D 0; > =C2=A0 =C2=A0 v_local_batch_count numeric; > BEGIN > =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_batch_count :=3D 0; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 LOOP > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 update tms_container_loading > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0set status_code = =3D G_CNTR_LOADING_EXPIRED > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0, last_up= date_tm =3D clock_timestamp()::timestamp(0) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0, last_up= date_user_an =3D G_LAST_UPDATE_USER_SYSTEM > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0, last_up= date_module_code =3D G_LAST_UPDATE_MODULE_BATCH > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where=20 > *tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::tim= estamp(0)))*=C2=A0=3D=20 > 1 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0and coalesce(sta= tus_code,'~') <> G_CNTR_LOADING_EXPIRED=20 > =C2=A0and ctid in (select a.ctid from tms_container_loading =C2=A0where= =20 > *tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::tim= estamp(0)))*=C2=A0=3D=20 > 1 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0and coalesce(sta= tus_code,'~') <> G_CNTR_LOADING_EXPIRED=20 > LIMIT 20000); > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 EXIT WHEN NOT FOUND; /* apply= on SQL */ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GET DIAGNOSTICS v_local_batch= _count =3D ROW_COUNT;=20 > v_batch_count :=3D v_batch_count + v_local_batch_count; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COMMIT; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 END LOOP; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_log_count :=3D v_log_count + 1; CALL=20 > Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB',=20 > v_log_count); > =C2=A0 =C2=A0 =C2=A0 =C2=A0 COMMIT; > END; > $BODY$; > > *Best Regards,* > Jagmohan --=20 Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------EC97A5F712A35ED008E8D2D8 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Hi Jagmohan,<= /p>

What is the point of= COMMITting after a few records? Why not let the whole batch run through. If there are any errors, PostgreSQL will rollback the whole transaction anyway.

As opposed to Oracle= , PostgreSQL won't commit some records, while others failed within the same transaction. As soon as a single operation within a transaction fails, it's dead and all subsequent operations will fail.

Regards,

Holger

Am 23.11.20 um 18:05 schrieb Jagmohan Kaintura:
Hi Team,

We have many BATCH JOBS in Oracle which we are committing=C2=A0after processing few Records. Th= ese batch Jobs process in some subsets and call transaction control statements COMMIT in case of Success and ROLLBACK in case of failure.=C2=A0

While converting to POstgreSQL we converted in Same Format with COMMIT and ROLLBACK. But while executing it ended up with below error message.
ERROR: =C2=A0invalid transaction termination
CONTEXT: =C2=A0PL/pgSQL function inline_code_block line 29 at= COMMIT

While reviewing the Transaction Management in PostgreSQL=C2=A0 "https://www.po= stgresql.org/docs/12/plpgsql-transactions.html" it speaks=C2=A0about a format which is not Supported.=C2=A0=

Transaction control is only possible in=C2=A0CALL=C2=A0or=C2=A0DO=C2=A0invocations= from the top level or nested=C2=A0CALL=C2=A0or=C2=A0DO=C2=A0invocations= without any other intervening command. For example, if the call stack is=C2=A0CALL proc1()=C2=A0=E2=86=92=C2= =A0CALL proc2()=C2=A0=E2=86=92=C2= =A0CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is=C2=A0CALL proc1()=C2=A0=E2=86=92=C2= =A0SELECT func2()=C2=A0=E2=86=92=C2= =A0CALL proc3(), then the last procedure cannot do transaction control, because of the=C2= =A0SELECT=C2=A0in between.= =C2=A0

My Call has :=C2=A0 CALL Batch J= ob =3D> SELECT function Used in SQL Statements=C2=A0 =3D=3D= > Call Procedure.=C2=A0 We have transaction control in "CALL Batch= Job" only.=C2=A0

Pseudo Code is like : Highlighte= d 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'
=C2=A0 =C2=A0 SECURITY DEFINER
AS $BODY$
DECLARE
=C2=A0 =C2=A0 G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) :=3D 'S= YSTEM';
=C2=A0 =C2=A0 G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) :=3D '= BATCH';

=C2=A0 =C2=A0 G_CNTR_LOADING_EXPIRED TMS_CONTAINER_LOADING.STATUS_CODE%TYPE :=3D '90';
=C2=A0 =C2=A0 G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_= CODE%TYPE :=3D '80';
=C2=A0 =C2=A0 G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE :=3D '95';

=C2=A0 =C2=A0 v_num_day numeric;
=C2=A0 =C2=A0 v_batch_count numeric;
=C2=A0 =C2=A0 v_log_count numeric :=3D 0;
=C2=A0 =C2=A0 v_local_batch_count numeric;
BEGIN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_batch_count :=3D 0;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 LOOP
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 update tms_contai= ner_loading
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0set = status_code =3D G_CNTR_LOADING_EXPIRED
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0, last_update_tm =3D clock_timestamp()::timestamp(0)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0, last_update_user_an =3D G_LAST_UPDATE_USER_SYSTEM
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0, last_update_module_code =3D G_LAST_UPDATE_MODULE_BATCH
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where=C2=A0= tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::tim= estamp(0)))=C2=A0=3D 1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0and = coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED =C2=A0and ctid in (select a.ctid fro= m tms_container_loading =C2=A0where=C2=A0tms$vvd.is_vessel= _departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))=C2=A0= =3D 1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0and = coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED LIMIT 20000);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 EXIT WHEN NOT FOU= ND; /* apply on SQL */
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GET DIAGNOSTICS v= _local_batch_count =3D ROW_COUNT; v_batch_count :=3D v_batch_count + v_local_batch_count;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COMMIT;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END LOOP;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_log_count :=3D v_log_count + = 1; CALL Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB', v_log_count);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 COMMIT;
END;
$BODY$;

= Best Regards,
Jagmohan 
--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------EC97A5F712A35ED008E8D2D8-- --lRDCUXPLy0l9uyRCXL1hOJCY4wx2vmGw9-- --KQ4GSssHhpg8DSZMkKx8UEhsweBBu8n1Q Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCX7y84gUDAAAAAAAKCRCKV5yTsxlhuo8p AJ970a88CiTsuDd0uyL24u7JHZJ8mwCeIa95EqujkoHFWkqeUPv/kW0GR8Q= =TiW6 -----END PGP SIGNATURE----- --KQ4GSssHhpg8DSZMkKx8UEhsweBBu8n1Q--