Re: Views, views, views! (long)

INFORMATION_SCHEMA is what should be exposed to the end-users of
PostgreSQL.

Pg_schema (for lack of a better name for internal metadata) can be
useful as well for all sorts of special purposes. Probably,
INFORMATION_SCHEMA (as designed by the SQL Standards committee) does not
need to worry about table inheritance, for example.

The only danger I see is Pg_schema forging ahead and INFORMATION_SCHEMA
lacking some crucial piece of data.

Pg_schema should NOT be the end-user database catalog. I would even
advocate hiding it from anything but internal processes. Anything that
is totally crucial as a piece of functionality should be added to
INFORMATION_SCHEMA as a kind of enhancement, if it cannot be stored in
that schema as-is.

I suspect that:
1. There is not a whole lot of stuff that cannot be directly stored in
the INFORMATION_SCHEMA location without modifying it.
2. Almost all of the information that cannot fit will be useful to
other database systems as well, and should be suggested to the ANSI/ISO
committee. Since INFORMATION_SCHEMA is a very new idea (only two
adopters that I know of so far) I expect it will need to grow and
PostgreSQL could be one of the contributors. Whether the enhancements
are accepted or not, it would be good to at least attempt to get them
noticed.
3. Of the fragment that does not fall under 1 and 2, it should be a
carefully documented extension that lives in the same place
(INFORMATION_SCHEMA) so that we know where to look to find it.

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of elein
> Sent: Thursday, May 05, 2005 4:55 PM
> To: PostgreSQL-development
> Cc: Josh Berkus; Peter Eisentraut
> Subject: Re: [HACKERS] Views, views, views! (long)
>
> There are several things to address in the flurry of messages.
>
> The first thing that the qa/support team did at Illustra was to
> write a series of views on the system catalog. It was the most
> pressing thing to do. Every single db engineer probably has one
> or two or seven views to look at objects in PostgreSQL.
>
> I have seen one decent schema diagram of
> the system catalogs and have despaired at creating one myself even
> though I know the catalogs pretty well. Using the -E option
> on psql does not even help much anymore because the queries are
> so complex they've been broken into smaller queries even though
> one should suffice. The \ options have improved but some, like
> df are still useless. Asking users to create their own views
> is not very user friendly.
>
> I have tried and modestly succeeded to steer the newview project to
> answer people's questions. This has also been the point of the
> system catalog views I have published on General Bits. These have
> been views I've written for my own toolset.
>
> What are my constraints?
> What functions operate on a particular data type?
> What are the functions underlying this aggregate?
> What foreign keys link to table x?
>
> These are just small samples which may or may not be answered
> by our set of views so far. There are lots of questions like
> this that are difficult for most people to get the answers to,
> yet the answers are crucial to managing the design of their
> database.
>
> The Information Schema is very good. But it is a set of views
> For All Databases and does not answer PostgreSQL specific questions.
> A set of views specifically for PostgreSQL, without the restraint of
> highly funded committees, is what is needed. Still we should, as
> Peter suggested, borrow terminology and techniques where ever possible
> and maintain some kind of consistency.
>
> There is no question in my mind that a simple SQL interface to
> answering the most pressing questions regarding the database
> is necessary. They may have to be updated with the system
> catalogs, but as Tom said, no one changes the catalogs unless it
> is absolutely necessary.
>
> Usability is a feature we don't put enough emphasis on, ever.
> New system views would help people be more productive with
> PostgreSQL, enable new interfaces to have better packaged information
> and help all of the people required to support a PostgreSQL database.
>
> Elein
>
> =============================================================
> elein(at)varlena(dot)com Varlena, LLC www.varlena.com
> (510)655-2584(o) (510)543-6079(c)
> PostgreSQL Consulting, Support & Training
>
> PostgreSQL General Bits http://www.varlena.com/GeneralBits/
> ==============================================================
> I have always depended on the [QA] of strangers.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org