Sojan
daacd6eda5
- Extract query inspection logic to Database::QueryInspection - Extract query logging to Database::QueryLogging - Move connection diagnostics to Database::ConnectionDiagnostics - Fix all rubocop issues by improving code organization
92 lines
3.3 KiB
Ruby
92 lines
3.3 KiB
Ruby
module Database::QueryInspection
|
|
extend ActiveSupport::Concern
|
|
|
|
private
|
|
|
|
def active_queries_sql
|
|
<<~SQL.squish
|
|
SELECT pid,#{' '}
|
|
now() - pg_stat_activity.query_start AS duration,
|
|
query,
|
|
state,
|
|
wait_event_type,
|
|
wait_event,
|
|
backend_type,
|
|
application_name,
|
|
client_addr,
|
|
usename
|
|
FROM pg_stat_activity#{' '}
|
|
WHERE state <> 'idle'
|
|
AND query NOT ILIKE '%pg_stat_activity%'
|
|
ORDER BY duration DESC;
|
|
SQL
|
|
end
|
|
|
|
def process_active_query_row(row)
|
|
{
|
|
pid: row['pid'],
|
|
duration: row['duration'].to_s,
|
|
state: row['state'],
|
|
query: row['query'],
|
|
wait_event_type: row['wait_event_type'],
|
|
wait_event: row['wait_event'],
|
|
backend_type: row['backend_type'],
|
|
application_name: row['application_name'],
|
|
client_addr: row['client_addr'],
|
|
username: row['usename']
|
|
}
|
|
end
|
|
|
|
def fetch_active_queries
|
|
query_data = ActiveRecord::Base.connection.execute(active_queries_sql)
|
|
query_data.map { |row| process_active_query_row(row) }
|
|
end
|
|
|
|
def locked_queries_sql
|
|
<<~SQL.squish
|
|
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 blocking_statement,
|
|
now() - blocking_activity.query_start AS blocking_duration
|
|
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;
|
|
SQL
|
|
end
|
|
|
|
def process_lock_row(row)
|
|
{
|
|
blocked_pid: row['blocked_pid'],
|
|
blocked_user: row['blocked_user'],
|
|
blocking_pid: row['blocking_pid'],
|
|
blocking_user: row['blocking_user'],
|
|
blocked_statement: row['blocked_statement'],
|
|
blocking_statement: row['blocking_statement'],
|
|
blocking_duration: row['blocking_duration'].to_s
|
|
}
|
|
end
|
|
|
|
def fetch_locked_queries
|
|
lock_data = ActiveRecord::Base.connection.execute(locked_queries_sql)
|
|
return [] if lock_data.count.zero?
|
|
|
|
lock_data.map { |row| process_lock_row(row) }
|
|
end
|
|
end
|