Fwd: autocommit for multi call store procedure - Mailing list pgsql-general

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

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