0

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?

  • did you try the solution beneath that one? looking at pg_locks? – Jon Scott Nov 2 '17 at 17:15
  • @JonScott Yes I did, and saw nothing – CBredlow Nov 2 '17 at 17:16
  • did you try the below? – Jon Scott Nov 3 '17 at 15:56
1

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.