Groups > comp.databases.postgresql > #827

Re: How to see values passed to a query

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From	Laurenz Albe <laurenz@nospam.pn>
Newsgroups	comp.databases.postgresql
Subject	Re: How to see values passed to a query
Date	Fri, 4 May 2018 07:50:41 -0000 (UTC)
Organization	A noiseless patient Spider
Lines	47
Message-ID	<pch3cg$uab$1@dont-email.me> (permalink)
References	<06c916a5-c85d-41d1-bfcb-fc04fe1af843@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	8bit
Injection-Date	Fri, 4 May 2018 07:50:41 -0000 (UTC)
Injection-Info	reader02.eternal-september.org; posting-host="72a1dc60a5eccf7fa1f343f715f7529a"; logging-data="31051"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18xSQ8dOm6z12bLhGwELlA0C2BDeP7Khtw="
User-Agent	Pan/0.144 (Time is the enemy; 28ab3ba git.gnome.org/pan2)
Cancel-Lock	sha1:K4VaWcScApyyd39pTyKHK6BkNCc=
Xref	csiph.com comp.databases.postgresql:827

Show key headers only | 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