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

[postgann2020 s]

Hi Team,

Good Evening,

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.

Blocking_PROCS_With_PIDS.csv
Description: MS-Excel spreadsheet

pg_stat_activity_output_for_clarification.xlsx
Description: MS-Excel 2007 spreadsheet

Lock_Reference_For_PROC.xlsx
Description: MS-Excel 2007 spreadsheet