Redshift - How to display dependencies that CASCADE will delete?

Question

When deleting table in Redshift I get an error message :

Amazon Invalid operation: cannot drop table rawdata.bss_edw_customer_account because other objects depend on it;

I don't want to use CASCADE because I don't know which other tables/views it will kill. How do I find out which tables or views are dependent on the table I want to drop? I want to make sure I don't step on somebody else's work.

Thanks

I don't think you can "kill" other people's work because the `DROP...CASCADE` will stop if there are dependencies. Since Amazon Redshift is based on PostgreSQL, this might work: [Find dependent objects for a table or view](https://stackoverflow.com/a/11773226/174777) — John Rotenstein, Jun 11 '20 at 23:27

score 4 · Answer 1 · answered Jun 12 '20 at 05:10

Redshift Admin Views for object [1] and constraint [2] dependencies can help in identifying dependent objects. You can also create the find_depend view as described in the DROP table documentation [3] to view any dependencies.

I also found that these views may not always list materialized views as dependent objects. If you have created any MV in the past, then you might want to check for dependencies using the view DDLs. The following query could help:

select schemaname, viewname from pg_views where schemaname not like 'pg_catalog' and schemaname not like 'information_schema' and definition like '%<tablename>%';

[1] https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_object_dependency.sql

[2] https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_constraint_dependency.sql

[3] https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html

score 0 · Answer 2 · answered Jul 28 '21 at 09:07

If you need a more general overview of the (materialized) view dependencies in a schema you can use the following snippit. Just replace schema_name with the name of the schema. If you need to display dependencies across schemas this should be easily doable by adapting the regex or by doing a union.

create view admin.v_view_dependencies_fixed as (
  with h1 as (
      select generate_series as i
      from generate_series(1, 100) -- we can use this since the query only touches the leader node
  ),
  h2 as (
      select schemaname                                         as dependent_schema,
             viewname                                           as dependent_view,
             schemaname || '.' || viewname                      as dependent_full,
             regexp_substr(definition, 'schema_name\\.\\w+', 1, i) as dependency
      from pg_views
               cross join h1
      where schemaname = 'schema_name'
        and dependency is not null
        and dependency != ''
  )
  select distinct
         dependent_full,
         dependent_schema,
         dependent_view,
         dependency as source_full,
         split_part(dependency, '.', 1) as source_schema,
         split_part(dependency, '.', 2) as source_object
  from h2
  where dependent_full != source_full
);

Redshift - How to display dependencies that CASCADE will delete?

2 Answers2