Skip to content

Instantly share code, notes, and snippets.

@zm69

zm69/locks_and_blocking.sql

Last active August 17, 2018 18:54
Show Gist options
  • Save zm69/8b4a91a23008bae1488ebde23f6a1911 to your computer and use it in GitHub Desktop.
Save zm69/8b4a91a23008bae1488ebde23f6a1911 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. zm69 renamed this gist Aug 17, 2018. 1 changed file with 0 additions and 0 deletions.
    0 locks.sql → locks_and_blocking.sql
    View file
    File renamed without changes.
  2. zm69 revised this gist Aug 17, 2018. No changes.
  3. zm69 revised this gist Aug 17, 2018. 1 changed file with 20 additions and 0 deletions.
    20 changes: 20 additions & 0 deletions locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -84,6 +84,26 @@ SELECT pg_cancel_backend(FF.blocked_pid) FROM (SELECT DISTINCT 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 for blocking activity query
    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 AND blocking_activity.query = 'SELECT "shopper_counters".* FROM "shopper_counters" WHERE "shopper_counters"."id" = 286802 LIMIT 1 FOR UPDATE /*application:Angle*/') as FF
    ;

    -- See summary of table sizes (index and storage sizes) sorted by largest size to smallest size
    SELECT
  4. zm69 revised this gist Aug 17, 2018. 1 changed file with 31 additions and 0 deletions.
    31 changes: 31 additions & 0 deletions locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -83,3 +83,34 @@ SELECT pg_cancel_backend(FF.blocked_pid) FROM (SELECT DISTINCT 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;


    -- 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
  5. zm69 revised this gist Aug 17, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -44,7 +44,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 total 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
  6. zm69 revised this gist Aug 17, 2018. 1 changed file with 22 additions and 0 deletions.
    22 changes: 22 additions & 0 deletions locks.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,25 @@
    --------- Blocked statement and count
    SELECT count(current_statement_in_blocking_process), db.current_statement_in_blocking_process FROM (
    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) as db GROUP BY db.current_statement_in_blocking_process;


    --------- Select blocked
  7. 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;