Find Locking/Blocking Redshift Queries

https://aws.amazon.com/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/

#!/bin/bash

#################################################################################
# findlockblocks.sh
#
# Dead-stupid script that leverages existing RS queries and does a mashup that reports
# the current running queries that are blocking others, sorted by time running.
#
# Nice, simple way to see if there's actually a problem or if RS is just swamped.
#
#                
# TODO: Utilize a sourced bash file with usernames and passwords, as with other scripts                   
# in the library.                    
#                    
#################################################################################

RSUSER="vacasaroot"
RSPASS="xxxxxxxxxx"

read -r -d '' "stats_sql" << 'EOF'

SELECT
  a.txn_owner,
  a.xid,
  a.pid,
  a.txn_start,
  a.lock_mode,
  a.relation AS table_id,
  nvl (TRIM(c."name"), d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid,
  DATEDIFF(s, a.txn_start, getdate ()) / 86400 || ' days ' || DATEDIFF(s, a.txn_start, getdate ()) % 86400 / 3600 || ' hrs ' || DATEDIFF(s, a.txn_start, getdate ()) % 3600 / 60 || ' mins ' || DATEDIFF(s, a.txn_start, getdate ()) % 60 || ' secs' AS txn_duration
FROM
  svv_transactions a
  LEFT JOIN
    (SELECT
      pid,
      relation,
      granted
    FROM
      pg_locks
    GROUP BY 1,
      2,
      3) b
    ON a.relation = b.relation
    AND a.granted = 'f'
    AND b.granted = 't'
  LEFT JOIN
    (SELECT
      *
    FROM
      stv_tbl_perm
    WHERE slice = 0) c
    ON a.relation = c.id
  LEFT JOIN pg_class d
    ON a.relation = d.oid
WHERE a.relation IS NOT NULL;

EOF

echo "$stats_sql" > /tmp/stats.sql


BLOCKPIDS=`cat /tmp/stats.sql | PGPASSWORD=${RSPASS} psql -h  warehouse.vacasa.services -p 5439 -U${RSUSER} -dwarehouse | cut -d'|' -f9 | grep "\S" | egrep -v "blocking|rows|\-\-\-"| sed 's/ //g' | sort -u`

#construct IN clause

for PID in $BLOCKPIDS
do
PIDCLAUSE="$PIDCLAUSE,$PID"
done
PIDCLAUSE=" AND pid in (${PIDCLAUSE:1}) "


stats_sql="select pid, duration/1000000 as seconds, trim(user_name) as user,substring(query,1,200) as querytxt from stv_recents where status = 'Running' and seconds >=1 ${PIDCLAUSE} order by seconds desc;"

echo $stats_sql > /tmp/stats.sql


echo "

Current active Redshift queries that are blocking the execution of other queries (may or may not be critical)
=============================================================================================================
"


cat /tmp/stats.sql | PGPASSWORD=${RSPASS} psql -h  warehouse.vacasa.services -p 5439 -U${RSUSER} -dwarehouse