跟踪 PostgreSQL 数据库中的依赖关系
在复杂的数据库中，Postgres 拒绝更改视图，因为许多规则和许多其他视图都依赖于它。
我可以使用 DROP CASCADE 删除视图及其所有依赖项，但之后如何重新创建这些视图？这并不容易，因为即使我知道 V 和 U 都取决于我想要更改的视图，我也不能随心所欲地创建它们。如果视图 V 依赖于 U，我必须先创建 V，然后再创建 U。
我如何跟踪哪些其他视图和规则依赖于某个视图，以便我能够以最简单的方式更改它？我还想生成一个脚本，以便每个人都能够使用我的脚本更改视图，有什么简单的方法可以生成它吗？
In a complex database, Postgres refuse to alter a view since many rules, and many other views depends on it.
I can drop the view with all its dependencies with DROP CASCADE, but how will I recreate these views afterwards? It's not so easy, since even if I know that both V and U depends on the view I want to alter I can't just create them however I want. If view V depends on U, I must create V first and only then create U.
How can I trace which other views and rules depend on a certain view, so that I'll be able to alter it in the easiest way? I also want to generate a script so that everyone would be able to alter the view with my script, any easy way to generate that?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
检查系统表 pg_depend 以获取依赖项。
Check the system table pg_depend to get the dependencies.
我不知道这对你有多有效，但最省力的方法可能是 DROP CASCADE 并使用 apgdiff 重新创建删除的视图：
pg_dump -s -f new_schema.sqlapgdiff new_schema.sql old_schema.sql > Restore.sql-- 创建一个脚本来恢复数据库中的旧模式。psql -f Restore.sql不幸的是，这意味着您的数据库将在一段时间内丢失这些视图，因此您可能必须将其脱机以进行维护。
I don't know how well this will work for you, but the least laborous way would probably be DROP CASCADE and use apgdiff to re-create the dropped views:
pg_dump -s -f old_schema.sqlto create a schema dump -- which includes all views etc.DROP VIEW x CASCADEpg_dump -s -f new_schema.sqlapgdiff new_schema.sql old_schema.sql > restore.sql-- create a script to restore the old schema in the database.psql -f restore.sqlUnfortunately this means that your database will be missing these views for a window of time, so you probably have to take it offline for maintenance.