Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2

Troels, Others,

> Generally: Nice. But have you considered if the INFORMATION_SCHEMA could
> be used? Unfortunately, the INFORMATION_SCHEMA currently has a major
> problem in its usefulness in PostgreSQL:
> http://troels.arvin.dk/db/rdbms/#cli-list_of_tables-postgresql-gotchas

Actually, I did. However, the format and columns of INFORMATION_SCHEMA are
defined by the SQL Standard, which will not cover a lot of PostgreSQL objects
(such as custom types or operators) and covers a lot of others in rather
awkward form. For that matter, your own editorial points out that we should
really be UPPERCASEing all of the object names in information_schema, which
would be SQL-spec but not generally useful.

> This reminds me: It would be nice if it were somehow possible to determine
> when (if ever) statistics have been gathered for a given schema object.
> This needs changes to more than VIEWs, though.

Well, you can always query pg_stats.

> Do you propose that typemodifiers be one column? - If would prefer if it
> were several columns. And it would be useful if it were easy to determine
> if a column is
>  - solely - or part of - a uniqueness constraint
>  - solely - or part of - a foreign key (pointing where?)
>  - if it is subject to a (set of) CHECK constraints

Yeah, I gave this some thought. The problem as I see it is that in the
future we may have additional types of typemodifiers which aren't covered,
and I don't want to get in the habit of adding more and more columns to the
view. However, that's not really an excuse; it might be better to:

pg_columns --> new view
schemaname
tablename
columnname
datatype
notnull
references (name which links pg_foreignkeys, or boolean?)
default
constraints (array, references pg_constraints)
othermodifiers (string of other column modifiers, for when such exist)
comment

In a way, though, it might be better for "references" to be a boolean column,
and users can query pg_foriegnkeys to find the exact reference.

====
BTW, People, I really don't see the point in prodiving a dual list -- that is,
a list of OIDs in addition to the list of names provided in the columns of
each view. The idea of these views is to keep the users *away* from
technical details like OIDs, which can and will change with the advancing
versions of PostgreSQL.

--
Josh Berkus
Aglio Database Solutions
San Francisco