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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)