Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #827

Re: How to see values passed to a query

Show all headers | View raw

On Wed, 02 May 2018 13:19:01 -0700, inverasln wrote:
> Postgres 9.6.1
> 
> I have an insert query that appears to be ending up in a deadlock.
> 
> 
> select pid, usename,
>        pg_blocking_pids(pid) as blocked_by,
>        query as blocked_query
> from   pg_stat_activity where  cardinality(pg_blocking_pids(pid)) > 0;
> 
> -[ RECORD 1 ]-+-----------------------------------------------------
> pid           | 14282 usename       | ap10310 blocked_by    | {12614}
> blocked_query | insert into sctdps_rec values ($1,$2,$3,$4,$5,$6,$7)
> 
> 
> select pid, wait_event_type, wait_event, state, query from  
> pg_stat_activity where  pid=12614;
> 
> -[ RECORD 1 ]---+----------------------------------------------------
> pid             | 12614 wait_event_type |
> wait_event      |
> state           | idle in transaction query           | select * from
> rprrcs_rec where rcs_rc_id = $1
>                   and  rcs_lng = $2 and  rcs_apln_pfx = $3 and 
>                   rcs_apln_no = $4 and  rcs_apln_no_sub = $5
> 
> 
> 
> While this is is great and I should be able to track down where and why
> these two became conflicted, I was just wondering if there is any place
> such as pg_stat_activity that might tell me what the values were that
> were set for the $1 through $7 when the query was formed. Is this a case
> where the explain would need to be used to see that?

First: you should be aware that it was *not* the SELECT statement that
took the conflicting lock, it must have been an earlier statement in the
same transaction.

Unfortunately you can neither find out the parameters of a prepared
statement nor which earlier statements ran in a transaction.

The only way to do that is to turn on statement logging, then you will
find the parameters in the PostgreSQL log file.

If you use "log_destination = stderr", you should put %c and %x into
log_line_prefix so that you can identify sessions and transactions.

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

How to see values passed to a query inverasln@gmail.com - 2018-05-02 13:19 -0700
  Re: How to see values passed to a query Laurenz Albe <laurenz@nospam.pn> - 2018-05-04 07:50 +0000

csiph-web