CODEWITHSUNDEEP
postgresqlview
Elazar Leibovich
Elazar Leibovich

Reputation: 33593

Tracing dependencies in PostgreSQL database

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?

Upvotes: 0

Views: 1106

Answers (2)

intgr
intgr

Reputation: 20466

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:

  1. Run pg_dump -s -f old_schema.sql to create a schema dump -- which includes all views etc.
  2. Run DROP VIEW x CASCADE
  3. Run pg_dump -s -f new_schema.sql
  4. Run apgdiff new_schema.sql old_schema.sql > restore.sql -- create a script to restore the old schema in the database.
  5. psql -f restore.sql

Unfortunately 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.

Upvotes: 0

Frank Heikens
Frank Heikens

Reputation: 127106

Check the system table pg_depend to get the dependencies.

Upvotes: 1

Related Questions