Subject: Re: Could someone please help us share the procedure to troubleshoot
 the locks on proc issues.
To: postgann2020 s <postgann2020@gmail.com>, pgsql-general@postgresql.org,
 pgsql-performance@postgresql.org, pgsql-admin@postgresql.org
References: 
 <CANynezMVB145OgFU_oniPQe9D_jBFtSSKJpO3-xBHe7T1L8geQ@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <b91f4296-c72f-ae4d-a788-4c9d2a193cd2@aklaver.com>
Date: Thu, 2 Apr 2020 16:00:12 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CANynezMVB145OgFU_oniPQe9D_jBFtSSKJpO3-xBHe7T1L8geQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 4/2/20 12:37 PM, postgann2020 s wrote:
> 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

To figure out below we need to see what is happening in above.

> 
> 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.


-- 
Adrian Klaver
adrian.klaver@aklaver.com