Re: pg_dump and pgpool

Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:
> I'm certainly willing to do the vast majority of the work. As Greg I
> think mentioned, maybe a fresh start using the information_schema would
> make sense as a sort of non-pg specific backup tool or something.

This is a dead end.

First, the information_schema only shows you the intersection between
what Postgres can do and what SQL can do; that leaves out way too much
to make a tool that anyone will want to use in practice. For instance,
you can't determine which columns are SERIAL columns. Worse, there are
cases where you can't tell what's going on from the information_schema
views because the views are designed around assumptions that don't hold,
such as constraint names being unique schema-wide rather than just
table-wide.

Second, there are protection problems. There are numerous cases in
which the information_schema views will show info only to the owner of
an object, and not to anyone else, not even superusers. This may be a
bug, or it may not be ... I'm not convinced whether the SQL spec
requires the views to work that way. But it will certainly cripple the
usefulness of a dump tool if even running it as superuser doesn't ensure
you get everything.

Third, as a means for avoiding any dependency on OIDs, this does not
work. Check out the "specific_name" columns of some of the views.

regards, tom lane