Postgres constraints order of writing to and deleting from tables

I am working in a postgresQL database that has 200+ plus开发者_JAVA技巧 tables, some with many (15+) constraints that are references to other tables. I was able to list all of the constraints from the pg_constraints tables.

I am trying to map the dependencies and tables referenced by each table in order to be able to manage writing to the tables from a web application. If a table has a dependency, I need to be sure the dependent tables are written to before that table, and if the table is referenced by another table, that the given table has the necessary rows before the table that references it is queried. How can I get a list of the tables in the order they would need to be written to,and in reverse, the order that would need to be followed to delete from multiple tables?

---

Two pieces of advice:

• There can be circular dependencies, especially with triggers and rules. In a case like yours not unlikely. That's perfectly acceptable design, programming logic in triggers / rules / foreign keys etc. has to take care that you don't enter infinite loops. (The RDBMS can detect them when they happen.) But your quest to map out dependencies on a relational level may lead you in circles.

• pgAdmin (current version 1.14) has dedicated tabs for "Dependencies" and "Dependents" in the main interface. Click on any object and get a full list. This could be rather helpful in your case - may even be all you need. You can also turn on statement-logging of your database and read how pgAdmin retrieves that information in your logs. Set log_statement = all for that purpose.
  _{Don't forget to reset log_statement, or your logfiles will get huge.}

---

You can also try to change constraints to deferred.