Shulou(Shulou.com)06/01 Report--

Today, developers need to delete an archive table of AWS Redshift, but the following error occurred in direct drop. It was found that other objects depend on this table and cannot be deleted directly, but the error report does not directly show what object depends on it. It may be constrained by views, foreign keys and so on.

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.

Looking at the table structure through\ d + auto_events_bk_20170223, it is found that there are no foreign key constraints of other tables, so exclude

Then use the\ dv+ command to view the view under schema:facts, but find that no view exists, but there may be other schema or db in view

So the question is, how do you find the view that is dependent on this table in the entire database?

Here AWS Redshift has provided us with a good method. Refer to the link: https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html

Create a view 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 = 'char char': "char" and c_c.relkind = 'vain char "

The query view finds the view associated with table auto_events_bk_20170223:

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

Finally, if you decide that these related view are useless, you can delete the table by ordering drop table auto_events_bk_20170223 cascade, including the view.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.