Tom,
> Do you still have the original database available? The obvious route to
> finding the problem is to watch pg_dump in action and see why it misses
> that view. How do you feel about letting someone else have access to
> your system to do this? (Or get out a debugger and do it yourself...)
OK, more specifics: The problem only seems to happen with views and functi=
ons=20
that are part of unresolved dependancies. e.g., here's how I produced the=
=20
problem:
1. Edited the view lock_users, on which 6 other views depended.=20
2. This broke the 6 other views.
3. Tried to re-load the other views and had problems finding them all.=20=
=20
Decided to dump and restore to resolve the dependancies.
4. Did a text pg_dump (not binary).
5. Dropped database and reloaded. Discovered that lock_users was not loade=
d;=20
in fact, it wasn't part of the pg_dump file at all.
6. Hand-edited the pg_dump file (yay Joe text editor!) and re-inserted the=
=20
lock_users view after its dependancies, but before the other views.
7. Re-loaded the database. After a couple of tries, it worked.
As the broken dependancy problem no longer exists, futher pg_dumps now back=
up=20
lock_users correctly.=20
At a blind guess, I would hypothesize that the problem occurrs becuase pg_d=
ump=20
is trying to backup stuff in correct dependancy order, but becuase of the=
=20
broken links gets confused and drops the object entirely. However, this=
=20
becomes a circular problem for Postgres db developers, as drop and restore =
is=20
one of the primary ways of fixing broken dependancy chains.
I will see if I can re-produce this on a sample database. lock_users is a=
=20
view with 6 view dependancies, and itself depends on 2 tables and a custom=
=20
function. So I can see how this would be a destruction test.
I do have the Postgresql log files for the last few days, but my mastery of=
=20
command-line text parsing is not sufficient to find the relevant section of=
=20
the log.=20=20
--=20
-Josh Berkus
Aglio Database Solutions
San Francisco
