PostgreSQL blocked locks query
Hi Team, I have query in terms of lock monitoring in PostgreSQL where I am not able to find a way to figure out what value has been passed in SQL statement (from JDBC driver as prepared statement). I am using PostgreSQL 13 version. The following is the SQL statement I am running in PGAdmin SELECT activity.pid as BlockedPid, activity.usename, activity.query, blocking.pid AS Blocking_Pid, to_char(now() - activity.query_start, 'HH24:MI:SS') as elapsed FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid)); The output I am getting is below, where in the SQL query is with $1. [cid:[email protected]] Is there a way to compute what is being passed as value for the above SQL statement ? Thanks The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.
RE: Re: PostgreSQL blocked locks query
Hi , Thank you for the reply. Is there a similar way to extract the same from a SQL command ? Thanks -Original Message- From: Justin Pryzby Sent: Thursday, May 13, 2021 10:09 PM To: Manoj Kumar Cc: [email protected] Subject: [EXT MSG] Re: PostgreSQL blocked locks query EXTERNAL source. Be CAREFUL with links / attachments On Thu, May 13, 2021 at 01:54:32PM +, Manoj Kumar wrote: > I have query in terms of lock monitoring in PostgreSQL where I am not able to > find a way to figure out what value has been passed in SQL statement (from > JDBC driver as prepared statement). > > I am using PostgreSQL 13 version. > > The following is the SQL statement I am running in PGAdmin > > The output I am getting is below, where in the SQL query is with $1. > > [cid:[email protected]] > > Is there a way to compute what is being passed as value for the above SQL > statement ? You should enable query logging, and pull the params out of the log. Note that v13 has log_parameter_max_length, which defaults to showing params in full. [pryzbyj@telsasoft2019 ~]$ PGOPTIONS='-c log_min_duration_statement=0 -c client_min_messages=debug' python3 -c "import pg; db=pg.DB('postgres'); q=db.query('SELECT \$1', 1)" DEBUG: loaded library "auto_explain" DEBUG: parse : SELECT $1 LOG: duration: 0.230 ms parse : SELECT $1 DEBUG: bind to LOG: duration: 0.141 ms bind : SELECT $1 DETAIL: parameters: $1 = '1' LOG: duration: 0.029 ms execute : SELECT $1 DETAIL: parameters: $1 = '1' -- Justin The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.
Performance Issue on a table
Dear Team,
Recently we have noticed that in one of our DB instances there is a potential
delay in querying a table from java code. could you please check the attached
log and help understand what is the problem and which direction should be look
into solving this delay of 4 odd mins ?
The table definition is as below, it contains around 2 billion rows.
create table "TAFJ_HASHLOCKS" (recid integer);
alter table "TAFJ_HASHLOCKS" add constraint "TAFJ_HASHLOCKS_PK" PRIMARY KEY
(recid);
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST: simple execute,
handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@13e344d,
maxRows=0, fetchSize=0, flags=1
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery
FINEST: FE=> SimpleQuery(query="SAVEPOINT PGJDBC_AUTOSAVE")
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST: FE=> Parse(stmt=null,query="SELECT RECID FROM TAFJ_HASHLOCKS WHERE
RECID = $1 FOR UPDATE NOWAIT ",oids={1043})
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST: FE=> Bind(stmt=null,portal=null,$1=<'256292129'>,type=VARCHAR)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl
sendDescribePortal
FINEST: FE=> Describe(portal=null)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST: FE=> Execute(portal=null,limit=0)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl sendSync
FINEST: FE=> Sync
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl
receiveCommandStatus
FINEST: <=BE CommandStatus(RELEASE)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl receiveRFQ
FINEST: <=BE ReadyForQuery(T)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl
receiveCommandStatus
FINEST: <=BE CommandStatus(SAVEPOINT)
Jul 22, 2021 4:25:00 PM org.postgresql.core.v3.QueryExecutorImpl receiveRFQ
FINEST: <=BE ReadyForQuery(T)
Jul 22, 2021 4:29:20 PM org.postgresql.core.v3.QueryExecutorImpl processResults
FINEST: <=BE ParseComplete [null]
Jul 22, 2021 4:29:20 PM org.postgresql.core.v3.QueryExecutorImpl processResults
FINEST: <=BE BindComplete [unnamed]
Jul 22, 2021 4:29:20 PM org.postgresql.core.v3.QueryExecutorImpl receiveFields
FINEST: <=BE RowDescription(1)
Thanks
The information in this e-mail and any attachments is confidential and may be
legally privileged. It is intended solely for the addressee or addressees. Any
use or disclosure of the contents of this e-mail/attachments by a not intended
recipient is unauthorized and may be unlawful. If you have received this e-mail
in error please notify the sender. Please note that any views or opinions
presented in this e-mail are solely those of the author and do not necessarily
represent those of TEMENOS. We recommend that you check this e-mail and any
attachments against viruses. TEMENOS accepts no liability for any damage caused
by any malicious code or virus transmitted by this e-mail.
pgjdbc.log
Description: pgjdbc.log
