Skip to content

Instantly share code, notes, and snippets.

@jayelkaake

jayelkaake/locks.sql

Forked from zm69/locks_and_blocking.sql
Last active October 23, 2018 22:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jayelkaake/efe336b613657c20f47a7fd4879d73cc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jayelkaake/efe336b613657c20f47a7fd4879d73cc to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jayelkaake revised this gist Aug 16, 2018. No changes.
  2. jayelkaake revised this gist Aug 16, 2018. 1 changed file with 26 additions and 3 deletions.
    29 changes: 26 additions & 3 deletions locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,34 @@
    -- See summary of table sizes (index and storage sizes) sorted by largest size to smallest size
    SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size,
    ROUND(indexes_size/1024/1024) as indexes_size_mb,
    ROUND(table_size/1024/1024) as table_size_mb,
    ROUND(total_size/1024/1024) as total_size_mb
    FROM (
    SELECT
    table_name,
    pg_table_size(table_name) AS table_size,
    pg_indexes_size(table_name) AS indexes_size,
    pg_total_relation_size(table_name) AS total_size
    FROM (
    SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
    FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
    ) AS pretty_sizes


    -- See queries that have been running for longer than 15 seconds:
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active'
    AND (now() - query_start) > interval '15 seconds'
    ORDER BY query_start ASC

    --------- Select blocked
    --------- Select blocked queries
    SELECT DISTINCT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_activity.query AS current_statement_in_blocking_process
    @@ -27,7 +50,7 @@ SELECT DISTINCT blocked_locks.pid AS blocked_pid,
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED;

    --------- Count blocked
    --------- Count blocked queries
    SELECT COUNT(*) FROM (SELECT DISTINCT blocked_locks.pid AS blocked_pid
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    @@ -47,7 +70,7 @@ SELECT COUNT(*) FROM (SELECT DISTINCT blocked_locks.pid AS blocked_pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED) as FF;

    --------- Cancel all blocked
    --------- Cancel all blocked queries
    SELECT pg_cancel_backend(FF.blocked_pid) FROM (SELECT DISTINCT blocked_locks.pid AS blocked_pid
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
  3. jayelkaake revised this gist Aug 16, 2018. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,9 @@

    -- See queries that have been running for longer than 15 seconds:
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active'
    AND (now() - query_start) > interval '15 seconds'
    ORDER BY query_start ASC

    --------- Select blocked
    SELECT DISTINCT blocked_locks.pid AS blocked_pid,
  4. @zm69 zm69 created this gist Aug 16, 2018.
    63 changes: 63 additions & 0 deletions locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,63 @@


    --------- Select blocked
    SELECT DISTINCT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_activity.query AS current_statement_in_blocking_process
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED;

    --------- Count blocked
    SELECT COUNT(*) FROM (SELECT DISTINCT blocked_locks.pid AS blocked_pid
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED) as FF;

    --------- Cancel all blocked
    SELECT pg_cancel_backend(FF.blocked_pid) FROM (SELECT DISTINCT blocked_locks.pid AS blocked_pid
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED) as FF;