Re: pg_dump, libdump, dump API, & backend again

At 03:47 22/07/00 +0200, Peter Eisentraut wrote:
>Philip Warner writes:
>
>> 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?
>
>People do ask all the time how they can reproduce psql's, say, "\d" output
>for themselves. Notice that they don't ask how to reproduce pg_dump's
>output, pg_dump does that just fine, but people want to get at the
>metadata and process it for themselves. I imagine people have web tools
>for modelling their databases, etc. or change tables on the fly. I'd say
>there is a big market for this sort of thing, and the current state of
>affairs that every system catalog change is in some way liable to break
>all these applications is a non-negligible problem.

This is very true; and to satisfy this issue, I think you are right in
suggesting using (a subset of) the Info schemas. It's possibly even
worthwhile creating something like:

Create View TABLES(TABLE_NAME) as Select whatever...
(this can be identical to pg_tables, for example)

and

Create View COLUMNS(TABLE_NAME, COLUMN_NAME, DATA_TYPE,...) as
Select...whatever

to satisfy some of these needs; ie. we don't even need to provide all the
standard columns, so long as we only provide ones that are in the standard,
and we also obey the visibility rules from the standard.

Just these two would, I think, provide the \d function in psql. Not that
I'm volunteering; pg_dump and 'insert/update...returning' are going to take
up enough time right now...

>It is therefore my opinion that making SQL dumps widely available is,
>while certainly useful, not the real core of the problem. Getting to the
>data that underlies the dump is. Anything that does that would be a step
>ahead.

Yes & no. There are two quite different problems, and the solution to one
does not provide a solution to the other, it just provides a layer on which
the other can be built:

1. How can we provide a stable interface for examining DB definitions that
works across releases.

You have answered this (to some extent) by suggesting Info schemas. Note
that these will still not work for non-standard features, but we could use
pg_* views for pg-specific structures. Perhaps whoever works on pgaccess
might have a good definition of the minimum requirements from an initial
implementation of info schemas?

It *might* be worth naming the guaranteed public views differently from the
normal pg_* tables; maybe pg_std_rules, then we can tell users that any
table/view that starts with pg_std_* will exist across versions, and always
at least have the columns present in the current release.

2. How can we provide a function in psql that displays definitions of all
things that a user can create with SQL?

This is the problem I set out to address, which is why I looked at using
pg_dump code. Which then led me to the philosophical question of what
should be responsible for 'decompiling' the internal representation of the
metadata. My feeling is that the backend 'compiles' SQL into metadata, so
it should also decompile it.

>Some things to consider:
>
>* A backend-side implementation: we already have a couple of views to that
>effect (pg_rules, pg_views). You can always add more.

I'll have a look at implementing something using views & functions as per
pg_views.

>* A C API: How many people do use C? How to propagate this to other
>interfaces? Are we really up to creating and maintaining another
>hand-crafted API?

I don't really want to be left maintaining such a thing and I'm sure nobody
else does, but as someone (Tom?) suggested, I'll have to develop a private
API to do the work, so making it public might be a good idea...maybe.

>
>> 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?
>
>If that's your concern that you can just link a couple of pg_dump's object
>files into psql. You could perhaps create a de facto API within pg_dump,
>which would also go a long way in cleaning up pg_dump's source code.

This is what I want to do as a minimum. I don't really want to create
pg_get_tablesql, pg_get_constraintsql, pg_get_typesql, pg_get_viewsql etc,
but maybe that's the best option after all. Maybe to avoid conflicts with
pg_get_ruledef etc, I should use names formatted as pg_dump_sql_*, but the
idea is the same.

>(When I was wheeling and dealing in psql last fall I was faced with the
>same questions regarding the table printing facilities. People are not
>really interested in getting pretty printed ASCII versions of their query
>results; their web browser or widget set can do that much better. And even
>those that do wouldn't like the particular formatting choices anyway. So I
>withdrew psql's printing functions from the public. You can still link in
>print.o into your program if you want to, but please don't complain if I
>change the spacing and your application breaks.)

It's a bit different with definitions; AFAICT there is no way of (simply)
getting the definition of a constraint while in psql. *I* want to be able
to do this, and personal need is a pretty good motivator...

So, perhaps I need to implement the metadata dumping code from pg_dump as a
set of functions that can be added to the backend and which are called in
one or more view definitions which pg_dump can use.

----------------------------------------------------------------
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 |/