How to see values passed to a query

[inve...@gmail.com]

Postgres 9.6.1

I have an insert query that appears to be ending up in a deadlock. If I check the pg_stat_activity and pg_blocking_pids() I've pretty much come down to one pid blocking the other, and t


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?

Thanks,

SteveN

[Laurenz Albe]

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.