Could someone please help us share the procedure to troubleshoot thelocks on proc issues. - Mailing list pgsql-general

Could someone please help us share the procedure to troubleshoot the locks on proc issues.

Environment:
============
 1 pgpool server (Master Pool Node) using Straming replication with load balancing
 4 DB nodes (1Master and 3 Slaves).
 
 Versions:
 1. postgres: 9.5.15
 2. pgpool   : 3.9
 3. repmgr:  4.1

We are continuously facing locking issues for below procedures , due to this the  rest of the call for these procs going into waiting state.Which cause the DB got hung. Below are the procs  running with DB_User2 from the application.
 
1. select * from Schema1.duct_remove_validation($1,$2,$3,$4)  ==> This proc it self calling Schema1.cable_remove_validation($1,$2).
2. select * from Schema1.cable_remove_validation($1,$2)  ==> This is also calling from the applications

if we ran explain analyze, its taking msec only, but if we run simultaneouly from application getting locked and waiting state.

We have ran below query for showing blocking queries and attached output in Blocking_Queries_with_PID.csv file:

SELECT
pl.pid as blocked_pid
,psa.usename as blocked_user
,pl2.pid as blocking_pid
,psa2.usename as blocking_user
,psa.query as blocked_statement
FROM pg_catalog.pg_locks pl
JOIN pg_catalog.pg_stat_activity psa
ON pl.pid = psa.pid
JOIN pg_catalog.pg_locks pl2
JOIN pg_catalog.pg_stat_activity psa2
ON pl2.pid = psa2.pid
ON pl.transactionid = pl2.transactionid
AND pl.pid != pl2.pid
WHERE NOT pl.granted;

Output: attached output in Blocking_Queries_with_PID.csv file


The waiting connections are keep on accumulating and cause DB hung.
I have attached pg_stat_activity excel file with the user along with the proc queries which cause waiting state.

Finds:

There are total 18 connections for DB_User2 which are running only above 2 procs, Out of that only one connection with 18732 is running proc (select * from Schema1.duct_remove_validation($1,$2,$3,$4))from long time  and reset of all 17 connections are in waiting state from the long time.

There are many exclusive locks on table for 18732 and other process as well. I have attached pg_locks reference excel(Lock_Reference_For_PROC) with highlighted pid 18732.

Could someone please suggest the procedure to troubleshoot this issue.

Please find the attachment for reference.

Thanks,
Postgann.