autocommit for multi call store procedure

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

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@evizi.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.postgres@gmail.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.le@evizi.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.postgres@gmail.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.le@evizi.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.postgres@gmail.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.le@evizi.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