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
Database Blog 