[PostgreSQL-Hackers] pg_dump.c

Hello, Andrew.

You wrote:


AD> In the "refactoring Large C files" discussion one of the biggest files
AD> Bruce mentioned is pg_dump.c. There has been discussion in the past of
AD> turning lots of the knowledge currently embedded in this file into a
AD> library, which would make it available to other clients (e.g. psql).

+1
It would be great to have library with such functionality!

AD> I'm
AD> not sure what a reasonable API for that would look like, though. Does
AD> anyone have any ideas?




AD> cheers

AD> andrew

Here's a sketch.

In essence, libpgdump should have the following areas of functionality:

- Discover the user-defined objects in the database.
- Tag each as pre-data, data, and post-data.
- Make available the dependency graph of the user-defined objects in the database.
- Enable the mechanical selection of subgraphs which may or may not be connected.
- Discover parallelization capability, if available.
- Dump requested objects of an arbitrary subset of the database,
optionally using such capability.

Then there's questions of scope, which I'm straddling the fence about.
Should there be separate libraries to transform and restore?

A thing I'd really like to have in a libdump would be to have the
RDBMS-specific parts as loadable modules, but that, too, could be way
out of scope for this.

Cheers,
David.

In the first place, this isn't an API, it's a description of
functionality. A C library's API is expressed in its header files.

Also, I think you have seriously misunderstood the intended scope of the
library. Dumping and restoring, parallelization, and so on are not in
the scope I was thinking of. I think those are very properly the
property of pg_dump.c and friends. The only part I was thinking of
moving to a library was the discovery part, which is in fact a very
large part of pg_dump.c.

One example of what I'd like to provide is something this:

char * pg_get_create_sql(PGconn *conn, object oid, catalog_class
oid, pretty boolean);

Which would give you the sql to create an object, optionally pretty
printing it.

Another is:

char * pg_get_select(PGconn *conn, table_or_view oid, pretty
boolean, alias *char );

which would generate a select statement for all the fields in a given
table, with an optional alias prefix.

For the purposes of pg_dump, perhaps we'd want to move all the getFoo()
functions in pg_dump.c into the library, along with a couple of bits
from common.c like getSchemaData().

(Kinda thinking out loud here.)

cheers

andrew

I think the major problem with creating a decent API here is that
"the SQL to create an object" is only a small part ... almost a trivial
part ... of what pg_dump needs to know about it. It's also aware of
ownership, permissions, schema membership, dependencies, etc etc.
I'm not sure about a reasonable representation for all that.

In particular, I think that discovering a safe dump order for a selected
set of objects is a pretty key portion of pg_dump's functionality.
Do we really want to assume that that needn't be included in a
hypothetical library?

Other issues include:

* pg_dump's habit of assuming that the SQL is being generated to work
with a current server as target, even when dumping from a much older
server. It's not clear to me that other clients for a library would
want that behavior ... but catering to multiple output versions would
kick the complexity up by an order of magnitude.

* a lot of other peculiar things that pg_dump does in the name of
backwards compatibility or robustness of the output script, which again
aren't necessarily useful for other purposes. An example here is the
choice to treat tablespace of a table as a separate property that's
not specified in the base CREATE TABLE command, so that the script
doesn't fail completely if the target database hasn't got such a
tablespace.

* performance. Getting the data retail per-object, as the above API
implies, would utterly suck. You have to think a little more carefully
about the integration between the discovery phase and the output phase,
as in there has to be a good deal of it.

regards, tom lane

Maybe. Who else would need it?

Good point. Maybe what we need to think about instead is adding some
backend functions to do the sort of things I want. That would avoid
version issues and have the advantage that it would be available to all
clients, as well as avoiding possible performance issues you mention.



cheers

andrew

For whatever it is worth, the "SHOW CREATE TABLE" command in MySQL is
well loved. Having the functionality to generate SQL in the server can
be very nice.

A good start might be to merge together more of pg_dump and pg_dumpall
than is presently the case.