Thread: PostgreSQL blocked locks query

PostgreSQL blocked locks query

From

Manoj Kumar

Date:

13 May 2021, 16:54:32

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.

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.

Attachment

image001.png

Re: PostgreSQL blocked locks query

From

Justin Pryzby

Date:

13 May 2021, 23:09:25

On Thu, May 13, 2021 at 01:54:32PM +0000, 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
beenpassed 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:image001.png@01D74810.4207C100]
> 
> 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 <unnamed>: SELECT $1
LOG:  duration: 0.230 ms  parse <unnamed>: SELECT $1
DEBUG:  bind <unnamed> to <unnamed>
LOG:  duration: 0.141 ms  bind <unnamed>: SELECT $1
DETAIL:  parameters: $1 = '1'
LOG:  duration: 0.029 ms  execute <unnamed>: SELECT $1
DETAIL:  parameters: $1 = '1'

-- 
Justin

RE: Re: PostgreSQL blocked locks query

From

Manoj Kumar

Date:

14 May 2021, 09:51:36

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 <pryzby@telsasoft.com>
Sent: Thursday, May 13, 2021 10:09 PM
To: Manoj Kumar <manojkumar@temenos.com>
Cc: pgsql-performance@postgresql.org
Subject: [EXT MSG] Re: PostgreSQL blocked locks query

EXTERNAL source. Be CAREFUL with links / attachments

On Thu, May 13, 2021 at 01:54:32PM +0000, 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
beenpassed 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:image001.png@01D74810.4207C100]
>
> 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 <unnamed>: SELECT $1
LOG:  duration: 0.230 ms  parse <unnamed>: SELECT $1
DEBUG:  bind <unnamed> to <unnamed>
LOG:  duration: 0.141 ms  bind <unnamed>: SELECT $1
DETAIL:  parameters: $1 = '1'
LOG:  duration: 0.029 ms  execute <unnamed>: 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
forthe addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended
recipientis unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender.
Pleasenote that any views or opinions presented in this e-mail are solely those of the author and do not necessarily
representthose of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts
noliability for any damage caused by any malicious code or virus transmitted by this e-mail.