Re: Should PG backend know how to represent metadata? - Mailing list pgsql-hackers

At 00:29 18/07/00 +0200, Peter Eisentraut wrote:
>Philip Warner writes:
>
>> I would have thought that pgaccess would still need to display table
>> definitions in SQL, but I have not looked at it closely enough. At the
>> lowest level I suspect pgaccess will always have to use direct access to
>> pg_* tables.
>
>I thought it was your intention to get rid of this fact. We should
>certainly be thinking in terms of all client applications.

I agree, but it seems we have a gain if we can get psql-compliant sql out
of a single library. I'm quite open to making a more general
implementation, but I'd need to know what pgaccess needs over and above a
psql-compliant SQL output.

The reason I think pgaccess will probably have to continue with internal
knowledge is that it is a low level manager for the database; at the
simplest level, getting tables and their columns would be great, but it
probably also needs to know what the primary key is, and even understand
constraints (at least non-NULL ones). This is a very different problem, and
definitely related to the SQL information schemas.

Perhaps what I do here can be structured to be useful to whoever implements
information schemas when they come along.


>[Information Schema]
>> This sounds good; where are they defined in the spec?
>
>Part 2, chapter 20, if that helps you. It's not really possible to
>implement all of these at this point because many are quite complex and
>depend on outer joins and other fancy features, or contain
>meta-information on features that don't exist yet. Actually, we probably
>need the full-blown TOAST before some of these will fit at all.

I agree. At best we could implement things like COLUMNS, and even then the
various 'schema' columns would be meaningless (until schemas come along).


>
>> This is the sort of thing I'd like to see, but on a more general level:
>> 
>>     format_object('table', <oid>)
>> 
>> would return the base definition of the table.
>
>I'm not sure if we want to move the entire pg_dump functionality into the
>backend. For example, if someone wants to move SQL dumps to a
>not-quite-SQL or a much-more-SQL database and the format is slightly
>wrong, then there's no way to amend that short of patching the backend.
>Then we could as well have the backend returns pre-formatted output for
>psql.
>
>A human-oriented layer over the system catalogs (which are implementation
>oriented) could go a long way toward maximum flexibility.

You may be right, but being able to 'select' a table or field definition is
very appealing. Can it be made a little cleaner by being implemented as a
dynamically linked function (as per user defined functions). That would
seem to reduce the problem you have with releasing a new backend, at least.


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