为什么我无法删除我的 Amazon Redshift 集群中的对象？

简短描述

由于以下原因，您可能无法删除 Amazon Redshift 集群中的对象（例如，表或视图）：

• **权限不足：**用户没有删除对象的权限。用户必须是对象的所有者或者具有管理员权限。
• **对象依赖项：**其他视图或表正在引用表列。
• **锁定争用：**事务锁定了对象，并导致删除操作挂起。

解决方案

权限不足

在 Amazon Redshift 中，只有表所有者、架构所有者或者超级用户可以删除表。

要确认用户权限和所有权，请从 GitHub 网站运行 v_get_obj_priv_by_user.sql 脚本：

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)
;

要查找关系的所有者，请运行下面的查询：

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

**注意：**请将 schema_name 替换为您的架构的名称，并将 relation_name 替换为您的关系的名称。

对象依赖项

您的删除操作可能会失败，并显示以下错误消息：

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

Invalid operation 错误表示目标对象存在对象依赖项。

要识别依赖于目标表的对象，请创建下面的三个视图：

• 用于识别约束依赖项的视图。有关更多信息，请参阅 GitHub 网站上的 v_constraint_dependency.sql。
• 用于识别依赖视图的视图。有关更多信息，请参阅 GitHub 网站上的 v_view_dependency.sql。
• 聚合前两个视图的对象视图。有关更多信息，请参阅 GitHub 网站上的 v_object_dependency.sql。

在创建三个视图后，运行 v_object_dependency.sql 脚本以获取目标对象的依赖对象：

select * from admin.v_object_dependency where src_objectname=target object

**注意：**请将 target object 替换为您的目标对象。

使用 CASCADE 参数删除所有相关对象以及目标对象：

drop table target object cascade;

**注意：**请将 target object 替换为您的目标对象。

锁定争用

如果执行删除操作时 drop 命令挂起或未输出任何内容，则事务可能锁定了该对象。因此，您无法在表上获取 AccessExclusiveLock。删除对象需要 AccessExclusiveLock。

要识别任何锁，请使用下面的语法：

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);

使用 PG_TERMINATE_BACKEND 释放锁。有关如何在 Amazon Redshift 中检测和释放锁定的更多信息，请参阅 How do I detect and release locks in Amazon Redshift?