Por que não consigo descartar um objeto no meu cluster do Amazon Redshift?

Breve descrição

Talvez você não consiga descartar um objeto, como uma tabela ou uma visualização, em seu cluster do Amazon Redshift pelos seguintes motivos:

• Permissões insuficientes: o usuário não tem permissão para descartar o objeto. O usuário deve ser proprietário do objeto ou ter permissões de administrador.
• Dependência de objetos: outra exibição ou tabela está fazendo referência às colunas da tabela.
• Contenção de bloqueio: uma transação bloqueia o objeto e faz com que a operação de descarte seja interrompida.

Resolução

Permissões insuficientes

No Amazon Redshift, somente o proprietário da tabela, o proprietário do esquema ou um superusuário pode descartar uma tabela.

Para confirmar as permissões e a propriedade do usuário, execute o script v_get_obj_priv_by_user.sql no site do GitHub:

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

Para encontrar o proprietário da relação, execute a seguinte consulta:

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

Observação: substitua schema_name pelo nome do seu esquema e relation_name pelo nome da sua relação.

Dependência de objetos

Sua operação de descarte pode falhar com a seguinte mensagem de erro:

"Operação inválida: não é possível descartar a tabela/visualização porque outros objetos dependem dela"

O erro Operação inválida indica que há dependências de objetos no objeto de destino.

Para identificar os objetos que dependem da tabela de destino, crie as três visualizações a seguir:

• Uma visão para identificar a dependência da restrição. Para obter mais informações, consulte v_constraint_dependency.sql no site do GitHub.
• Uma visualização para identificar as visualizações dependentes. Para obter mais informações, consulte v_view_dependency.sql no site do GitHub.
• Uma visualização de objeto que agrega as duas visualizações anteriores. Para obter mais informações, consulte v_object_dependency.sql no site do GitHub.

Depois de criar as três visualizações, execute o script v_object_dependency.sql para obter os objetos dependentes do objeto de destino:

select * from admin.v_object_dependency where src_objectname=target object

Observação: substitua target object pelo seu objeto alvo.

Use o parâmetro CASCADE para descartar todos os objetos relacionados junto ao objeto de destino:

drop table target object cascade;

Observação: substitua target object pelo seu objeto alvo.

Contenção de bloqueio

Se o comando drop travar ou não gerar nada quando você executa um descarte, uma transação pode estar bloqueando o objeto. Como resultado, você não pode adquirir AccessExclusiveLock na tabela. AccessExclusiveLock é necessário para descartar um objeto.

Para identificar bloqueios, use a seguinte sintaxe:

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 para liberar os bloqueios. Para obter mais informações sobre como detectar e liberar bloqueios no Amazon Redshift, consulte Como detectar e liberar bloqueios no Amazon Redshift?