<commit_before># pglockanalyze: analyze postgres locking behavior

pglockanalyze examines queries from the Postgresql's "pg\_locks" system catalog
and produces some summary output about which backend processes are blocked on
which others.

This was really a one-off written to deal with a specific problem, but may be
helpful for others in the future.

Specifically, the Postgres wiki suggests using [this
query](https://wiki.postgresql.org/wiki/Lock_dependency_information) to get a
"flat view of blocking [processes]":

```sql
SELECT 
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.GRANTED              AS other_granted
FROM
    pg_catalog.pg_locks AS waiting
JOIN
    pg_catalog.pg_stat_activity AS waiting_stm
    ON (
        waiting_stm.pid = waiting.pid
    )
JOIN
    pg_catalog.pg_locks AS other
    ON (
        (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        )
        OR waiting.transactionid = other.transactionid
    )
JOIN
    pg_catalog.pg_stat_activity AS other_stm
    ON (
        other_stm.pid = other.pid
    )
WHERE
    NOT waiting.GRANTED
AND
    waiting.pid <> other.pid
```

Each row in the `pg_locks` view represents a Postgresql backend process either
holding or attempting to take a lock.  Processes holding or attempting to take
multiple locks may appear multiple times in the view.

The query above essentially selects all pairs of rows from "pg\_locks" that are
operating on the same lock, and then joins that information with the
"pg\_activity" information for each backend.  In principle, this can show you<commit_msg>add pg_locks documentation link to README<commit_after># pglockanalyze: analyze postgres locking behavior

pglockanalyze examines queries from the Postgresql's "pg\_locks" system catalog
and produces some summary output about which backend processes are blocked on
which others.

This was really a one-off written to deal with a specific problem, but may be
helpful for others in the future.

Specifically, the Postgres wiki suggests using [this
query](https://wiki.postgresql.org/wiki/Lock_dependency_information) to get a
"flat view of blocking [processes]":

```sql
SELECT 
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.GRANTED              AS other_granted
FROM
    pg_catalog.pg_locks AS waiting
JOIN
    pg_catalog.pg_stat_activity AS waiting_stm
    ON (
        waiting_stm.pid = waiting.pid
    )
JOIN
    pg_catalog.pg_locks AS other
    ON (
        (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        )
        OR waiting.transactionid = other.transactionid
    )
JOIN
    pg_catalog.pg_stat_activity AS other_stm
    ON (
        other_stm.pid = other.pid
    )
WHERE
    NOT waiting.GRANTED
AND
    waiting.pid <> other.pid
```

[Each row in the pg_locks
view](http://www.postgresql.org/docs/9.2/static/view-pg-locks.html) represents a
Postgresql backend process either holding or attempting to take a lock.
Processes holding or attempting to take multiple locks may appear multiple times
in the view.

The query above essentially selects all pairs of rows from "pg\_locks" that are
operating on the same lock, and then joins that information with the
"pg\_activity" information for each backend.  In principle, this can show you
{'fraction_non_alphanumeric': 0.05601907032181168, 'fraction_numerical': 0.0004767580452920143, 'mean_word_length': 2.7835888187556357, 'pattern_counts': {'":': 2, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4896557', 'n_tokens_mistral': 1187, 'n_tokens_neox': 1159, 'n_words': 420}