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

참고: 대상 객체를 내 대상 객체로 바꿉니다.

대상 객체와 함께 모든 관련 객체를 삭제하려면 CASCADE 매개 변수를 사용합니다.

drop table target object cascade;

참고: 대상 객체를 내 대상 객체로 바꿉니다.

잠금 경합

드롭을 수행할 때 드롭 명령이 중단되거나 아무 것도 출력되지 않으면 트랜잭션이 객체를 잠근 상태일 수 있습니다. 따라서 테이블에서 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에서 잠금을 감지하고 해제하려면 어떻게 해야 하나요?를 참조하세요.