CODEWITHSUNDEEP
amazon-redshift
Shenanigator
Shenanigator

Reputation: 1066

Redshift DROP TABLE never stops executing

I tried attempted to execute all of the following and the query was still running after over 400 seconds in all cases causing me to have to alt+f4 the navicat program using the created admin account whom is a super user.

DROP TABLE IF EXISTS test.<table> CASCADE;
DROP TABLE IF EXISTS test.<table>;
DROP TABLE test.<table>;

https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html says that is proper syntax, what should I be looking at on my cluster to figure out why these queries never execute and just go on forever. I am passing schema because there is a production table in the database with the same name and I cannot delete it, this table exists within the test schema. Is schema inclusion the problem?

Upvotes: 4

Views: 4734

Answers (2)

Aashish Kumar
Aashish Kumar

Reputation: 2879

This is happens when another process using that table i.e. Deadlock

You can use this query to find out the pid of your lock transaction and kill it.

select a.txn_owner, a.txn_db, 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;

SELECT pg_terminate_backend(<blocking pid>);

Upvotes: 1

Shenanigator
Shenanigator

Reputation: 1066

The article AWSQuickSolutions: Redshift Table Can’t Be Dropped or Drop Table Hangs helped me find an answer; I didn't realize there was a pendning backend lock on the table. Found the lock and removed the pending query.

Upvotes: 7

Related Questions