Why am I unable to DROP an object in my Amazon Redshift cluster?
I'm unable to drop a table or a view in my Amazon Redshift cluster. Why is this happening?
"I'm unable to drop a table or a view in my Amazon Redshift cluster. Why is this happening?Short descriptionYou might not be able to drop an object (such as a table or a view) in your Amazon Redshift cluster for the following reasons:Insufficient permissions: The user doesn't have the proper permissions to drop the object. (The user must be an owner of the object or have admin permissions.)Object dependency: The table columns are being referred to by another view or tableLock contention: A transaction is holding a lock on the object, which causes the drop operation to hang.ResolutionInsufficient permissionsIn Amazon Redshift, only the owner of the table, the schema owner, or a superuser can drop a table.To confirm user permissions and ownership, create a view using the v_get_obj_priv_by_user.sql script:CREATE OR REPLACE VIEW admin.v_get_obj_priv_by_userASSELECTObject dependencyYour drop operation might fail with the following error message:Invalid operation: cannot drop table/view <object_name> because other objects depend on itThe Invalid operation error indicates object dependencies on the target object.To identify the objects that depend on the target table, create these three views:A view to identify the constraint dependencyA view to identify the dependent viewsAn object view which aggregates the two previous viewsAfter you create these three views, use the v_object_dependency.sql script to get the dependent objects of the target object:select * from admin.v_object_dependency where src_objectname=<target object>After checking all the dependent objects, drop all the related objects along with the target object by using the CASCADE parameter:drop table <target object> cascade;Lock contentionIf the drop command hangs or doesn't output anything when you perform a drop, a transaction might be holding a lock on the object. As a result, you can't acquire the AccessExclusiveLock on the table. The AccessExclusiveLock is required to drop an object.To identify any locks, use the following syntax: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_durationfrom 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.oidwhere  a.relation is not null;And once you identify the locking transaction either COMMIT the blocking transaction or terminate the session of the blocking transaction if it is no longer necessary by :select pg_terminate_backend(PID);After you identify the locks, use PG_TERMINATE_BACKEND to release the locks. For more information about detecting and releasing locks in Amazon Redshift, see How do I detect and release locks in Amazon Redshift?Follow"