Postgres: Let us check what happens to the lock when is placed outside the TX proc module...

 Create Proc statement:

CREATE or replace PROCEDURE transaction_test1()

LANGUAGE plpgsql

AS $$

declare

o int;

j int;

BEGIN

select max(id) from pgtst_schema.pgtst_vacuum into o;

    FOR i IN 0..9 LOOP

    j:=i+o+1;

    INSERT INTO pgtst_schema.pgtst_vacuum

    values (j, lpad('TST',mod(j,100),'CHK'));

    COMMIT;

    PERFORM pg_sleep(10);

    END LOOP;

END;

$$;

screen 1:

begin work;

LOCK table pgtst_schema.pgtst_vacuum in exclusive mode;

call transaction_test1();

commit work;

screen 2:

Data collection:

-bash-4.2$ psql -d pgtst_db -f othertab_query_table_data.sql -x -a -q >30jan2023_proc2_othertable_assesment.out

-bash-4.2$ psql -d pgtst_db -f query_table_data.sql -x -a -q >30jan2023_proc2_table_assesment.out

screen 3:

begin work;

LOCK table pgtst_schema.pgtst_vacuum in exclusive mode;

commit work;

Observation:

Screen 1 command with the call routine failed!

pgtst_db=# begin work;

BEGIN

Time: 0.105 ms

pgtst_db=*# LOCK table pgtst_schema.pgtst_vacuum in exclusive mode;

LOCK TABLE

Time: 0.133 ms

pgtst_db=*# call transaction_test1();

ERROR:  invalid transaction termination

CONTEXT:  PL/pgSQL function transaction_test1() line 11 at COMMIT

Time: 4.015 ms

pgtst_db=!# end work;

ROLLBACK

Time: 2.009 ms

ERROR:  invalid transaction termination <<<<<<< is because we have initiated a commit within a call routine when inside a begin statement.

Stack overflow: https://stackoverflow.com/questions/59159091/invalid-transaction-termination

As per the above url, this isnt expected to work, a limitation of postgresql.

The same call without begin just works fine...

pgtst_db=# call transaction_test1();

CALL

Time: 100115.783 ms (01:40.116)

pgtst_db=#