Re: Views, views, views! (long) - Mailing list pgsql-hackers
From | Andrew - Supernews |
---|---|
Subject | Re: Views, views, views! (long) |
Date | |
Msg-id | slrnd7kvah.2ep3.andrew+nonews@trinity.supernews.net Whole thread Raw |
In response to | Views, views, views! (long) (Josh Berkus <josh@agliodbs.com>) |
List | pgsql-hackers |
On 2005-05-05, Peter Eisentraut <peter_e@gmx.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
pgsql-hackers by date: