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

Dann,

> 1.  There is not a whole lot of stuff that cannot be directly stored in
> the INFORMATION_SCHEMA location without modifying it.

See Andrew's post. There is a whole lot of stuff not covered by I_S in a way
that is useful to PGSQL users. Also this would require making
information_schema part of the default user path.

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

I like that idea, actually.

However, there's not a whelk's chance in a supernova of our suggestions
getting read, let alone accepted. But we could make the effort. You
volunteering?

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

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, May 05, 2005 at 05:24:43PM -0700, Dann Corbit wrote:
> 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.

On close examination of PostgreSQL's extensibility capabilities will
show you that this is actually not the case.

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

The SQL committee is s l o w l y catching up to the OR capability that
was designed into Postgres back in '86. As other databases finally
get around to adopting similar models the items are added to the standards.
In many ways, SQL2003 is finally catching up with some of what
Postgres was designed to do. We already have been influencing the
committee for years indirectly.

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

This is the bulk of what we are working on. Some is duplicated
work with the information schema but it usually has more information
or context.

--elein
elein(at)varlena(dot)com

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

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> 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.

[ raised eyebrow... ] INFORMATION_SCHEMA is in the SQL92 spec.
If only two systems have adopted it in the last 13 years, it's
a failure and the SQL committee is unlikely to want to spend any
time on it. In any case, they just issued a spec and so there is
very unlikely to be another update for another five or so years.

regards, tom lane

>>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.
>
>
> [ raised eyebrow... ] INFORMATION_SCHEMA is in the SQL92 spec.
> If only two systems have adopted it in the last 13 years, it's
> a failure and the SQL committee is unlikely to want to spend any
> time on it. In any case, they just issued a spec and so there is
> very unlikely to be another update for another five or so years.

INFORMATION_SCHEMA is also in MySQL 5, so that makes 3 :D

Chris