Re: Views, views, views! (long)

On 2005-05-05, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Josh Berkus wrote:
>> Either the information schema adheres to
>> the spec, or it only covers 25% of PostgreSQL objects. There isn't
>> a 3rd alternative. I'm fine with merging this with the
>> information_schema (some of these views are derived from the same
>> code) but it's either/or.
>
> I can think of a couple of ways offhand about how the information schema
> could be extended without breaking the SQL standard. You could just
> add columns where needed.

How are you going to add a column to a view defined in the standard
without risking conflict with future versions of that standard?

How are you going to deal with the fact that the visibility rules for
information_schema are sometimes completely wrong? The primary reason
why I started writing catalog views for my own application usage was
exactly _because_ those rules were wrong - I needed, for example, to be
able to see which were the primary key columns for accessible tables (and
all tables for the superuser), whereas information_schema limits the
constraint views to tables owned by the current user.

> Or you could add tables that are joined to
> the standard tables and contain the extra information.

Still the visibility problem.

> Or you could
> create a "information_schema_2" that contains a copy of the original
> information schema with the extra information added somewhere, so users
> can easily switch back and forth.

"easily"? information_schema is not something you ever want to put in
your search path, so having an "information_schema_2" would be no more
convenient for users than our proposal.

> If you look closer, there isn't really all that much that cannot be
> gotten from the information schema. Figuring out exactly what that is
> might be instructive before deciding how to go forward.

The first obvious thing is that the information schema tells you nothing
at all that relates to _implementation_ rather than _semantics_.
For example, there is nothing at all in it about indexes, since those
are only an implementation detail. Nor does it tell you anything about
tablespaces, the sizes of tables, aggregate functions, casts, databases,
rules, sequences, or table inheritance. It tells you almost nothing about
user-defined data types. It doesn't allow lookups based on OIDs that you
received via the wire protocol (which exposes table and type OIDs quite a
lot).

information_schema is also known to be broken as regards looking
up some constraints, thanks to the lack of schema-wide uniqueness of
constraint names. In fact, it's possible to create foreign-key constraints
that don't appear in information_schema at all, or which appear multiple
times.

information_schema also scales poorly with the size of the schema, since
the use of standardised types interferes with the use of the system
indexes. We deliberately decided to retain the "name" type for object
names in our views in order to avoid this.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services