pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR : invalid transaction termination : PostgreSQL v12
5+ messages / 4 participants
[nested] [flat]
* ERROR : invalid transaction termination : PostgreSQL v12
@ 2020-11-23 17:05 Jagmohan Kaintura <jagmohan@tecorelabs.com>
2020-11-24 07:57 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jagmohan Kaintura @ 2020-11-23 17:05 UTC (permalink / raw)
To: pgsql-admin
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"; 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$;
*Best Regards,*
Jagmohan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR : invalid transaction termination : PostgreSQL v12
2020-11-23 17:05 ERROR : invalid transaction termination : PostgreSQL v12 Jagmohan Kaintura <jagmohan@tecorelabs.com>
@ 2020-11-24 07:57 ` Holger Jakobs <holger@jakobs.com>
2020-11-24 15:39 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Holger Jakobs @ 2020-11-24 07:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Jagmohan,
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 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$;
>
> *Best Regards,*
> Jagmohan
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR : invalid transaction termination : PostgreSQL v12
2020-11-23 17:05 ERROR : invalid transaction termination : PostgreSQL v12 Jagmohan Kaintura <jagmohan@tecorelabs.com>
2020-11-24 07:57 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Holger Jakobs <holger@jakobs.com>
@ 2020-11-24 15:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-24 21:15 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2020-11-24 15:39 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Nov 24, 2020 at 12:57 AM Holger Jakobs <holger@jakobs.com> wrote:
> 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.
>
Avoiding that behavior is the point of batching with periodic commits.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR : invalid transaction termination : PostgreSQL v12
2020-11-23 17:05 ERROR : invalid transaction termination : PostgreSQL v12 Jagmohan Kaintura <jagmohan@tecorelabs.com>
2020-11-24 07:57 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Holger Jakobs <holger@jakobs.com>
2020-11-24 15:39 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 David G. Johnston <david.g.johnston@gmail.com>
@ 2020-11-24 21:15 ` Holger Jakobs <holger@jakobs.com>
2020-11-24 21:21 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Holger Jakobs @ 2020-11-24 21:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
So what is the intended behaviour?
* If you run everything in autocommit, it will be very slow, but all
non-failing records will end up in the table alright.
* If you run a commit after a couple of records, you will lose all
records of this group if there is at least one failing record.
is that what you want?
Oracle's behaviour of saving all non-failing records while discarding
all failing records is not directly possible with PostgreSQL, because
the sense of a transaction is all-or-nothing.
Am 24.11.20 um 16:39 schrieb David G. Johnston:
> On Tue, Nov 24, 2020 at 12:57 AM Holger Jakobs <holger@jakobs.com
> <mailto:holger@jakobs.com>> wrote:
>
> 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.
>
> Avoiding that behavior is the point of batching with periodic commits.
>
> David J.
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ERROR : invalid transaction termination : PostgreSQL v12
2020-11-23 17:05 ERROR : invalid transaction termination : PostgreSQL v12 Jagmohan Kaintura <jagmohan@tecorelabs.com>
2020-11-24 07:57 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Holger Jakobs <holger@jakobs.com>
2020-11-24 15:39 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 David G. Johnston <david.g.johnston@gmail.com>
2020-11-24 21:15 ` Re: ERROR : invalid transaction termination : PostgreSQL v12 Holger Jakobs <holger@jakobs.com>
@ 2020-11-24 21:21 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2020-11-24 21:21 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
Holger Jakobs <holger@jakobs.com> writes:
> Oracle's behaviour of saving all non-failing records while discarding
> all failing records is not directly possible with PostgreSQL, because
> the sense of a transaction is all-or-nothing.
There are external tools that can emulate row-at-a-time loading,
such as pgloader [1]. I'm not sure what sort of speed penalty
you pay for that, but I imagine it's not trivial.
regards, tom lane
[1] https://pgloader.io
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-11-24 21:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-23 17:05 ERROR : invalid transaction termination : PostgreSQL v12 Jagmohan Kaintura <jagmohan@tecorelabs.com>
2020-11-24 07:57 ` Holger Jakobs <holger@jakobs.com>
2020-11-24 15:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-24 21:15 ` Holger Jakobs <holger@jakobs.com>
2020-11-24 21:21 ` Tom Lane <tgl@sss.pgh.pa.us>
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