Assuming a particular schema is standalone, i.e. does not depend
on any other external schema, I'd like to know the order in which to
(re)populate tables from data coming from (an existing) custom storage.
When the schema's table have foreign key relationships,
"parent" tables must be populated before children tables
referencing the parent ones.
I've looked at pg_depend, and there doesn't seem to be any
"direct relationships" between parent and child tables, i.e. I found
no rows with the parent and child tables as refobjid and objid of the same row.
One must apparently go through a pg_constraint dependency first.
But even then, I'm guessing I need a CTE to do the topological sort.
I've done topological sorts in C++, but not in a functional language like SQL.
Would anyone happen to have a query to returns that order for a schema?
A complication is that sometimes there are circular dependencies between
tables, which are "solved" by deferring one constraint to "break the cycle".
Would the above query handle that?
I guess any tool that restores a "backup" has the same problem, no?
Or are those tools somehow bypassing that issue?
Perhaps by disabling constraints when reloading the data, then re-enabling them?
Thanks for any insights. --DD