Why can't I DROP an object in my Amazon Redshift cluster?

4 minute read
0

I can't drop a table or a view in my Amazon Redshift cluster.

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 permissions to drop the object. The user must be an owner of the object or have admin permissions.
  • Object dependency: Another view or table is referring to the table columns.
  • Lock contention: A transaction is holding a lock on the object and causes the drop operation to hang.

Resolution

Insufficient permissions

In Amazon Redshift, only the table owner, the schema owner, or a superuser can drop a table.

To confirm user permissions and ownership, run the v_get_obj_priv_by_user.sql script from the GitHub website:

CREATE OR REPLACE VIEW admin.v_get_obj_priv_by_user
AS
SELECT
    * 
FROM
    (
    SELECT
         schemaname
        ,objectname
        ,objectowner
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
    FROM
        (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, tableowner as objectowner, QUOTE_IDENT(schemaname) || '.' || QUOTE_IDENT(tablename) AS fullobj FROM pg_tables
        WHERE schemaname !~ '^information_schema|catalog_history|pg_'
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, viewowner as objectowner, QUOTE_IDENT(schemaname) || '.' || QUOTE_IDENT(viewname) AS fullobj FROM pg_views
        WHERE schemaname !~ '^information_schema|catalog_history|pg_'
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
;

To find the owner of the relation, run the following query:

select schemaname,tablename, tableowner From pg_tables where schemaname='schema_name' and tablename='relation_name';

Note: Replace schema_name with your schema's name and relation_name with your relation's name.

Object dependency

Your drop operation might fail with the following error message:

"Invalid operation: cannot drop table/view because other objects depend on it"

The Invalid operation error indicates that there are object dependencies on the target object.

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

  • A view to identify the constraint dependency. For more information, see v_constraint_dependency.sql on the GitHub website.
  • A view to identify the dependent views. For more information, see v_view_dependency.sql on the GitHub website.
  • An object view that aggregates the two previous views. For more information, see v_object_dependency.sql on the GitHub website.

After you create the three views, run 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

Note: Replace target object with your target object.

Use the CASCADE parameter to drop all the related objects along with the target object:

drop table target object cascade;

Note: Replace target object with your target object.

Lock contention

If the drop command hangs or doesn't output anything when you perform a drop, then 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.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);

Use PG_TERMINATE_BACKEND to release the locks. For more information about how to detect and release locks in Amazon Redshift, see How do I detect and release locks in Amazon Redshift?

AWS OFFICIAL
AWS OFFICIALUpdated 3 months ago