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