Fwd: autocommit for multi call store procedure

Hi Adrian,

I am processing this issue with Ninad.

Could you double check it?

Regards,
Trang

---------- Forwarded message ---------
From: Trang Le <trang(dot)le(at)evizi(dot)com>
Date: Fri, Sep 17, 2021 at 8:19 AM
Subject: Re: autocommit for multi call store procedure
To: Ninad Shah <nshah(dot)postgres(at)gmail(dot)com>

Hi Ninad,

Thanks for your sharing.

It's weird I can run in pgadmin4.

call PRC_ADDRESS_DS_TEST(); call PRC_ADDRESS_DS_TEST1();

[image: image.png]

Could you check it in pgadmin4?

Regard,
Trang

On Fri, Sep 17, 2021 at 12:20 AM Ninad Shah <nshah(dot)postgres(at)gmail(dot)com>
wrote:

> Hi Trang,
>
> The way you are executing It invokes functions sequentially. test1 will be
> executed first then test2.
>
> And yes, it works for me.
>
> postgres=# call transaction_test1(); call transaction_test2();
> CALL
> CALL
>
> In your case, you are facing the issue with the first call statement.
> Kindly check your version.
>
>
> Regards,
> Ninad Shah
>
> On Thu, 16 Sept 2021 at 15:29, Trang Le <trang(dot)le(at)evizi(dot)com> wrote:
>
>> Hi Ninah,
>>
>> Could you please run those queries at the same time?
>>
>> call transaction_test1();
>> call transaction_test2();
>>
>> I run in new windows
>>
>> [image: image.png]
>>
>> Regards,
>> Trang
>>
>> On Thu, Sep 16, 2021 at 4:53 PM Ninad Shah <nshah(dot)postgres(at)gmail(dot)com>
>> wrote:
>>
>>> Hi Trang,
>>>
>>> I tried it on version 13.3, and both the cases work as expected. Not
>>> only that but also by applying different variations in the procedure, it
>>> delivers desired results.
>>>
>>> *Case 1:*
>>>
>>> Do not use the call statement inside a procedure, rather just put simple
>>> INSERT statements followed by COMMIT, and it works as expected.
>>>
>>> postgres=# CREATE or replace PROCEDURE transaction_test3()
>>> postgres-# LANGUAGE plpgsql
>>> postgres-# AS $$
>>> postgres$# DECLARE
>>> postgres$# r RECORD;
>>> postgres$# BEGIN
>>> postgres$# INSERT INTO test_table VALUES (1, 'test1');
>>> postgres$# INSERT INTO test_table VALUES (2, 'test2');
>>> postgres$# COMMIT;
>>> postgres$# END;
>>> postgres$# $$;
>>> CREATE PROCEDURE
>>> postgres=#
>>> postgres=#
>>> postgres=# call transaction_test3();
>>> CALL
>>>
>>> *Case 2:*
>>>
>>> Use 2 call statements inside a nested BEGIN block followed by a COMMIT
>>> statement. This also works as expected.
>>>
>>> postgres=# CREATE or replace PROCEDURE transaction_test4()
>>> postgres-# LANGUAGE plpgsql
>>> postgres-# AS $$
>>> postgres$# DECLARE
>>> postgres$# r RECORD;
>>> postgres$# BEGIN
>>> postgres$# BEGIN
>>> postgres$# call transaction_test1();
>>> postgres$# call transaction_test2();
>>> postgres$# COMMIT;
>>> postgres$# END;
>>> postgres$# END;
>>> postgres$# $$;
>>> CREATE PROCEDURE
>>> postgres=#
>>> postgres=#
>>> postgres=# call transaction_test4();
>>> CALL
>>>
>>> Additionally, in all the cases, data is getting inserted into a test
>>> table.
>>>
>>> Kindly verify what version you are using?
>>>
>>>
>>> Regards,
>>> Ninad Shah
>>>
>>>
>>>
>>>
>>> On Thu, 16 Sept 2021 at 14:59, Trang Le <trang(dot)le(at)evizi(dot)com> wrote:
>>>
>>>> Hi Ninah,
>>>>
>>>> I already double checked with this case, it has the same problem.
>>>>
>>>> Could you please double check the attached file?
>>>>
>>>> Regards,
>>>> Trang
>>>>
>>>> On Thu, Sep 16, 2021 at 4:20 PM Ninad Shah <nshah(dot)postgres(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> I saw some examples where COMMIT statements are part of FOR loops.
>>>>>
>>>>> Would you try using a loop?
>>>>>
>>>>>
>>>>> Regards,
>>>>> Ninad Shah
>>>>>
>>>>>
>>>>> On Thu, 16 Sept 2021 at 13:12, Trang Le <trang(dot)le(at)evizi(dot)com> wrote:
>>>>>
>>>>>> No, I leaved exception to sub-block. So it does not effect. I
>>>>>> committed in end of outer block and inside exception
>>>>>>
>>>>>> This is my query
>>>>>>
>>>>>> CREATE OR REPLACE PROCEDURE mdm.prc_address_ds_test(
>>>>>> )
>>>>>> LANGUAGE 'plpgsql'
>>>>>> AS $BODY$
>>>>>> BEGIN
>>>>>> CALL mdm.prc_log_job(v_job_id, v_cnt, v_job_desc, v_trx_type, 0,
>>>>>> v_start_ts);
>>>>>> BEGIN
>>>>>> v_cnt := v_cnt + 1;
>>>>>> v_start_ts := clock_timestamp();
>>>>>> v_job_desc := 'Insert records in address_ds test';
>>>>>> v_trx_type := 'I';
>>>>>> GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
>>>>>> CALL mdm.prc_log_job(v_job_id, v_cnt, v_job_desc, v_trx_type,
>>>>>> sql$rowcount, v_start_ts);
>>>>>> EXCEPTION
>>>>>> WHEN others THEN
>>>>>> v_cnt := v_cnt + 1;
>>>>>> GET DIAGNOSTICS sql$rowcount = ROW_COUNT;
>>>>>> GET STACKED DIAGNOSTICS aws$frmt_err_bcktrc = PG_EXCEPTION_CONTEXT;
>>>>>> begin
>>>>>> CALL mdm.prc_log_job(v_job_id, v_cnt, v_job_desc, v_trx_type,
>>>>>> sql$rowcount::bigint, v_start_ts, substring(aws$frmt_err_bcktrc, 0, 4000));
>>>>>> commit;
>>>>>> end;
>>>>>> RAISE USING hint = -20101, message = aws$frmt_err_bcktrc, detail =
>>>>>> 'User-defined exception';
>>>>>> END;
>>>>>> CALL mdm.prc_log_job(job_id => v_job_id, job_id_seq_num => v_cnt,
>>>>>> job_trx_type => NULL, job_trx_cnt => NULL::bigint,
>>>>>> job_desc => CONCAT('END JOB: ', v_job_nme)::character varying,
>>>>>> job_start_ts => v_start_ts);
>>>>>> commit;
>>>>>> END;
>>>>>> $BODY$;
>>>>>>
>>>>>> On Thu, Sep 16, 2021 at 2:38 PM Ninad Shah <nshah(dot)postgres(at)gmail(dot)com>
>>>>>> wrote:
>>>>>>
>>>>>>> Have you used an EXCEPTION block in the procedure?
>>>>>>>
>>>>>>>
>>>>>>> Regards,
>>>>>>> Ninad Shah
>>>>>>>
>>>>>>> On Thu, 16 Sept 2021 at 13:06, Trang Le <trang(dot)le(at)evizi(dot)com> wrote:
>>>>>>>
>>>>>>>> Hi guys,
>>>>>>>>
>>>>>>>> I am using pgadmin4 to interact with Postgres database. For now I
>>>>>>>> would like to run 2 store procedure (those have commit statement in begin
>>>>>>>> end block). I enable autocommit and run call 2 store procedures at the same
>>>>>>>> time. However, there is an error with invalid transaction termination.
>>>>>>>>
>>>>>>>> Could you help me on this issue?
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Trang
>>>>>>>>
>>>>>>>