From: Justin Pryzby <pryzby@telsasoft.com>
To: Manoj Kumar <manojkumar@temenos.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL blocked locks query
Date: Thu, 13 May 2021 15:09:25 -0500
Message-ID: <20210513200925.GL27406@telsasoft.com> (raw)
In-Reply-To: <SJ0PR04MB7662ACFCDECF38E62B08A38AD9519@SJ0PR04MB7662.namprd04.prod.outlook.com>
References: <SJ0PR04MB7662ACFCDECF38E62B08A38AD9519@SJ0PR04MB7662.namprd04.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, manojkumar@temenos.com
Subject: Re: PostgreSQL blocked locks query
In-Reply-To: <20210513200925.GL27406@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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