Postgres - not releasing table lock in function-CodePudding

My postgres version is 9.6 and I am accessing a table in one function named myfunction();.

DECLARE 
  test_cur record;
    cur cursor for select pid::text, state, usename, query, query_start::text 
        from pg_stat_activity 
        where pid in (select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 'mytablename');                
BEGIN    
open cur;
loop
    fetch cur into test_cur;
    exit when not found;
    raise info 'lock details- %', test_cur.pid::text||','||test_cur.state||','||test_cur.usename||','||test_cur.query||','||test_cur.query_start::text;
end loop;
close cur;

//it prints-  lock details - <NULL>

create table temp_1 as 
select v.* from(
    select row_number() over (partition by p.name) as row_num, p.name,p.age 
    from mytablename p
    ) v where v.row_num=1 ;

open cur;
loop
    fetch cur into test_cur;
    exit when not found;
    raise info 'lock details- %', test_cur.pid::text||','||test_cur.state||','||test_cur.usename||','||test_cur.query||','||test_cur.query_start::text;
end loop;
close cur;

//it prints-INFO:  lock details - 3704853,active,username,select myfunction();,2022-02-21 05:39:54.220118-05

drop table if exists temp_1; 

open cur;
loop
    fetch cur into test_cur;
    exit when not found;
    raise info 'lock details- %', test_cur.pid::text||','||test_cur.state||','||test_cur.usename||','||test_cur.query||','||test_cur.query_start::text;
end loop;
close cur;

//it prints-INFO:  lock details - 3704853,active,username,select myfunction();,2022-02-21 05:39:54.220118-05

END;

I am surprise to see that table always shows locks in it. Even after the dropping temp table which was created using mytable. Why the lock is not released ? Will it release only after the execution ? Is there any way to avoid or release lock on table mytablename , in function. Because I put a update on mytablename later in this function but it goes on hold forever and have to kill the session at end.

please suggest.

CodePudding user response：

Because every PostGreSQL functions are involved in a transaction that begin before the execution of the function and ends after...

Lock is to prevent if the drop will be effective or not. Imagine the transaction after executing the function has been released... ? What happen ? The ROLLBACK cannot apply if any !