Perché non riesco a eliminare un oggetto nel cluster Amazon Redshift?

Breve descrizione

Potresti non essere in grado di eliminare un oggetto, ad esempio una tabella o una vista, nel cluster Amazon Redshift per i seguenti motivi:

• Autorizzazioni insufficienti: l'utente non dispone delle autorizzazioni per eliminare l'oggetto. L'utente deve essere il proprietario dell'oggetto o disporre delle autorizzazioni di amministratore.
• Dipendenza dell'oggetto: un'altra vista o tabella si riferisce alle colonne della tabella.
• Contesa di blocco: una transazione blocca l'oggetto e causa il blocco dell'operazione di eliminazione.

Risoluzione

Autorizzazioni insufficienti

In Amazon Redshift, solo il proprietario della tabella, il proprietario dello schema o un superutente può eliminare una tabella.

Per confermare le autorizzazioni e la proprietà dell'utente, esegui lo script v_get_obj_priv_by_user.sql dal sito web 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)
;

Per trovare il proprietario della relazione, esegui la seguente query:

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

Nota: sostituisci schema_name con il nome del tuo schema e relation_name con il nome della tua relazione.

Dipendenza dell’oggetto

L'operazione di eliminazione potrebbe non riuscire e restituire il seguente messaggio di errore:

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

L'errore di operazione non valida indica la presenza di dipendenze nell'oggetto di destinazione.

Per identificare gli oggetti che dipendono dalla tabella di destinazione, crea le tre viste seguenti:

• Una vista per identificare la dipendenza da vincoli. Per ulteriori informazioni, consulta v_constraint_dependency.sql sul sito web GitHub.
• Una vista per identificare le viste dipendenti. Per ulteriori informazioni, consulta v_view_dependency.sql sul sito web GitHub.
• Una vista dell'oggetto che aggrega le due viste precedenti. Per ulteriori informazioni, consulta v_object_dependency.sql sul sito web GitHub.

Dopo avere creato le tre viste, esegui lo script v_object_dependency.sql per ottenere gli oggetti dipendenti dell'oggetto di destinazione:

select * from admin.v_object_dependency where src_objectname=target object

Nota: sostituisci target object con il tuo oggetto di destinazione.

Utilizza il parametro CASCADE per eliminare tutti gli oggetti correlati insieme all'oggetto di destinazione:

drop table target object cascade;

Nota: sostituisci target object con il tuo oggetto di destinazione.

Contesa di blocco

Se il comando di eliminazione si blocca o non produce alcun effetto, è possibile che una transazione mantenga un blocco sull'oggetto. Di conseguenza, non è possibile acquisire l'AccessExclusiveLock sulla tabella. L'AccessExclusiveLock è necessario per eliminare un oggetto.

Per identificare eventuali blocchi, utilizza la seguente sintassi:

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

Utilizza PG_TERMINATE_BACKEND per eliminare i blocchi. Per ulteriori informazioni su come rilevare ed eliminare i blocchi in Amazon Redshift, consulta Come posso rilevare e sbloccare i blocchi in Amazon Redshift?