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?as
select?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?viewoid
from?pg_catalog.pg_class?c_p
join?pg_catalog.pg_depend?d_p
on?c_p.relfilenode?=?d_p.refobjid
join?pg_catalog.pg_depend?d_c
on?d_p.objid?=?d_c.objid
join?pg_catalog.pg_class?c_c
on?d_c.refobjid?=?c_c.relfilenode
left?outer?join?pg_namespace?n_p
on?c_p.relnamespace?=?n_p.oid
left?outer?join?pg_namespace?n_c
on?c_c.relnamespace?=?n_c.oid
where?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删除表，包括视图一同删除。