Meaning of transaction pg_locks?

Sorry, should RTFM more closely:

"If a transaction is waiting for a row-level lock, it will usually
appear in the view as waiting for the transaction ID
of the current holder of that row lock."

so I need to look at the row locks on the blocker.

Philip Warner wrote:

Hi,

Can anyone explain the way to debug this kind of situation and/or
explain the meaning of these locks?

Partial output of "select * from pg_locks":

| | 1192675195 | 62860 | ShareLock | f
| | 1192675195 | 62814 | ExclusiveLock | t
| | 1192675195 | 62838 | ShareLock | f
| | 1192675195 | 63525 | ShareLock | f

where 1192675195 is the 'transaction' field.

I am not at all clear what the processes are waiting for, or if there is
a way to reduce such contention.

--
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 03 5330 3171 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
http://www.rhyme.com.au <http://www.rhyme.com.au/&gt;
| / \|
| --________--
GPG key available upon request. | /
|/

Philip Warner <pjw@rhyme.com.au> writes:

Partial output of "select * from pg_locks":

| | 1192675195 | 62860 | ShareLock | f
| | 1192675195 | 62814 | ExclusiveLock | t
| | 1192675195 | 62838 | ShareLock | f
| | 1192675195 | 63525 | ShareLock | f

where 1192675195 is the 'transaction' field.

I am not at all clear what the processes are waiting for,

Neither are we, because you left out all the columns that might tell
that ...

regards, tom lane

Tom Lane wrote:

Neither are we, because you left out all the columns that might tell
that ...

The columns are actually blank....it's the other rows I left out with
the row-level locks:

925282231 | 925280527 | | 62814 | RowExclusiveLock | t
925282208 | 925280527 | | 62814 | RowExclusiveLock | t
| | 1192675195 | 62814 | ExclusiveLock | t
925282207 | 925280527 | | 62814 | RowExclusiveLock | t
925282025 | 925280527 | | 62814 | AccessShareLock | t
925282025 | 925280527 | | 62814 | RowExclusiveLock | t
925282206 | 925280527 | | 62814 | RowExclusiveLock | t
925282212 | 925280527 | | 62814 | RowExclusiveLock | t
925282210 | 925280527 | | 62814 | RowExclusiveLock | t
925282209 | 925280527 | | 62814 | RowExclusiveLock | t
925281338 | 925280527 | | 62814 | AccessShareLock | t
925281338 | 925280527 | | 62814 | RowExclusiveLock | t
925282211 | 925280527 | | 62814 | RowExclusiveLock | t
925282213 | 925280527 | | 62814 | RowExclusiveLock | t

So I assume the processes waiting on the TX were waiting for one or more
of those rows.

Now I just need to figure out why the rows were locked for such a long
time (the row level locks are mostly on one table and various indexes of
that table).

It might be helpful to look at pg_stat_activity.

...Robert