Fwd: autocommit for multi call store procedure

Trang Le <trang.le@xxxxxxxxx> · Fri, 17 Sep 2021 08:32:55 +0700

Hi Adrian,
I am processing this issue with Ninad.

Could you double check it?

Regards,
Trang

---------- Forwarded message ---------
From: Trang Le <trang.le@xxxxxxxxx>
Date: Fri, Sep 17, 2021 at 8:19 AM
Subject: Re: autocommit for multi call store procedure
To: Ninad Shah <nshah.postgres@xxxxxxxxx>

Hi Ninad,
Thanks for your sharing.

It's weird I can run in pgadmin4.

call PRC_ADDRESS_DS_TEST(); call PRC_ADDRESS_DS_TEST1();

Could you check it in pgadmin4?

Regard,
Trang

On Fri, Sep 17, 2021 at 12:20 AM Ninad Shah <nshah.postgres@xxxxxxxxx> 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.le@xxxxxxxxx> wrote:
Hi Ninah,
Could you please run those queries at the same time?

call transaction_test1();
call transaction_test2();

I run in new windows

Regards,
Trang

On Thu, Sep 16, 2021 at 4:53 PM Ninad Shah <nshah.postgres@xxxxxxxxx> 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.le@xxxxxxxxx> 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.postgres@xxxxxxxxx> 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.le@xxxxxxxxx> 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.postgres@xxxxxxxxx> wrote:
Have you used an EXCEPTION block in the procedure?

Regards,
Ninad Shah

On Thu, 16 Sept 2021 at 13:06, Trang Le <trang.le@xxxxxxxxx> 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