pg_dump, libdump, dump API, & backend again - Mailing list pgsql-hackers

Executive summary: 

I have discarded the idea of (partially) implementing Info Schemas as part
of a pg_dump/psql enhancement, and returned to my original plan. You can
now skip to 'Questions', below.

----------- Discussion --------
Having now waded through the SQL standards rather more than is good for my
sanity, I have concluded that even if I could implement the entire info
schema system in PG, I would still have to write SQL dumping code that
would look quite similar to the SQL dumping code in pg_dump. What is more,
it would not support rules, or any non-standard features of PG (at least I
think the latter are true).

Being therefore forced to look at the intersection of the info schemas and
PG, I would probably end up just supporting COLUMNS, VIEWS, & TABLES. And
even with this work complete, I would have to revise pg_dump to use it, by
which time I would have sucessfully implemented the current functionality
of pg_dump.

As a result I am inclined back to the more modest goal of making the code
to dump individual database items more generally available. Perhaps then at
some later stage we can modify the internals to use info schemas, when they
are implemented. The way I see it at the moment, what I plan to implement
is still a layer above either info schemas or the pg_* tables.

As to pgaccess, if an API layer is needed for ease of update & portability,
I would be interested to know if anyone had considered the practicality of
using the ODBC interface? ODBC allows interrogation of metadata, execution
of DDL statements, execution of queries etc. It seems to me that if
pgaccess were implemented on top of ODBC, the authors would have a very
useful general purpose tool. But I have not looked at what special features
it has, so I have no idea how practical the suggestion is.

----------- Questions --------

1. Can anyone think of an existing utility or library in PG that would
profit from a more convenient interface for querying metadata than is
available from ODBC or the pg_* tables? If so, can you give some details &
pointers. [This will be useful in modifying any resulting API].

2. Can anybody suggest the most appropriate (polite) location for any SQL
extraction code (library? backend? external function?). Some people have
commented on it being a bad idea that it goes into the backend, and I have
observed that it would be nice to extract valid SQL definitions via SQL,
thus making the code available to most users with minimal effort. Currently
I would expect to implement an API and (ideally) an SQL interface. The SQL
side of things would probably just call the API, but the details need to be
worked out.

3. My current plan/idea/suggestion is to implement an API that, given an
object type (eg. 'table'), will return the most basic definition for the
object and a list of related objects. This API could be used by both psql
and pg_dump to create SQL for display & backup purposes.

4. Even if I achieve nothing more than breaking 'libdump' out of pg_dump,
and adding a \D command to psql, does anybody object to this as an objective? 


Any comments and suggestions would be greatly appreciated...


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/