Date: Thu, 13 May 2021 15:09:25 -0500
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
Message-ID: <20210513200925.GL27406@telsasoft.com>
References: 
 <SJ0PR04MB7662ACFCDECF38E62B08A38AD9519@SJ0PR04MB7662.namprd04.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <SJ0PR04MB7662ACFCDECF38E62B08A38AD9519@SJ0PR04MB7662.namprd04.prod.outlook.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210513200925.GL27406%40telsasoft.com>
Precedence: bulk

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