pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL blocked locks query
3+ messages / 2 participants
[nested] [flat]
* PostgreSQL blocked locks query
@ 2021-05-13 13:54 Manoj Kumar <manojkumar@temenos.com>
2021-05-13 20:09 ` Re: PostgreSQL blocked locks query Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 3+ messages in thread
From: Manoj Kumar @ 2021-05-13 13:54 UTC (permalink / raw)
To: pgsql-performance
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:image001.png@01D74810.4207C100]
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.
Attachments:
[image/png] image001.png (12.6K, ../../SJ0PR04MB7662ACFCDECF38E62B08A38AD9519@SJ0PR04MB7662.namprd04.prod.outlook.com/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL blocked locks query
2021-05-13 13:54 PostgreSQL blocked locks query Manoj Kumar <manojkumar@temenos.com>
@ 2021-05-13 20:09 ` Justin Pryzby <pryzby@telsasoft.com>
2021-05-14 06:51 ` RE: Re: PostgreSQL blocked locks query Manoj Kumar <manojkumar@temenos.com>
0 siblings, 1 reply; 3+ messages in thread
From: Justin Pryzby @ 2021-05-13 20:09 UTC (permalink / raw)
To: Manoj Kumar <manojkumar@temenos.com>; +Cc: pgsql-performance
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 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: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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Re: PostgreSQL blocked locks query
2021-05-13 13:54 PostgreSQL blocked locks query Manoj Kumar <manojkumar@temenos.com>
2021-05-13 20:09 ` Re: PostgreSQL blocked locks query Justin Pryzby <pryzby@telsasoft.com>
@ 2021-05-14 06:51 ` Manoj Kumar <manojkumar@temenos.com>
0 siblings, 0 replies; 3+ messages in thread
From: Manoj Kumar @ 2021-05-14 06:51 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
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 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: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 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-05-14 06:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-13 13:54 PostgreSQL blocked locks query Manoj Kumar <manojkumar@temenos.com>
2021-05-13 20:09 ` Justin Pryzby <pryzby@telsasoft.com>
2021-05-14 06:51 ` Manoj Kumar <manojkumar@temenos.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox