Why am I unable to DROP an object in my Amazon Redshift cluster?

Last updated: 2020-09-10

I'm unable to drop a table or a view in my Amazon Redshift cluster. Why is this happening?

Short description

You 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 table
  • Lock contention: A transaction is holding a lock on the object, which causes the drop operation to hang.

Resolution

Insufficient permissions

In 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_user
AS
SELECT

Object dependency

Your drop operation might fail with the following error message:

Invalid operation: cannot drop table/view <object_name> because other objects depend on it

The Invalid operation error indicates object dependencies on the target object.

To identify the objects that depend on the target table, create these three views:

After 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 contention

If 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_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;
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?


Did this article help?


Do you need billing or technical support?