Views, views, views! (long)

PG hackers,

AndrewSN, Jim Nasby, Elein and I have been working for the last couple of
months on a new set of system views for PostgreSQL.   (primarily Andrew, who
did the lion's share of the work and came up with many clever SQL
workarounds)  We'd like to include them in the 8.1 release, so we're going to
post most of the definitions for your feedback now.  Let me summarize:

Goals of the New System Views
---------------------------------------
1. To be easily human-readable, unlike the system tables.
2. To provide a consistent API to PostgreSQL object definitions which will
seldom (if ever) be changed, only added to.
3. To provide queryable definitions for all PostgreSQL objects.

In more detail:
1. The current system tables are designed around performance and code
requirements, and as such are optimized for code access, not
comprehensability.  Column names are obscure, special system data types are
used, and everything is OIDs and typids.  This is perfect for our code, but
too many user-space applications are using these tables for comfort.  Our
first system views (pg_tables, for example) only went halfway in providing a
user-friendly interface.  So the new system views have the following
requirements:
        a) all view and column names are as explicit and as readable as         possible (e.g. "type_schema_name", not
"typnsname")
        b) OIDs, typids, and other system codes are avoided wherever possible        in preference to full object names
        c) In most places, "system" objects are segregated from "user"
objects,        e.g. pg_user_indexes

2. One of the issues with user applications querying the system tables is that
they can be subject to significant changes from version to version.   This
has kept the pgAdmin and phpPgAdmin teams busy since 7.2, and means that GUI
tools which fall out of maintenance (like Xpg) soon stop working.  This is
easily remedied through a set of system views which will remain consistent
regardless of changes in the underlying system tables.  This has the
beneficial effect of giving us more freedom to make changes to the system
tables.
      Further, we discovered when we proposed dropping the old system views
that once these views are created we're stuck with them for several years, if
not forever; people's not-easily-recoded tools rely on them.
        d) Columns may be added to the system views, but never dropped or         changed in incompatible ways.
 Likewise,views will be added but        not dropped or renamed. 
        e) Users and app developers should be actively encouraged to use the        system views rather than the system
tablesin the documentation. 
        f) Existing projects, features and add-ons, where appropriate,        should gradually be shifted to use the
systemviews to minimize        version maintenance. 

3. The new system views (unlike, for example, \d) are designed to be a SQL
interface to system objects.   This means that:
        g) All views are as normalized as possible, using child views rather       than arrays, and providing keys and
consistentjoin columns. 
        h) Each view or set of views provides all of the data required        to replicate the appropriate CREATE
statement.
        i) Column names are designed to be universal to a particular type of
data,         where this does not cause duplication.  For example,         pg_user_tables has "schema_name" rather than
"table_schema".         This was done to make joins easier (i.e. USING, NATURAL JOIN) 


Additional assumptions we worked with include:    j) all view names are plural in order to prevent conflict with       
systemtables. 
     k) no procedural languages are used, as we don't want to make PLs         mandatory.   Currently everything is SQL
(really!)and we may move        a few functions to a C library eventually.    l) internal functions required for the
systemviews are named using a        "_pg_" convention. 
        m) We will be offering a "back-patch" for 7.4 and 8.0 via pgFoundry.

What We Need From Hackers
--------------------------------------
(other than patch approval, that is)
As stated above, these system views, once incorporated into a pg distribution,
are likely to be with us *forever*.   As such, we really can't afford to do
major refactoring of the column names and structure once they're released.  
So it's really, really, important for everyone on hackers to look over the
definitions below and find stuff that we've missed or doesn't make any sense.  
Also, we'd like to know about 8.1 changes that affect these views.

There are two additional other questions to discuss that our team as not
settled:
I) Should the new views be part of /contrib before they become part of the
main source?
II) Should the new views be in their own schema?   This would make them easier
to manage for DBAs who want to restrict access or dump them, but would add a
second "system" schema to the template.

Information_Schema note
---------------------------------
Q: Why not just use information_schema?
A: Because the columns and layout of information_schema is strictly defined by
the SQL standard.  This prevents it from covering all PostgreSQL objects, or
from covering the existing objects adequately to replicate a CREATE
statement.  As examples, there is no "types" table in information_schema, and
the "constraints" table assumes that constraint names are universally unique
instead of table-unique as they are in PG.

The View Definitions
----------------------------------
The column definitions of the views are below.   Please examine them
carefully.  Currently, the following views are incomplete and thus not
included:
pg_functions
pg_function_parameters
pg_types

pg_acl_modes

    Column    | Type |
 -------------+------+-----------
  object_type | text |
  mode        | text |
  granted     | text |
  description | text |
 
pg_all_aggregates

         Column         |  Type   | Modifiers
 -----------------------+---------+-----------
  schema_name           | name    |
  aggregate_name        | name    |
  input_type_schema     | name    |
  input_type            | name    |
  output_type_schema    | name    |
  output_type           | name    |
  initial_value         | text    |
  trans_function_schema | name    |
  trans_function_name   | name    |
  final_function_schema | name    |
  final_function_name   | name    |
  is_system_aggregate   | boolean |
  owner                 | name    |

pg_user_aggregates

         Column         |  Type   | Modifiers
 -----------------------+---------+-----------
  schema_name           | name    |
  aggregate_name        | name    |
  input_type_schema     | name    |
  input_type            | name    |
  output_type_schema    | name    |
  output_type           | name    |
  initial_value         | text    |
  trans_function_schema | name    |
  trans_function_name   | name    |
  final_function_schema | name    |
  final_function_name   | name    |
  owner                 | name    |

pg_all_casts

        Column       |  Type   |
 --------------------+---------+-----------
  source_schema      | name    |
  source_type        | name    |
  target_schema      | name    |
  target_type        | name    |
  function_schema    | name    |
  function_name      | name    |
  function_arguments | text    |
  context            | text    |
  is_system_cast     | boolean |

pg_user_casts

        Column       |  Type   |
 --------------------+---------+-----------
  source_schema      | name    |
  source_type        | name    |
  target_schema      | name    |
  target_type        | name    |
  function_schema    | name    |
  function_name      | name    |
  function_arguments | text    |
  context            | text    |

pg_all_conversions

         Column        |  Type   | Modifiers
 ----------------------+---------+-----------
  schema_name          | name    |
  conversion_name      | name    |
  source_encoding      | name    |
  destination_encoding | name    |
  is_default           | boolean |
  function_schema      | name    |
  function_name        | name    |
  is_system_conversion | boolean |
  owner                | name    |

pg_user_conversions

         Column        |  Type   |
 ----------------------+---------+-----------
  schema_name          | name    |
  conversion_name      | name    |
  source_encoding      | name    |
  destination_encoding | name    |
  is_default           | boolean |
  function_schema      | name    |
  function_name        | name    |
  owner                | name    |

pg_databases

        Column       |  Type   |
 --------------------+---------+-----------
  database_name      | name    |
  encoding           | name    |
  default_tablespace | name    |
  database_config    | text[]  |
  is_template        | boolean |
  can_connect        | boolean |
  owner              | name    |

pg_database_config

        Column       | Type |
 --------------------+------+-----------
  database_name      | name |
  config_variable    | text |
  config_value       | text |

pg_all_foreign_key_indexes, pg_user_foreign_key_indexes

        Column        |  Type   | Modifiers
 ---------------------+---------+-----------
  schema_name         | name    |
  table_name          | name    |
  constraint_name     | name    |
  num_columns         | integer |
  num_indexed_columns | integer |
  index_name          | name    |

pg_all_foreign_keys, pg_user_foreign_keys

            Column            |  Type   |
 -----------------------------+---------+-----------
  foreign_key_schema_name     | name    |
  foreign_key_table_name      | name    |
  foreign_key_constraint_name | name    |
  foreign_key_table_oid       | oid     |
  foreign_key_columns         | name[]  |
  key_schema_name             | name    |
  key_table_name              | name    |
  key_constraint_name         | name    |
  key_table_oid               | oid     |
  key_index_name              | name    |
  key_columns                 | name[]  |
  match_type                  | text    |
  on_delete                   | text    |
  on_update                   | text    |
  is_deferrable               | boolean |
  is_deferred                 | boolean |

pg_all_foreign_key_columns, pg_user_foreign_key_columns

            Column            |  Type   |
 -----------------------------+---------+-----------
  foreign_key_schema_name     | name    |
  foreign_key_table_name      | name    |
  foreign_key_constraint_name | name    |
  foreign_key_table_oid       | oid     |
  foreign_key_column          | name    |
  column_position             | integer |
  key_schema_name             | name    |
  key_table_name              | name    |
  key_table_oid               | oid     |
  key_column                  | name    |

pg_all_grants, pg_user_grants

     Column    |  Type   |
 --------------+---------+-----------
  object_type  | name    |
  object_oid   | oid     |
  schema_name  | name    |
  object_name  | name    |
  object_args  | text    |
  owner        | name    |
  grantor      | text    |
  grantee      | text    |
  is_group     | boolean |
  privilege    | text    |
  grant_option | boolean |

pg_groups

    Column   |  Type   |
 ------------+---------+-----------
  group_name | name    |
  gid        | integer |

pg_groups_users

    Column   | Type |
 ------------+------+-----------
  group_name | name |
  user_name  | name |

pg_all_index_columns, pg_user_index_columns

      Column      |  Type   |
 -----------------+---------+-----------
  schema_name     | name    |
  table_name      | name    |
  index_name      | name    |
  column_name     | name    | NULL if an expression
  column_position | integer | 1..n
  opclass_schema  | name    |
  opclass_name    | name    |
  definition      | text    | expression or column name

pg_all_indexes

      Column      |     Type      |
 -----------------+---------------+-----------
  schema_name     | name          |
  table_name      | name          |
  index_name      | name          |
  tablespace      | name          |
  index_method    | name          |
  num_columns     | smallint      |
  is_primary_key  | boolean       |
  is_unique       | boolean       |
  is_clustered    | boolean       |
  is_expression   | boolean       |
  is_partial      | boolean       |
  estimated_rows  | real          |
  estimated_mb    | numeric(12,1) |
  is_system_table | boolean       |
  table_oid       | oid           |
  predicate       | text          |
  definition      | text          |
  owner           | name          |
  comment         | text          |

pg_user_indexes

      Column      |     Type      |
 -----------------+---------------+-----------
  schema_name     | name          |
  table_name      | name          |
  index_name      | name          |
  tablespace      | name          |
  index_method    | name          |
  num_columns     | smallint      |
  is_primary_key  | boolean       |
  is_unique       | boolean       |
  is_clustered    | boolean       |
  is_expression   | boolean       |
  is_partial      | boolean       |
  estimated_rows  | real          |
  estimated_mb    | numeric(12,1) |
  predicate       | text          |
  definition      | text          |
  owner           | name          |
  comment         | text          |

pg_all_relation_columns, pg_user_relation_columns

     Column     |  Type   |
 ---------------+---------+-----------
  schema_name   | name    |
  relation_name | name    |
  column_name   | name    |
  relation_oid  | oid     |
  column_number | integer |
  is_view       | boolean |
  nullable      | boolean |
  declared_type | text    |
  default_value | text    |
  comment       | text    |

pg_all_relation_column_type_info, pg_user_relation_column_type_info

        Column       |  Type   |
 --------------------+---------+-----------
  schema_name        | name    |
  relation_name      | name    |
  column_name        | name    |
  relation_oid       | oid     |
  column_number      | integer |
  is_view            | boolean |
  nullable           | boolean |
  domain_schema      | name    |
  domain_name        | name    |
  type_sqlname       | text    | "bare" SQL name, e.g. 'numeric'
  type_sqldef        | text    | full SQL name, e.g. 'numeric(10,2)'
  type_schema        | name    |
  type_name          | name    |
  type_oid           | oid     |
  type_length        | integer |
  is_array           | boolean |
  array_dimensions   | integer | currently always 1 for arrays
  element_sqlname    | text    |
  element_sqldef     | text    |
  element_schema     | name    |
  element_name       | name    |
  element_oid        | oid     |
  element_length     | integer |
  character_length   | integer |
  bit_length         | integer |
  integer_precision  | integer |
  float_precision    | integer |
  numeric_precision  | integer |
  numeric_scale      | integer |
  time_precision     | integer |
  interval_precision | integer |
  interval_fields    | text    |

pg_all_relations

      Column         |     Type      |
 --------------------+---------------+-----------
  schema_name        | name          |
  relation_name      | name          |
  is_system_relation | boolean       |
  is_temporary       | boolean       |
  is_view            | boolean       |
  relation_oid       | oid           |
  owner              | name          |
  comment            | text          |

pg_user_relations

      Column         |     Type      |
 --------------------+---------------+-----------
  schema_name        | name          |
  relation_name      | name          |
  is_temporary       | boolean       |
  is_view            | boolean       |
  owner              | name          |
  comment            | text          |

pg_all_rules, pg_user_rules

     Column     |  Type   |
 ---------------+---------+-----------
  schema_name   | name    |
  relation_name | name    |
  rule_name     | name    |
  rule_event    | text    |
  is_instead    | boolean |
  condition     | text    |
  action        | text    |

pg_all_schemas
        Column        |  Type   |
 ---------------------+---------+-----------
  schema_name         | name    |
  is_system_schema    | boolean |
  is_temporary_schema | boolean |
  owner               | name    |
  comment             | text    |

pg_user_schemas

        Column        |  Type   |
 ---------------------+---------+-----------
  schema_name         | name    |
  is_temporary_schema | boolean |
  owner               | name    |
  comment             | text    |

pg_all_schema_contents, pg_user_schema_contents

    Column    | Type |
 -------------+------+-----------
  schema_name | name |
  owner       | name |
  object_type | name |
  object_name | name |
  object_args | text |

pg_all_sequences

        Column       |  Type   |
 --------------------+---------+-----------
  schema_name        | name    |
  sequence_name      | name    |
  is_system_sequence | boolean |
  is_temporary       | boolean |

pg_user_sequences

        Column       |  Type   |
 --------------------+---------+-----------
  schema_name        | name    |
  sequence_name      | name    |
  is_temporary       | boolean |

pg_all_table_columns, pg_user_table_columns

     Column     |  Type   |
 ---------------+---------+-----------
  schema_name   | name    |
  table_name    | name    |
  column_name   | name    |
  table_oid     | oid     |
  column_number | integer |
  nullable      | boolean |
  declared_type | text    |
  default_value | text    |
  comment       | text    |

pg_all_table_column_type_info, pg_user_table_column_type_info

        Column       |  Type   |
 --------------------+---------+-----------
  schema_name        | name    |
  table_name         | name    |
  column_name        | name    |
  table_oid          | oid     |
  column_number      | integer |
  nullable           | boolean |
  domain_schema      | name    |
  domain_name        | name    |
  type_sqlname       | text    | "bare" SQL name, e.g. 'numeric'
  type_sqldef        | text    | full SQL name, e.g. 'numeric(10,2)'
  type_schema        | name    |
  type_name          | name    |
  type_oid           | oid     |
  type_length        | integer |
  is_array           | boolean |
  array_dimensions   | integer | currently always 1 for arrays
  element_sqlname    | text    |
  element_sqldef     | text    |
  element_schema     | name    |
  element_name       | name    |
  element_oid        | oid     |
  element_length     | integer |
  character_length   | integer |
  bit_length         | integer |
  integer_precision  | integer |
  float_precision    | integer |
  numeric_precision  | integer |
  numeric_scale      | integer |
  time_precision     | integer |
  interval_precision | integer |
  interval_fields    | text    |

pg_all_table_constraints, pg_user_table_constraints

      Column      | Type |
 -----------------+------+-----------
  schema_name     | name |
  table_name      | name |
  constraint_name | name |
  constraint_type | text |
  table_oid       | oid  |
  definition      | text |

pg_all_table_constraint_columns, pg_user_table_constraint_columns

      Column      |  Type   |
 -----------------+---------+-----------
  schema_name     | name    |
  table_name      | name    |
  constraint_name | name    |
  column_name     | name    |
  column_position | integer |
  constraint_type | text    |
  table_oid       | oid     |

pg_all_unique_constraint_columns, pg_user_unique_constraint_columns

      Column      |  Type   |
 -----------------+---------+-----------
  schema_name     | name    |
  table_name      | name    |
  constraint_name | name    |
  is_primary_key  | boolean |
  column_name     | name    |
  column_position | integer |
  table_oid       | oid     |

pg_all_primary_key_columns, pg_user_primary_key_columns

      Column      |  Type   |
 -----------------+---------+-----------
  schema_name     | name    |
  table_name      | name    |
  constraint_name | name    |
  column_name     | name    |
  column_position | integer |
  table_oid       | oid     |

pg_all_table_check_constraints, pg_user_table_check_constraints

      Column      |  Type  |
 -----------------+--------+-----------
  schema_name     | name   |
  table_name      | name   |
  constraint_name | name   |
  table_oid       | oid    |
  columns         | name[] |
  predicate       | text   |

pg_all_table_inheritance, pg_user_table_inheritance
 
       Column       |  Type   |
 -------------------+---------+-----------
  schema_name       | name    |
  table_name        | name    |
  table_oid         | oid     |
  descendent_schema | name    |
  descendent_table  | name    |
  descendent_oid    | oid     |
  ordinal_position  | integer |

pg_all_table_storage, pg_user_table_storage

      Column             |     Type      |
 ------------------------+---------------+-----------
  schema_name            | name          |
  table_name             | name          |
  tablespace             | name          |
  is_temporary           | boolean       |
  num_indexes            | integer       |
  clustered_on           | name          |
  estimated_rows         | real          |
  estimated_index_rows   | real          |
  estimated_total_mb     | numeric       |
  estimated_data_mb      | numeric       |
  estimated_main_mb      | numeric       |
  estimated_external_mb  | numeric       |
  estimated_index_mb     | numeric       |
  index_tablespaces      | name[]        |
 
pg_all_tables

      Column      |     Type      |
 -----------------+---------------+-----------
  schema_name     | name          |
  table_name      | name          |
  tablespace      | name          |
  with_oids       | boolean       |
  estimated_rows  | real          |
  estimated_mb    | numeric(12,1) | includes toast but not indexes
  has_toast_table | boolean       |
  has_descendents | boolean       |
  is_system_table | boolean       |
  is_temporary    | boolean       |
  table_oid       | oid           |
  owner           | name          |
  comment         | text          |

pg_user_tables

      Column      |     Type      |
 -----------------+---------------+-----------
  schema_name     | name          |
  table_name      | name          |
  tablespace      | name          |
  with_oids       | boolean       |
  estimated_rows  | real          |
  estimated_mb    | numeric(12,1) | includes toast but not indexes
  has_toast_table | boolean       |
  has_descendents | boolean       |
  is_temporary    | boolean       |
  owner           | name          |
  comment         | text          |

pg_tablespaces

    Column    |  Type   |
 -------------+---------+-----------
  tablespace  | name    |
  location    | text    |
  is_writable | boolean |
  owner       | name    |
  comment     | text    |

pg_tablespace_usage

    Column   | Type |
 ------------+------+-----------
  tablespace | name |
  database   | name |

pg_all_tablespace_contents, pg_user_tablespace_contents

     Column     |     Type      |
 ---------------+---------------+-----------
  tablespace    | name          |
  object_type   | text          |
  owner         | name          |
  object_schema | name          |
  object_name   | name          |
  estimated_mb  | numeric(12,1) |

pg_all_triggers, pg_user_triggers

      Column      |  Type   |
 -----------------+---------+-----------
  schema_name     | name    |
  table_name      | name    |
  trigger_name    | name    |
  function_schema | name    |
  function_name   | name    |
  function_args   | text[]  |
  function_oid    | oid     |
  before          | boolean |
  for_each_row    | boolean |
  on_insert       | boolean |
  on_delete       | boolean |
  on_update       | boolean |
  enabled         | boolean |
  definition      | text    |
  comment         | text    |
 
pg_users

          Column         |           Type           |
 ------------------------+--------------------------+-----------
  user_name              | name                     |
  uid                    | integer                  |
  create_datebase        | boolean                  |
  create_user            | boolean                  |
  superuser              | boolean                  |
  update_system_catalogs | boolean                  |
  password_expires       | timestamp with time zone |

pg_user_config

      Column      | Type |
 -----------------+------+-----------
  user_name       | name |
  config_variable | text |
  config_value    | text |

pg_all_view_columns, pg_user_view_columns

     Column     |  Type   |
 ---------------+---------+-----------
  schema_name   | name    |
  view_name     | name    |
  column_name   | name    |
  view_oid      | oid     |
  column_number | integer |
  nullable      | boolean |
  declared_type | text    |
  default_value | text    |
  comment       | text    |

pg_all_view_column_type_info, pg_user_view_column_type_info

        Column       |  Type   |
 --------------------+---------+-----------
  schema_name        | name    |
  view_name          | name    |
  column_name        | name    |
  view_oid           | oid     |
  column_number      | integer |
  nullable           | boolean |
  domain_schema      | name    |
  domain_name        | name    |
  type_sqlname       | text    | "bare" SQL name, e.g. 'numeric'
  type_sqldef        | text    | full SQL name, e.g. 'numeric(10,2)'
  type_schema        | name    |
  type_name          | name    |
  type_oid           | oid     |
  type_length        | integer |
  is_array           | boolean |
  array_dimensions   | integer | currently always 1 for arrays
  element_sqlname    | text    |
  element_sqldef     | text    |
  element_schema     | name    |
  element_name       | name    |
  element_oid        | oid     |
  element_length     | integer |
  character_length   | integer |
  bit_length         | integer |
  integer_precision  | integer |
  float_precision    | integer |
  numeric_precision  | integer |
  numeric_scale      | integer |
  time_precision     | integer |
  interval_precision | integer |
  interval_fields    | text    |

pg_all_views

      Column      |     Type      |
 -----------------+---------------+-----------
  schema_name     | name          |
  view_name       | name          |
  is_insertable   | boolean       |
  is_updateable   | boolean       |
  is_deleteable   | boolean       |
  definition      | text          |
  is_system_view  | boolean       |
  view_oid        | oid           |
  owner           | name          |
  comment         | text          |

pg_user_views

      Column      |     Type      |
 -----------------+---------------+-----------
  schema_name     | name          |
  view_name       | name          |
  is_insertable   | boolean       |
  is_updateable   | boolean       |
  is_deleteable   | boolean       |
  definition      | text          |
  owner           | name          |
  comment         | text          |



--
Josh Berkus
Aglio Database Solutions
San Francisco