為什麼我無法捨棄 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"

無效操作錯誤表示對目標物件有物件相依性。

若要識別相依於目標資料表的物件，請建立下列三個檢視：

• 用於識別限制相依性的檢視。如需詳細資訊，請參閱 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 中偵測和釋放鎖定的詳細資訊，請參閱如何在 Amazon Redshift 中偵測和釋放鎖定？