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

At 18:06 24/07/00 +0200, Peter Eisentraut wrote:
>Philip Warner writes:
>
>> 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.
>
>There is certainly an intellectual merit in making the backend spit out
>the SQL it took in. But, just for kicks, there is also an intellectual
>merit in *not* doing it: Whatever you dump represents the state of a
>database, but SQL only provides a method for modifying the state, not for
>representing it. The backend has, by and large, no idea how it got to the
>current state. Which is the philosophical counter-argument to the notion
>that the backend should also provide the inverse operation to its query
>parser -- there is no inverse.

No, but any DB state must be representable, and the DB may be the best
arbiter of how to represent it. If psql is used to transform the database
from state 'A' to state 'B', then the database should be able to describe
state 'B', ISTM.

I don't know if PG allows table columns to depend on columns in other
tables, but if they can then dumping a valid schema becomes almost
impossible. [Dec/Rdb allows 'computed by' columns which can contain
arithmetic operations on columns in the current table, or even 'select..'
statements. This means that defining a pair of cross-referenced tables can
be a multi-step process].

However, the fact the problem is hard/impossible, is not really an argument
for placing it in a FE or BE, AFAICT...

>Getting of that high horse, the practical problems I see is that there are
>about a thousand parameters which would affect how you dump the SQL:
>
>* indentation

This would be picked arbitrarily.

>* capitalization
>* quotes

These are related. pg_dump already has to handle this, and the client
should be able to specify how quotes are handled (needs to be in the
API/SQL/View).

>* standards compliance (to migrate to a different RDBMS)

This is currently a problem in pg_dump, and could be left out, I think. To
dump valid SQL-nn format output, we probably want to wqait for info
schemas. Also, we *can't* produce standard SQL for some PG features, I think.

>* order

This is an issue for the client. I am not proposing a
'Dump_The_Entire_Datbase' call; just a
'Dump_One_Thing_And_A_List_Of_Dependencies' call.

>* view as view, view as rule?

Also an issue with pg_dump currently; I'd prefer to see it as a view.

>* with/without psql specific stuff (\connect ...)

Another issue for the client.

>* How to resolve circular dependencies?

Issue for the client.

>These kinds of decisions are better off with a client application, ISTM.

Most of them would be; the only things handled at the backend would be
'formatting', quotes (based on client request), and SQL generation. The
client has to put the SQL together in a meaningful way including \connect
statements and handling dependencies.

I agree that this does not sit well with the "DB must be able to dump it's
state" argument; perhaps I need to just clean up pg_dump a little, link it
into psql, then get on with something I actually want to do...(ie.
insert/update...returning).

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