Re: ERROR : invalid transaction termination : PostgreSQL v12

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

>