Amazon Redshift クラスターでオブジェクトを削除できないのはなぜですか?

簡単な説明

以下の理由により、Amazon Redshift クラスター内のオブジェクト (テーブルやビューなど) を削除できない場合があります。

• **不十分な権限:**ユーザーにオブジェクトを削除する権限がない。ユーザーはオブジェクトの所有者であるか、管理者権限を持っている必要があります。
• **オブジェクト依存:**別のビューまたはテーブルがテーブル列を参照している。
• **ロックの競合:**トランザクションによってオブジェクトがロックされ、削除の操作がハングアップする。

解決策

不十分な権限

Amazon Redshift では、テーブル所有者、スキーマ所有者、またはスーパーユーザーのみがテーブルを削除できます。

ユーザーの権限と所有権を確認するには、GitHub Web サイトから 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 エラーは、ターゲットオブジェクトにオブジェクト依存があることを示しています。

ターゲットテーブルに依存するオブジェクトを特定するには、次の 3 つのビューを作成します。

• 制約の依存を特定するためのビュー。詳細については、GitHub ウェブサイトの v_constraint_dependency.sql を参照してください。
• 依存ビューを特定するためのビュー。詳細については、GitHub ウェブサイトの v_view_dependency.sql を参照してください。
• 上の 2 つのビューを集約したオブジェクトビュー。詳細については、GitHub ウェブサイトの v_object_dependency.sql を参照してください。

3 つのビューを作成したら、v_object_dependency.sql スクリプトを実行してターゲットオブジェクトの依存オブジェクトを取得します。

select * from admin.v_object_dependency where src_objectname=target object

注:****ターゲットオブジェクトはご自分のターゲットオブジェクトに置き換えます。

CASCADE パラメータを使用して、関連するすべてのオブジェクトをターゲットオブジェクトと共に削除します。

drop table target object cascade;

注:****ターゲットオブジェクトはご自分のターゲットオブジェクトに置き換えます。

ロックの競合

削除を実行したときに 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 でロックを検出して解除する方法を教えてください」を参照してください。