Re: ERROR : invalid transaction termination : PostgreSQL v12

Поиск
Список
Период
Сортировка
От Jagmohan Kaintura
Тема Re: ERROR : invalid transaction termination : PostgreSQL v12
Дата
Msg-id CA+cYFtuDuPPYuE8TCYdv3YwuH0AYMRjX5aCmyFCK-4UYgB75Bw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: ERROR : invalid transaction termination : PostgreSQL v12  (Michael Lewis <mlewis@entrata.com>)
Ответы Re: ERROR : invalid transaction termination : PostgreSQL v12  (Michael Lewis <mlewis@entrata.com>)
Список pgsql-general
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. 

В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Lewis
Дата:
Сообщение: Re: ERROR : invalid transaction termination : PostgreSQL v12
Следующее
От: Michael Lewis
Дата:
Сообщение: Re: ERROR : invalid transaction termination : PostgreSQL v12