check_postgres_locks.py

from invenio_db import db

def get_indexes_info():
    return list(db.engine.execute('''
        SELECT
        pg_class.relname,
        pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
        pg_class.reltuples AS num_rows,
        COUNT(indexname) AS number_of_indexes,
        CASE WHEN x.is_unique = 1 THEN 'Y'
           ELSE 'N'
        END AS UNIQUE,
        SUM(CASE WHEN number_of_columns = 1 THEN 1
                  ELSE 0
                END) AS single_column,
        SUM(CASE WHEN number_of_columns IS NULL THEN 0
                 WHEN number_of_columns = 1 THEN 0
                 ELSE 1
               END) AS multi_column
        FROM pg_namespace 
        LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
        LEFT OUTER JOIN
               (SELECT indrelid,
                   MAX(CAST(indisunique AS INTEGER)) AS is_unique
               FROM pg_index
               GROUP BY indrelid) x
               ON pg_class.oid = x.indrelid
        LEFT OUTER JOIN
            ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
                   JOIN pg_class c ON c.oid = x.indrelid
                   JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
            AS foo
            ON pg_class.relname = foo.ctablename
        WHERE 
             pg_namespace.nspname='public'
        AND  pg_class.relkind = 'r'
        GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
        ORDER BY 2;
        '''))

def get_running_queries():
    return list(db.engine.execute('''
        SELECT *
        FROM pg_stat_activity;
    '''))

def get_locks():
    return list(db.engine.execute('''                                                  
        SELECT blocked_locks.pid     AS blocked_pid,
              blocked_activity.usename  AS blocked_user,
              blocking_locks.pid     AS blocking_pid,
              blocking_activity.usename AS blocking_user,
              blocked_activity.query    AS blocked_statement,
              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;'''))

def get_connection(conn_id):
    return list(db.engine.execute('SELECT * FROM pg_stat_activity where pid = %s;' % conn_id))