Pourquoi ne puis-je pas SUPPRIMER un objet dans mon cluster Amazon Redshift ?

Brève description

Voici les raisons qui peuvent vous empêcher de supprimer un objet, tel qu’une table ou une vue, dans votre cluster Amazon Redshift :

• **Autorisations insuffisantes :**L’utilisateur n’est pas autorisé à supprimer l’objet. L’utilisateur doit être propriétaire de l’objet ou disposer de droits d’administrateur.
• **Dépendance à l’objet :**Une autre vue ou une autre table renvoient aux colonnes de cette table.
• **Conflit de verrouillage :**Une transaction maintient un verrou sur l’objet et entraîne le blocage de la suppression.

Résolution

Autorisations insuffisantes

Dans Amazon Redshift, le propriétaire de la table, le propriétaire du schéma ou un super utilisateur sont les seuls à pouvoir supprimer une table.

Pour confirmer les autorisations et la propriété de l’utilisateur, exécutez le script v_get_obj_priv_by_user.sql depuis le site 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)
;

Pour trouver le propriétaire de la relation, exécutez la requête suivante :

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

Remarque : Remplacez schema_name par le nom de votre schéma et relation_name par le nom de votre relation.

Dépendance à l’objet

Votre suppression peut échouer et afficher le message d’erreur suivant :

« Opération non valide : impossible de supprimer la table/la vue car d’autres objets en dépendent »

L’erreur opération non valide indique qu’il existe des dépendances à l’objet cible.

Pour identifier quels objets dépendent de la table cible, créez les trois vues suivantes :

• Une vue permettant d’identifier la dépendance de contrainte. Pour en savoir plus, consultez v_constraint_dependency.sql sur le site Web GitHub.
• Une vue permettant d’identifier les vues dépendantes. Pour en savoir plus, consultez v_view_dependency.sql sur le site Web GitHub.
• Une vue d’objet qui regroupe les deux vues précédentes. Pour en savoir plus, consultez v_object_dependency.sql sur le site Web GitHub.

Une fois que vous avez créé les trois vues, exécutez le script v_object_dependency.sql pour obtenir les objets dépendants de l’objet cible :

select * from admin.v_object_dependency where src_objectname=target object

**Remarque :**Remplacez objet cible par votre objet cible.

Utilisez le paramètre CASCADE pour supprimer tous les objets associés ainsi que l’objet cible :

drop table target object cascade;

**Remarque :**Remplacez objet cible par votre objet cible.

Conflit de verrouillage

Si la commande de suppression se bloque ou n’affiche rien lorsque vous effectuez une suppression, cela signifie peut-être qu’une transaction verrouille l’objet. Par conséquent, il est impossible d’acquérir le AccessExclusiveLock sur la table. Le AccessExclusiveLock est nécessaire pour supprimer un objet.

Pour identifier les verrous, utilisez la syntaxe suivante :

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

Désactivez les verrous à l’aide de PG_TERMINATE_BACKEND. Pour plus d’informations sur la façon de détecter et de débloquer les verrous dans Amazon Redshift, consultez Comment détecter et désactiver les verrous dans Amazon Redshift ?