Redshift drop有依赖关系的表

今天开发需要删除AWS Redshift的一个归档表，但是直接drop发生了如下报错，发现有其他对象依赖这个表，导致无法直接删除，但是报错没有直接显示是什么对象依赖它，可能是有视图，外键等约束

analyticsprod=# DROP TABLE IF EXISTS facts.auto_events_bk_20170223;

ERROR: cannot drop table auto_events_bk_20170223 because other objects depend on it

HINT: Use DROP ... CASCADE to drop the dependent objects too.

通过\d+ auto_events_bk_20170223 查看表结构，发现并没有其他表的外键约束，所以排除；

然后通过\dv+命令在schema：facts下查看视图，但是发现没有任何view存在，但是view可能存在其他schema或db,

那么问题来了,如何在整个数据库中找到与这个表有依赖的的view?

这里AWS Redshift已经给我们提供了一个好的方法,参考链接：https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html

创建一个视图find_depend:

create view find_depend asselect distinct c_p.oid as tbloid,n_p.nspname as schemaname, c_p.relname as name,n_c.nspname as refbyschemaname, c_c.relname as refbyname,c_c.oid as viewoidfrom pg_catalog.pg_class c_pjoin pg_catalog.pg_depend d_pon c_p.relfilenode = d_p.refobjidjoin pg_catalog.pg_depend d_con d_p.objid = d_c.objidjoin pg_catalog.pg_class c_con d_c.refobjid = c_c.relfilenodeleft outer join pg_namespace n_pon c_p.relnamespace = n_p.oidleft outer join pg_namespace n_con c_c.relnamespace = n_c.oidwhere d_c.deptype = 'i'::"char"and c_c.relkind = 'v'::"char";

查询视图找到与表auto_events_bk_20170223相关的view:

analyticsprod=# select * from facts.find_depend where schemaname='facts' and name not in ('find_depend') order by name; tbloid | schemaname |          name           | refbyschemaname |  refbyname  | viewoid--------+------------+-------------------------+-----------------+-------------+--------- 879566 | facts      | auto_events_bk_20170223 | public          | map_routes2 |  965969 879566 | facts      | auto_events_bk_20170223 | public          | map_routes  |  966155 879566 | facts      | auto_events_bk_20170223 | public          | map_edges   | 1019697(3 rows)analyticsprod=# set search_path to public;analyticsprod=# \dv               List of relations schema |         name          | type | owner--------+-----------------------+------+------- public | map_edges             | view | fengw public | map_edges_group       | view | fengw public | map_route_edges_group | view | fengw public | map_routes            | view | fengw public | map_routes2           | view | fengw public | map_routes_group      | view | fengw(6 rows)

最后如果判断这些相关的view没有用的话，可以通过命令drop table auto_events_bk_20170223 cascade删除表，包括视图一同删除。