Redshift DROP and TRUNCATE Hanging with No Locks – Solution

redshift

I followed this procedure to check to see if a table lock is preventing a table from being deleted, but finding none. However, I am still unable to drop a table in Redshift. Are there other system tables I can check to see if something is locking a table and preventing the drop?

Best Answer

Please try this code to identify locks over past 7 days.

This is from the link here lock_wait.sql

WITH locks AS (
SELECT svv.xid
,      l.pid
,      svv.txn_owner as username
,      TRIM(d.datname) as dbname
,      svv.relation
,      TRIM(nsp.nspname) as schemaname
,      TRIM(c.relname) as objectname
,      l.mode
,      l.granted
,      svv.lockable_object_type as obj_type
,      svv.txn_start
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start)),2) as block_sec
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start))/60,2) as block_min
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start))/60/60,2) as block_hr
,      CASE WHEN l.granted is false THEN ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM rct.starttime)),2) ELSE NULL END as waiting
FROM   pg_catalog.pg_locks l
INNER JOIN pg_catalog.svv_transactions svv ON l.pid = svv.pid
AND   l.relation = svv.relation
AND   svv.lockable_object_type is not null
LEFT JOIN pg_catalog.pg_class c on c.oid = svv.relation
LEFT JOIN pg_namespace nsp ON nsp.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_database d on d.oid = l.database
LEFT OUTER JOIN stv_recents rct ON rct.pid = l.pid
WHERE  l.pid <> pg_backend_pid()
)
select distinct * from (
SELECT l.xid
,     l.pid
,      l.username
,      l.dbname
,      l.relation
,      l.schemaname
,      l.objectname
,      l.mode
,      DECODE(l.granted, true, 'True', false, 'False') granted
,      l.obj_type
,      l.txn_start
,      DECODE(l.granted, true, l.block_sec, NULL) as block_sec
,      DECODE(l.granted, true, l.block_min, NULL) as block_min
,      DECODE(l.granted, true, l.block_hr, NULL) as block_hr
,      waiting
,      b.max_sec_blocking
,      b.num_blocking
,      b.pidlist
FROM   locks l
LEFT OUTER JOIN
      (
       SELECT relation
       ,      mode
       ,      listagg(b.pid, ',') as pidlist
       ,      MIN(block_sec) as min_sec_blocking
       ,      MAX(waiting) as max_sec_blocking
       ,      COUNT(*) as num_blocking
       FROM   locks b
       WHERE  granted is false
       GROUP BY relation
       ,      mode
      ) b
  ON  l.relation = b.relation
AND  l.granted is true
AND (l.mode like '%Exclusive%'
OR (l.mode like '%Share%' AND b.mode like '%ExclusiveLock' and b.mode not like '%Share%'))
)
-- where objectname like 'pg_%'
ORDER BY granted DESC
,       max_sec_blocking desc nulls last
,      block_sec DESC,
waiting desc nulls last
;
Related Question