Re: ERROR : invalid transaction termination : PostgreSQL v12

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(at)entrata(dot)com> wrote:

> On Mon, Nov 23, 2020 at 10:03 AM Jagmohan Kaintura <
> jagmohan(at)tecorelabs(dot)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.