Re: 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 tables in the documentation.
        f) Existing projects, features and add-ons, where appropriate,
    should gradually be shifted to use the system views 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 consistent join 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
    system tables.
     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 system views 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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> As stated above, these system views, once incorporated into a pg
> distribution, are likely to be with us *forever*.

I dislike to burst your bubble, but this claim is ridiculous on its
face.

We don't whack the system catalogs around from release to release just
because we'd like to break as many user applications as possible ...
quite the contrary. When we change the catalogs it's because there
is some fairly fundamental change in functionality involved. I think
the idea that some views in front of the catalogs can hide this problem
is the merest pipe dream.

To put it more bluntly: exactly what are you accomplishing here that
isn't already accomplished, in a *truly* standard fashion, by the
INFORMATION_SCHEMA? Why do we need yet another nonstandard view on
the underlying reality?

regards, tom lane

Tom,

> To put it more bluntly: exactly what are you accomplishing here that
> isn't already accomplished, in a *truly* standard fashion, by the
> INFORMATION_SCHEMA? Why do we need yet another nonstandard view on
> the underlying reality?

To quote myself:

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.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> PG hackers,

[snip]

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

This all looks good to me, from a quick read through. I don't claim to
have examined the details, but the general idea is definitely something
that would be very worth having.

A nice thing to add would be a more human-comprehensible view of the
pg_locks table. I keep meaning to write a view for it myself, but
haven't ever gotten a round tuit.

Tim

--
-----------------------------------------------
Tim Allen tim(at)proximity(dot)com(dot)au
Proximity Pty Ltd http://www.proximity.com.au/
http://www4.tpg.com.au/users/rita_tim/

Tim,

> A nice thing to add would be a more human-comprehensible view of the
> pg_locks table. I keep meaning to write a view for it myself, but
> haven't ever gotten a round tuit.

Jim Nasby is working on that; see his other posts.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh,

it's very difficult to read your messages (I'm using Pine), because
of some symbols (~Z on my xterm) which broke formatting.
Is't known problem of pine (4.62) or your mailer ?

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Josh Berkus wrote:
>         a) all view and column names are as explicit and as readable
> as possible (e.g. "type_schema_name", not "typnsname")

I would suggest that you align your terminology with the information
schema as much as possible, so it would be "type_schema" and not
"type_schema_name", and "ordinal_position" instead of
"column_position". Otherwise we'll have a lot of confusion ahead if we
instroduced a third parallel set of terminology.

>         c) In most places, "system" objects are segregated from
> "user" objects,
>     e.g. pg_user_indexes

I think that is a bad idea as it goes against the fundamental design of
PostgreSQL.

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

Dave Page already pointed out an example where this is a bad idea. When
a feature is removed, we can't keep claiming it exists.

>         g) All views are as normalized as possible, using child views
> rather than arrays, and providing keys and consistent join columns.

You still seem to have a bunch of arrays in there. Anything with an
array is never normalized.

That said, I don't particularly care for this proposal. If you want a
human-readable version of the system catalogs, I suggest you work on
extensions of the information schema, not a completely new interface.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Tom,
>> To put it more bluntly: exactly what are you accomplishing here that
>> isn't already accomplished, in a *truly* standard fashion, by the
>> INFORMATION_SCHEMA? Why do we need yet another nonstandard view on
>> the underlying reality?

> To quote myself:

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

So? If you want reality, look at the catalogs.

I think that in a release or three, these views will be just as
distorted a representation of the underlying reality as the
information_schema is now. Either that or you'll be changing them
incompatibly. You can't have both truth and a greater degree of
stability than the underlying catalogs.

So my opinion remains "what's the point?". All you have really
accomplished is some editorialization on table/column names.

regards, tom lane

Tom, Peter,

> That said, I don't particularly care for this proposal. If you want a
> human-readable version of the system catalogs, I suggest you work on
> extensions of the information schema, not a completely new interface.

So, both of your would prefer that we break the SQL spec with the information
schema? In order to cover all PG objects? Because that's what your
proposing. 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.

> So? If you want reality, look at the catalogs.

The system catalogs are NOT user-friendly, nor are they meant to be. The
purpose of the new system views is to answer questions like, "what objects
does user "gregory" have permissions on?" and "do any of my fuctions use
custom type 'joebert' before I change it?" and "I need a way to query all of
my functions in a loop so that I can change their permissions." You can get
that info from the system catalogs, but only if you're a SQL wizard and know
them very well.

These are all things that users (NOT pg hackers) have to do for applications
daily, and that we currently don't provide any easy, comprehensible way to
access. It's certainly easy for pg hackers to say, "oh, use the system
tables" but those tables are baffling and awkward for the many thousands of
users who are not PG hackers. Elein's series on General Bits covering
queries which were the inspiration for many of the views was immensely
popular.

Frankly, this is sounding a lot like "Who needs OpenOffice.org? Use vi!"

Further, Jim and I went over the purpose of the views on this list two months
ago and even Tom seemed positive to the idea. What the hell changed? No
wonder the discussion of the decision process dragged on so. If we
introduce a proposal, do an implementation, and come back with it 2 months
later, I don't expect a bunch of core members to act like they've never heard
of it before.

If there are things (like not dropping columns) that you want to change about
the spec, fine. But if you think that nobody needs these views, it's
because you haven't had much contact with end users lately.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

>
> Frankly, this is sounding a lot like "Who needs OpenOffice.org? Use vi!"

Frankly, this is sounding a lot like "Who needs
pgadmin/phppgadmin/pgaccess/younameit? use SELECT * FROM pg_somewot in
psql instead".

As Dave already pointed out, serious admin tools will avoid views. We
have to deal with version specific issues anyway.

Regards,
Andreas

Andreas,

> As Dave already pointed out, serious admin tools will avoid views. We
> have to deal with version specific issues anyway.

Actually, I don't think that's what Dave said. He simply said that modifying
pgAdmin to keep up with pg_catalog changes hasn't actually been a problem.

And, as an increasing number of 3rd-party tools support PostgreSQL (like
Embarcadero) they need a simple comprehensible API for system objects -- more
objects than are included in the information_schema. I'm currently working
on the integration of a major DSS tool with PostgreSQL, and we're already
using the alpha version of the system views because we need them. A 3rd
party proprietary vendor is not going to learn about OIDs, and they're not
going to use pgAdmin.

When we discussed this on this list 2 months ago, I was under the impression
that extending the information_schema was verboten becuase it would break the
SQL spec. If that's not the case, I personally would love to not duplicate
objects. But let's establish that.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> And, as an increasing number of 3rd-party tools support PostgreSQL (like
> Embarcadero) they need a simple comprehensible API for system objects -- more
> objects than are included in the information_schema.

There are only two choices: Creating a minimal subset tool, which will
rely on INFORMATION_SCHEMA (or a schema API as in ODBC) as standardized
by SQL specs, or making it specifically for every DBMS, whether using
some fancy views or not.

> A 3rd
> party proprietary vendor is not going to learn about OIDs,

Doing it seriously, it probably needs the internal DBMS object
identifiers (oid in the case of pgsql), to uniquely identify objects
even after a rename. Hiding the OIDs in schema views will reduce their
usability.

Regards,
Andreas

Andreas,

> There are only two choices: Creating a minimal subset tool, which will
> rely on INFORMATION_SCHEMA (or a schema API as in ODBC) as standardized
> by SQL specs, or making it specifically for every DBMS, whether using
> some fancy views or not.

Thing is, INFORMATION_SCHEMA doesn't hold a lot of information that people
need to know. Like permissions, comments, object owners, functions, types,
etc. If adding columns and views to the Information schema ... and changing
keys in a couple of places ... is OK, then we have somewhere to go.

Unfortunately, PostgreSQL does not have a seat on the ANSI committee, so we're
not going to get the standard changed. The standard lately belongs to
Oracle and DB2 and we have to suffer under it.

> Doing it seriously, it probably needs the internal DBMS object
> identifiers (oid in the case of pgsql), to uniquely identify objects
> even after a rename. Hiding the OIDs in schema views will reduce their
> usability.

Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs
are not consistent after a database reload and names are. I can see your
point though; what do other people think?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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. Or you could add tables that are joined to
the standard tables and contain the extra information. 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.

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.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter,

> 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. Or you could add tables that are joined to
> the standard tables and contain the extra information. 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.
>
> 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.

This makes sense; I do wish that someone had mentioned it when I originally
raised the subject of new system views. It would have saved us some work.

--Josh

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Guys,

> > it's very difficult to read your messages (I'm using Pine), because
> > of some symbols (~Z on my xterm) which broke formatting.
> > Is't known problem of pine (4.62) or your mailer ?
>
> There were a lot of \240 characters. I use mutt.

Yeah, sorry, it's a KMail problem. I'm not sure how to fix it.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, May 05, 2005 at 14:26:56 +0400,
Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
> Josh,
>
> it's very difficult to read your messages (I'm using Pine), because
> of some symbols (~Z on my xterm) which broke formatting.
> Is't known problem of pine (4.62) or your mailer ?

There were a lot of \240 characters. I use mutt.

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

On 2005-05-05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> This makes sense; I do wish that someone had mentioned it when I originally
> raised the subject of new system views. It would have saved us some work.

I'd have raised it myself if I thought there was any mileage in it. As
you can probably guess, I don't.

information_schema is fine at what it is _intended_ for - as a
standardized way of accessing a standard subset of the available metadata.
In that sense it is still necessary - however it is not sufficient, and
I don't believe that either the raw catalogs nor any reasonable extension
of information_schema actually fills that gap.

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

On Thu, May 05, 2005 at 08:15:27PM -0000, Andrew - Supernews wrote:
> On 2005-05-05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > This makes sense; I do wish that someone had mentioned it when I
> > originally raised the subject of new system views. It would have
> > saved us some work.
>
> I'd have raised it myself if I thought there was any mileage in it.
> As you can probably guess, I don't.
>
> information_schema is fine at what it is _intended_ for - as a
> standardized way of accessing a standard subset of the available
> metadata. In that sense it is still necessary - however it is not
> sufficient, and I don't believe that either the raw catalogs nor any
> reasonable extension of information_schema actually fills that gap.

The information schema has the same problem that every other system
built for every database does: it has a minimal set of abstract
information, which prevents it, by design, from having application-
specific functionality.

Kudos to the New System Views people for their hard work thus far :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> The purpose of the new system views...

As long as they are in a separate schema (like information_schema,
but hopefully not as long). pg_views? pg_info? information_skema? :)

> But if you think that nobody needs these views, it's because you
> haven't had much contact with end users lately.

Well, who really *does* need these? After all, end users should be
using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's
the job of the people writing those interfaces to know the system
catalogs well and present them to the users in a pretty fashion. If
people want an "easy" way to look up the information, they use an
interface. If not, they should learn the system catalogs.
/devilsadvocate

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200505050632
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCefgSvJuQZxSWSsgRAlhmAKCXhqS9b5n5PgmWutFAzR6D5rg5SQCfQpuL
qpOQ/PBghtBbI8RkJ2tXM7s=
=0d0+
-----END PGP SIGNATURE-----

On 2005-05-05, "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
>> The purpose of the new system views...
>
> As long as they are in a separate schema (like information_schema,
> but hopefully not as long). pg_views? pg_info? information_skema? :)

The proof-of-concept implementation puts them in pg_sysviews. This is
by no means cast in stone.

>> But if you think that nobody needs these views, it's because you
>> haven't had much contact with end users lately.
>
> Well, who really *does* need these? After all, end users should be
> using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's
> the job of the people writing those interfaces to know the system
> catalogs well and present them to the users in a pretty fashion. If
> people want an "easy" way to look up the information, they use an
> interface. If not, they should learn the system catalogs.

One thing that has become _absolutely_ clear to me in the process of
writing these views is that telling people to use the system catalogs
is a _really_ bad idea. I've seen a number of apps now that have been
doing incorrect catalog lookups and breaking to a greater or lesser
extent as a result; furthermore, writing the views has often required
delving into details of the backend implementation that are not well
documented. (See a recent discussion here on typmods for an example.)

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

Greg,

> Well, who really *does* need these? After all, end users should be
> using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's
> the job of the people writing those interfaces to know the system
> catalogs well and present them to the users in a pretty fashion. If
> people want an "easy" way to look up the information, they use an
> interface. If not, they should learn the system catalogs.
> /devilsadvocate

Well, because of who worked on it we were primarily thinking of custom
applications developers and integrators, who fall somewhere between "dumb
user" and "pg hacker", and thus need an interface which falls between
"pgAdmin" and "pg_class". Also database designers (not all database
designers like using GUIs).

And, as Lance points out, these system views would help as a diagnostic view
for support engineers. Actually, I'll second that; I've had cause to
recommend the CVS system views more than a dozen times to pgsql newbies on
IRC. For example, there is an easy query you can do using the new system
views (thanks, Andrew!) to check what unindexed foriegn keys you have.

So, "who would they be useful to?" is "everyone *except* the people on this
list."

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On N, 2005-05-05 at 22:43 +0000, Greg Sabino Mullane wrote:

> > The purpose of the new system views...
>
> As long as they are in a separate schema (like information_schema,
> but hopefully not as long). pg_views? pg_info? information_skema? :)
>
> > But if you think that nobody needs these views, it's because you
> > haven't had much contact with end users lately.
>
> Well, who really *does* need these? After all, end users should be
> using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc).

Perhaps they can be thouhgt of as another "interface of some sort" ;)

They may be even not installed by default, but bundled and as easy to
install as any (other) contrib module.

--
Hannu Krosing <hannu(at)tm(dot)ee>

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.

On 2005-05-05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> And, as Lance points out, these system views would help as a diagnostic view
> for support engineers. Actually, I'll second that; I've had cause to
> recommend the CVS system views more than a dozen times to pgsql newbies on
> IRC. For example, there is an easy query you can do using the new system
> views (thanks, Andrew!) to check what unindexed foriegn keys you have.

Another popular query that gets asked on IRC is "how do I find out what
permissions have been granted to user X?" Try doing _that_ one in the
system catalogs.

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

> As Dave already pointed out, serious admin tools will avoid views. We
> have to deal with version specific issues anyway.

I don't see why phpPgAdmin would avoid using the views, unless some
serious randomness happened that we had to support. The unimaginable
craziness of currently trying to support multiple versions of postgresql
is pretty bad, and the information_schema is next to useless for these
things since it doesn't have PostgreSQL specific things in it.

Chris

> Hmmm ... we argued about this. I was in favor of hiding the OIDs because OIDs
> are not consistent after a database reload and names are. I can see your
> point though; what do other people think?

Well phpPgAdmin is unable to use the pg_tables view, for instance,
because we have no way of getting the table comment using the
information in that view...

Chris

On 2005-05-06, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> wrote:
>> Hmmm ... we argued about this. I was in favor of hiding the OIDs
>> because OIDs are not consistent after a database reload and names are.
>> I can see your point though; what do other people think?
>
> Well phpPgAdmin is unable to use the pg_tables view, for instance,
> because we have no way of getting the table comment using the
> information in that view...

If you look at the columns lists, you'll find that oids are exposed in
a number of places. In general, I didn't make a point of exposing them
everywhere, but I _did_ expose them in cases where I thought it likely
that querying by or for the oid in particular might be needed. (OIDs
are, after all, exposed quite a bit by the wire protocol and by libpq.)

Whether the balance is correct here is something I'm open to suggestions
about.

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

On Thursday 05 May 2005 19:17, Andrew - Supernews wrote:
> On 2005-05-05, "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
> furthermore, writing the views has often required
> delving into details of the backend implementation that are not well
> documented. (See a recent discussion here on typmods for an example.)

This argument at least is a red herring. The answer to a lack of
documentation about a widget is never to build more widgets that hide the
original widget and require more documentation, the answer is to better
document the orginal widget.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Thursday 05 May 2005 19:37, Hannu Krosing wrote:
> On N, 2005-05-05 at 22:43 +0000, Greg Sabino Mullane wrote:
> > > The purpose of the new system views...
> >
> > As long as they are in a separate schema (like information_schema,
> > but hopefully not as long). pg_views? pg_info? information_skema? :)
> >
> > > But if you think that nobody needs these views, it's because you
> > > haven't had much contact with end users lately.
> >
> > Well, who really *does* need these? After all, end users should be
> > using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc).
>
> Perhaps they can be thouhgt of as another "interface of some sort" ;)
>
> They may be even not installed by default, but bundled and as easy to
> install as any (other) contrib module.

I was starting to think this... like this should be a project on foundry
called "enhanced system views" that would be fairly database version
independant and people could install into any databases they needed them in.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

>
> I was starting to think this... like this should be a project on foundry
> called "enhanced system views" that would be fairly database version
> independant and people could install into any databases they needed them in.

You mean like:

http://pgfoundry.org/projects/newsysviews/

>

On 2005-05-06, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> wrote:
> On Thursday 05 May 2005 19:17, Andrew - Supernews wrote:
>> On 2005-05-05, "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
>> furthermore, writing the views has often required
>> delving into details of the backend implementation that are not well
>> documented. (See a recent discussion here on typmods for an example.)
>
> This argument at least is a red herring. The answer to a lack of
> documentation about a widget is never to build more widgets that hide the
> original widget and require more documentation, the answer is to better
> document the orginal widget.

I disagree. If you have a bad interface, the fix is to provide a better
one, not aggravate the problem by encouraging even more use of the bad
interface.

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

Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> On 2005-05-06, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> wrote:
>> On Thursday 05 May 2005 19:17, Andrew - Supernews wrote:
>>> furthermore, writing the views has often required
>>> delving into details of the backend implementation that are not well
>>> documented. (See a recent discussion here on typmods for an example.)
>>
>> This argument at least is a red herring.

> I disagree. If you have a bad interface, the fix is to provide a better
> one, not aggravate the problem by encouraging even more use of the bad
> interface.

"Bad" and "poorly documented" are two quite different aspersions.

More to the point: how can you build a "good" interface on top of a
"bad" one? Whatever fundamental shortcomings exist in the latter cannot
be hidden by the former.

regards, tom lane

Tom, Andrew, Robert,

> More to the point: how can you build a "good" interface on top of a
> "bad" one? Whatever fundamental shortcomings exist in the latter cannot
> be hidden by the former.

I think "bad" and "good" are pretty irrelevant myself. The system tables are
very "good" at what they do: support the postgresql code base. They are not
*meant* to be user-friendly. That's why we need a different "interface" to
be "good" for a different purpose. Which is what we're trying to do.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Fri, May 06, 2005 at 02:43:19AM -0000, Andrew - Supernews wrote:
> On 2005-05-06, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> wrote:
> >> Hmmm ... we argued about this. I was in favor of hiding the OIDs
> >> because OIDs are not consistent after a database reload and names are.
> >> I can see your point though; what do other people think?
> >
> > Well phpPgAdmin is unable to use the pg_tables view, for instance,
> > because we have no way of getting the table comment using the
> > information in that view...
>
> If you look at the columns lists, you'll find that oids are exposed in
> a number of places. In general, I didn't make a point of exposing them
> everywhere, but I _did_ expose them in cases where I thought it likely
> that querying by or for the oid in particular might be needed. (OIDs
> are, after all, exposed quite a bit by the wire protocol and by libpq.)
>
> Whether the balance is correct here is something I'm open to suggestions
> about.

Perhaps it makes sense to expose the OIDs of each object in it's view.
IE: pg_tables would have table_oid, pg_types would have type_oid, etc.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Thu, May 05, 2005 at 06:55:38PM +0200, Andreas Pflug wrote:
> Josh Berkus wrote:
>
> >
> >Frankly, this is sounding a lot like "Who needs OpenOffice.org? Use vi!"
>
> Frankly, this is sounding a lot like "Who needs
> pgadmin/phppgadmin/pgaccess/younameit? use SELECT * FROM pg_somewot in
> psql instead".

And what about users who's interface to PostgreSQL is psql?
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Thu, May 05, 2005 at 11:29:34PM -0400, Robert Treat wrote:
> I was starting to think this... like this should be a project on foundry
> called "enhanced system views" that would be fairly database version
> independant and people could install into any databases they needed them in.

The pgFoundry project started as a means for those of us working on this
to coordinate our efforts and has grown somewhat from that. The original
intention was to make this part of initdb, and support older versions
through the pgFoundry project.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On 5/6/05, Greg Sabino Mullane <greg(at)turnstep(dot)com> wrote:
> As long as they are in a separate schema (like information_schema,
> but hopefully not as long). pg_views? pg_info? information_skema? :)
>
> > But if you think that nobody needs these views, it's because you
> > haven't had much contact with end users lately.
>
> Well, who really *does* need these? After all, end users should be
> using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's
> the job of the people writing those interfaces to know the system
> catalogs well and present them to the users in a pretty fashion. If
> people want an "easy" way to look up the information, they use an
> interface. If not, they should learn the system catalogs.
> /devilsadvocate

Wellll... Lets assume that young DBA needs to get a list of primary
keys for each table. If she's smart she'll probably run psql -E and
get queries like:

SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid=i.inhparent AND i.inhrelid = '6338115' ORDER BY inhseqno
ASC

SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i
WHERE a.attrelid = '6338117' AND a.attnum > 0 AND NOT a.attisdropped
AND a.attrelid = i.indexrelid
ORDER BY a.attnum

SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname,
pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)
FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class
c2, pg_catalog.pg_am a
WHERE i.indexrelid = c.oid AND c.oid = '6261315' AND c.relam = a.oid
AND i.indrelid = c2.oid

...and so on. Then refashion them to do the needed query.

Then again she may look inside information_schema.* (columns?), but
it is not as natural as one would like.

And then again, as most people are lazy, she would probably use:

select schemaname,tablename,attname from pg_stats where n_distinct =
-1 and schemaname='public';

Which is simply the stupidest way, and of course the wrong one. Yet it
gives an illusion of returning "quite right" data the easy way... Sometimes
it may be terribly tempting...

I would certainly like to see these views in PostgreSQL. Maybe as
a contrib package (just as there are tsearch2 or intarray). I think
such views would not be of much use for, say pgAdmin. Yet again
for querying from perl/php or over "human carrier" it would be
benefitial, I guess.

My 0.03 PLN. ;)

Regards,
Dawid

Christopher Kings-Lynne wrote:
> The unimaginable craziness of currently trying to support multiple
> versions of postgresql is pretty bad,

Hu? So you suggest version specific admin tools? *scratch head*

Just for curiosity:
pgAdmin CVS currently has 80 version checked pieces of code to support
7.3/7.4/8.0, and the vast majority is about version specific CREATE
options, i.e. DDL commands, not for querying system catalogs. I remember
only a single occurrence when a system catalog change had an impact on
pgAdmin's usability (dropped datpath).

> and the information_schema is next to useless for these things since
> it doesn't have PostgreSQL specific things in it.

And the restriction to current user owned objects reduces usability to zero.

Regards,
Andreas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> And what about users who's interface to PostgreSQL is psql?

Backslash commands.

-----BEGIN PGP SIGNATURE-----

iD8DBQFCeqzEvJuQZxSWSsgRAgkPAKC1V0Sm3Umi1eGFnoj1P5Qt26V32wCeMjLh
+3LX4eUjgKdy+SOKHSRzRxQ=
=mSNP
-----END PGP SIGNATURE-----

On Thursday 05 May 2005 23:45, Joshua D. Drake wrote:
> > I was starting to think this... like this should be a project on foundry
> > called "enhanced system views" that would be fairly database version
> > independant and people could install into any databases they needed them
> > in.
>
> You mean like:
>
> http://pgfoundry.org/projects/newsysviews/

As Jim points out, their current long term goal is to be a replacement for the
current system views (hence *new* system views), and the current project was
created to facilitate development. What I am thinking is that the project
take on a different goal, mainly that it be an add on that intends to work
along side the current system views and be both backward and forward
compatible (hence *enhanced* system views). It's a subtle difference.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Fri, May 06, 2005 at 11:34:49AM -0000, Greg Sabino Mullane wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > And what about users who's interface to PostgreSQL is psql?
>
> Backslash commands.

There's a ton of cases the backslash commands don't cover, which others
have given examples of. Even if we went to town and added a whole bunch
of extra backslash commands, there's still going to be things that just
aren't covered.

Aside from that, it's currently rather silly that every admin tool has
to code up a very complex set of queries to get info from the system
catalog. It makes much more sense to put that complexity into a set of
system views that are maintained as part of the backend, instead of
pushing that effort out to everyone who writes tools.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> Aside from that, it's currently rather silly that every admin tool has
> to code up a very complex set of queries to get info from the system
> catalog. It makes much more sense to put that complexity into a set of
> system views that are maintained as part of the backend, instead of
> pushing that effort out to everyone who writes tools.

So instead, they should code up complex queries to get info from the
system views? Your claim only makes sense if you know exactly what
"every admin tool" is going to need, what format they are going to want
it in, and other things that I doubt you are really prescient enough
to get 100% right.

regards, tom lane

Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
>
>>Aside from that, it's currently rather silly that every admin tool has
>>to code up a very complex set of queries to get info from the system
>>catalog. It makes much more sense to put that complexity into a set of
>>system views that are maintained as part of the backend, instead of
>>pushing that effort out to everyone who writes tools.
>
>
> So instead, they should code up complex queries to get info from the
> system views? Your claim only makes sense if you know exactly what
> "every admin tool" is going to need, what format they are going to want
> it in, and other things that I doubt you are really prescient enough
> to get 100% right.
>

Well I think you're wrong. We really should have a view like this, I'll
provide more to include them in pgsql8.1:

CREATE VIEW pg_dependent_objects_for_pga3 AS
SELECT DISTINCT deptype, classid, cl.relkind,
CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind
WHEN tg.oid IS NOT NULL THEN 'T'::text
WHEN ty.oid IS NOT NULL THEN 'y'::text
WHEN ns.oid IS NOT NULL THEN 'n'::text
WHEN pr.oid IS NOT NULL THEN 'p'::text
WHEN la.oid IS NOT NULL THEN 'l'::text
WHEN rw.oid IS NOT NULL THEN 'R'::text
WHEN co.oid IS NOT NULL THEN 'C'::text || contype
ELSE '' END AS type,
COALESCE(coc.relname, clrw.relname) AS ownertable,
COALESCE(cl.relname, conname, proname, tgname, typname,
lanname, rulename, ns.nspname) AS refname,
COALESCE(nsc.nspname, nso.nspname, nsp.nspname,
nst.nspname, nsrw.nspname) AS nspname
FROM pg_depend dep
LEFT JOIN pg_class cl ON dep.objid=cl.oid
LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
LEFT JOIN pg_proc pr on dep.objid=pr.oid
LEFT JOIN pg_namespace nsp ON pronamespace=nsp.oid
LEFT JOIN pg_trigger tg ON dep.objid=tg.oid
LEFT JOIN pg_type ty on dep.objid=ty.oid
LEFT JOIN pg_namespace nst ON typnamespace=nst.oid
LEFT JOIN pg_constraint co on dep.objid=co.oid
LEFT JOIN pg_class coc ON conrelid=coc.oid
LEFT JOIN pg_namespace nso ON connamespace=nso.oid
LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid
LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
LEFT JOIN pg_namespace nsrw ON cl.relnamespace=nsrw.oid
LEFT JOIN pg_language la ON dep.refobjid=la.oid
LEFT JOIN pg_namespace ns ON dep.objid=ns.oid

Isn't it a shame that this widely usable query isn't included in pgsql
since 7.0? ;-)

Regards,
Andreas

On Fri, May 06, 2005 at 12:21:55PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > Aside from that, it's currently rather silly that every admin tool has
> > to code up a very complex set of queries to get info from the system
> > catalog. It makes much more sense to put that complexity into a set of
> > system views that are maintained as part of the backend, instead of
> > pushing that effort out to everyone who writes tools.
>
> So instead, they should code up complex queries to get info from the
> system views? Your claim only makes sense if you know exactly what
> "every admin tool" is going to need, what format they are going to want
> it in, and other things that I doubt you are really prescient enough
> to get 100% right.

Actually, given the amount of info provided by the views, I'd be
surprised if there's anything that is either missing (except for OIDs in
some places), or in the 'wrong format' for admin tools. If there is then
I'd like to hear about it so we can consider changes.

And btw, I'm not suggesting that these views will mean that admin tools
will never have to do any joining of tables, but they shouldn't require
anything nearly as complex as what's currently required.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Fri, May 06, 2005 at 09:08:10AM -0400, Robert Treat wrote:
> On Thursday 05 May 2005 23:45, Joshua D. Drake wrote:
> > > I was starting to think this... like this should be a project on foundry
> > > called "enhanced system views" that would be fairly database version
> > > independant and people could install into any databases they needed them
> > > in.
> >
> > You mean like:
> >
> > http://pgfoundry.org/projects/newsysviews/
>
> As Jim points out, their current long term goal is to be a replacement for the
> current system views (hence *new* system views), and the current project was
> created to facilitate development. What I am thinking is that the project
> take on a different goal, mainly that it be an add on that intends to work
> along side the current system views and be both backward and forward
> compatible (hence *enhanced* system views). It's a subtle difference.

What I don't like about that idea (assuming you're intending that these
views are never brought into initdb) is it means that admin tools (like
psql) then either require the user to install the views by hand, or they
don't use them and keep doing things the hard (and error-prone) way.

But yes, the intention is to continue to support backwards compatability
as much as possible. Currently I believe that compatability stops at
versions that don't support schemas, though that could change.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Fri, May 06, 2005 at 01:20:09AM -0500, Jim C. Nasby wrote:
> On Fri, May 06, 2005 at 02:43:19AM -0000, Andrew - Supernews wrote:
> > On 2005-05-06, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> wrote:
> > >> Hmmm ... we argued about this. I was in favor of hiding the OIDs
> > >> because OIDs are not consistent after a database reload and names are.
> > >> I can see your point though; what do other people think?
> > >
> > > Well phpPgAdmin is unable to use the pg_tables view, for instance,
> > > because we have no way of getting the table comment using the
> > > information in that view...
> >
> > If you look at the columns lists, you'll find that oids are exposed in
> > a number of places. In general, I didn't make a point of exposing them
> > everywhere, but I _did_ expose them in cases where I thought it likely
> > that querying by or for the oid in particular might be needed. (OIDs
> > are, after all, exposed quite a bit by the wire protocol and by libpq.)
> >
> > Whether the balance is correct here is something I'm open to suggestions
> > about.
>
> Perhaps it makes sense to expose the OIDs of each object in it's view.
> IE: pg_tables would have table_oid, pg_types would have type_oid, etc.

And this is exactly what we are doing. The table view has a tableoid.
The type view has the type oid, etc.

> --
> Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Actually bad and good are appropriate. The structure
of the system catalogs dates back to the grad student's
theses and is not really good. But it is stable and
does the job. It really is not user friendly, however.

I reassert that I have seen only one decent schema drawing
of the system catalogs and it is obtuse at best. The
short comings wrt user friendliness in the system catalogs
cannot be adequately handled by better documentation.
The complex queries people will have to write to extract
the information means that each person will have to write their
own set of system views, correctly or not. This is the
current state of affairs.

With regards to the backslash commands, yes, let us improve
them! And by the way, the same views and queries will have
to be written for improved backslash commands are the same
as are being written for the newsysviews. The additional interface
of SQL is also imperative for interfaces that want to
create scripts and/or do operations on the data found
in the system catalog. e.g. drop all foreign keys linked
to table foo.

Elein
elein(at)varlena(dot)com

On Thu, May 05, 2005 at 09:22:40PM -0700, Josh Berkus wrote:
> Tom, Andrew, Robert,
>
> > More to the point: how can you build a "good" interface on top of a
> > "bad" one? Whatever fundamental shortcomings exist in the latter cannot
> > be hidden by the former.
>
> I think "bad" and "good" are pretty irrelevant myself. The system tables are
> very "good" at what they do: support the postgresql code base. They are not
> *meant* to be user-friendly. That's why we need a different "interface" to
> be "good" for a different purpose. Which is what we're trying to do.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

On 2005-05-06, "Jim C. Nasby" <decibel(at)decibel(dot)org> wrote:
> But yes, the intention is to continue to support backwards compatability
> as much as possible. Currently I believe that compatability stops at
> versions that don't support schemas, though that could change.

I have made no attempt to support pre-7.4. It _might_ be possible to do
7.3, but I haven't checked what functionality we currently rely on is
missing there.

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

On Friday 06 May 2005 13:43, Jim C. Nasby wrote:
> On Fri, May 06, 2005 at 09:08:10AM -0400, Robert Treat wrote:
> > On Thursday 05 May 2005 23:45, Joshua D. Drake wrote:
> > > > I was starting to think this... like this should be a project on
> > > > foundry called "enhanced system views" that would be fairly database
> > > > version independant and people could install into any databases they
> > > > needed them in.
> > >
> > > You mean like:
> > >
> > > http://pgfoundry.org/projects/newsysviews/
> >
> > As Jim points out, their current long term goal is to be a replacement
> > for the current system views (hence *new* system views), and the current
> > project was created to facilitate development. What I am thinking is
> > that the project take on a different goal, mainly that it be an add on
> > that intends to work along side the current system views and be both
> > backward and forward compatible (hence *enhanced* system views). It's a
> > subtle difference.
>
> What I don't like about that idea (assuming you're intending that these
> views are never brought into initdb) is it means that admin tools (like
> psql) then either require the user to install the views by hand, or they
> don't use them and keep doing things the hard (and error-prone) way.
>

Sorry, but I'm still in the "admin tools wont use these" camp since I don't
believe these views can solve an admin tools need to support multiple
versioning within its code. I also don't think it is any harder to learn to
query the system tables than it would be to learn to query these new views
(with a few caevets that I will come back to) and it might actually be
better. If I'm building an admin tool, I have to know that tablespaces
aren't supported on some older versions, and I think it is easier to figure
this out if my query breaks on tablespace information rather than if my query
just silently sends me some special data (NULL?) that I have to interpret to
mean "not supported".

That said, some admin tools already have a requirment that you install some
little piece of schema into your database to support them, they could
include this package along with thier software if they felt strongly about
it.

The cavet I am thinking about from above is things like the relacl bits of
pg_class, which are a total poop to work with. Adding a couple of new system
views to help make that information more transparent would be a good thing.
Actually I am thinkinga couple of parts of this stuff could be used as an
enhancement to the current system views if people weren't interested in a
wholesale replacement.

> But yes, the intention is to continue to support backwards compatability
> as much as possible. Currently I believe that compatability stops at
> versions that don't support schemas, though that could change.

I'm curious, are the queries between various versions actually all that
different? I can't imagine that you can present a stable interface going back
3 versions that is relevant to all three versions that also requires serious
query changes between each version.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Fri, May 06, 2005 at 05:44:43PM -0400, Robert Treat wrote:
> Sorry, but I'm still in the "admin tools wont use these" camp since I don't
> believe these views can solve an admin tools need to support multiple
> versioning within its code. I also don't think it is any harder to learn to
> query the system tables than it would be to learn to query these new views
> (with a few caevets that I will come back to) and it might actually be
> better. If I'm building an admin tool, I have to know that tablespaces

I find it hard to believe that it's easier to write a 30 line query
instead of just selecting out of a single view. But, even if an admin
tool does want to 'go direct to the source' and query the system tables,
ISTM that having a reference implementation (the system views) would be
very valuable.

> aren't supported on some older versions, and I think it is easier to figure
> this out if my query breaks on tablespace information rather than if my query
> just silently sends me some special data (NULL?) that I have to interpret to
> mean "not supported".

Well, these views don't prevent you from using version() to know what is
and isn't supported, but if you can think of other means to indicate
what features are and aren't available I'm all ears.

> That said, some admin tools already have a requirment that you install some
> little piece of schema into your database to support them, they could
> include this package along with thier software if they felt strongly about
> it.
>
> The cavet I am thinking about from above is things like the relacl bits of
> pg_class, which are a total poop to work with. Adding a couple of new system
> views to help make that information more transparent would be a good thing.
> Actually I am thinkinga couple of parts of this stuff could be used as an
> enhancement to the current system views if people weren't interested in a
> wholesale replacement.

It's certainly not decided that these views would replace anything. I'm
in favor of always keeping these views in their own schema so that it's
up to the user to decide what exactly they want to query. If they want
stuff out of the current catalog, then use pg_catalog. If they want
these new views, then use pg_sysviews.

There is the possibility of eventually replacing some of the old system
views, but that would be several versions away, if it were to ever
happen. And of course these would not replace the system tables.

> > But yes, the intention is to continue to support backwards compatability
> > as much as possible. Currently I believe that compatability stops at
> > versions that don't support schemas, though that could change.
>
> I'm curious, are the queries between various versions actually all that
> different? I can't imagine that you can present a stable interface going back
> 3 versions that is relevant to all three versions that also requires serious
> query changes between each version.

I suggest taking a gander at the '_compat' files at
http://lnk.nu/cvs.pgfoundry.org/251/. Basically, features that are new
in 8.0 (ie: tablespaces) have an abstraction layer. The code under that
layer is version specific, but the code above it is generic. So
_compat74.sql creates a bogus pg_tablespace (though now that I'm
thinking about it, we should probably use a different name for that,
such as _pg_tablespace).

Of course, we could certainly go the route of having completely
different view definitions for different versions, but I'm not sure
that's an improvement.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On 2005-05-08, "Jim C. Nasby" <decibel(at)decibel(dot)org> wrote:
> I suggest taking a gander at the '_compat' files at
> http://lnk.nu/cvs.pgfoundry.org/251/. Basically, features that are new
> in 8.0 (ie: tablespaces) have an abstraction layer. The code under that
> layer is version specific, but the code above it is generic. So
> _compat74.sql creates a bogus pg_tablespace (though now that I'm
> thinking about it, we should probably use a different name for that,
> such as _pg_tablespace).

Remember that this is still an alpha version. In the longer term I think
we should look at splitting it into two schemas, one with the views
themselves and another with the support functions and other implementation
details. (There are other ways to handle pg_tablespace too, that just
happened to be the convenient one for proof-of-concept testing.)

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

On 2005-05-05, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> I would suggest that you align your terminology with the information
> schema as much as possible, so it would be "type_schema" and not
> "type_schema_name", and "ordinal_position" instead of
> "column_position". Otherwise we'll have a lot of confusion ahead if we
> instroduced a third parallel set of terminology.

Personally I'm open to suggestions on this; we didn't entirely agree on
the naming conventions when writing the stuff so far.

>> c) In most places, "system" objects are segregated from
>> "user" objects, e.g. pg_user_indexes
>
> I think that is a bad idea as it goes against the fundamental design of
> PostgreSQL.

In what way? Please elaborate.

>> g) All views are as normalized as possible, using child views
>> rather than arrays, and providing keys and consistent join columns.
>
> You still seem to have a bunch of arrays in there. Anything with an
> array is never normalized.

There are 6 array columns in there at the moment. One looks pointless and
might get removed (database_config). The others are all intentional and
cover cases where the denormalized view is (a) already easily available
within the query and (b) substantially useful. The normalized versions
are available too in all cases.

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

Robert,

> As Jim points out, their current long term goal is to be a replacement for
> the current system views (hence *new* system views), and the current
> project was created to facilitate development.  What I am thinking is that
> the project take on a different goal, mainly that it be an add on that
> intends to work along side the current system views and be both backward
> and forward compatible (hence *enhanced* system views).  It's a subtle
> difference.

*what* current system views? We appear to have 8 of them. 2 we're not
touching at this time. The other 6 are all deficient in various ways, and
those ways are not fixable in a backwards-compatible fashion.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Am Montag, 9. Mai 2005 00:41 schrieb Andrew - Supernews:
> >> c) In most places, "system" objects are segregated from
> >> "user" objects, e.g. pg_user_indexes
> >
> > I think that is a bad idea as it goes against the fundamental design of
> > PostgreSQL.
>
> In what way? Please elaborate.

PostgreSQL does not really distinguish between "system" and "user" things.
How will you do that?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Am Freitag, 6. Mai 2005 12:20 schrieb Andreas Pflug:
> > and the information_schema is next to useless for these things since
> > it doesn't have PostgreSQL specific things in it.
>
> And the restriction to current user owned objects reduces usability to
> zero.

The information schema restricts the views to the objects to which you have
some access right, which doesn't seem all that useless.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Tue, May 10, 2005 at 04:55:40PM +0200, Peter Eisentraut wrote:
> Am Montag, 9. Mai 2005 00:41 schrieb Andrew - Supernews:
> > >> c) In most places, "system" objects are segregated from
> > >> "user" objects, e.g. pg_user_indexes
> > >
> > > I think that is a bad idea as it goes against the fundamental design of
> > > PostgreSQL.
> >
> > In what way? Please elaborate.
>
> PostgreSQL does not really distinguish between "system" and "user" things.
> How will you do that?

It's currently done using this function:

create or replace function _pg_sv_system_schema(name) returns boolean
as 'select $1 in (name ''pg_catalog'', name ''pg_toast'',
name ''pg_sysviews'', name ''information_schema'')'
language sql immutable strict;

Objects that are in one of those schemas are considered system objects.
This is how pg_dump does it (except for casts, which are considered
system objects if the source type, destination type, and conversion
function are ALL in system schemas).

psql also distinguishes between system and user tables, although it
restricts this to pg_catalog.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On 2005-05-10, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Am Freitag, 6. Mai 2005 12:20 schrieb Andreas Pflug:
>> > and the information_schema is next to useless for these things since
>> > it doesn't have PostgreSQL specific things in it.
>>
>> And the restriction to current user owned objects reduces usability to
>> zero.
>
> The information schema restricts the views to the objects to which you have
> some access right, which doesn't seem all that useless.

Actually that varies according to the individual view. Some restrict to
objects to which you have access, others restrict to objects that you own.
Furthermore, in the latter case there is no exception for superusers.

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

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> On Tue, May 10, 2005 at 04:55:40PM +0200, Peter Eisentraut wrote:
>> PostgreSQL does not really distinguish between "system" and "user" things.
>> How will you do that?

> It's currently done using this function:

> create or replace function _pg_sv_system_schema(name) returns boolean
> as 'select $1 in (name ''pg_catalog'', name ''pg_toast'',
> name ''pg_sysviews'', name ''information_schema'')'
> language sql immutable strict;

> Objects that are in one of those schemas are considered system objects.
> This is how pg_dump does it

Peter's point still stands though: the *system* isn't making that
distinction. pg_dump needs to make a distinction so that it doesn't
dump built-in objects; which is not necessarily the same distinction
that a user might want to make. Thus, the fact that psql does it a
bit differently isn't necessarily a bug.

I think the real problem here is that it's hard to be all things to all
people. If you suppress display of certain objects, that may be nice
suppression of clutter for one user, yet render the view useless from
the perspective of another user --- or even the same user on a different
day, when he is looking for a particular built-in function for instance.
(I know it's always bugged the heck out of me that \df editorializes on
which functions it thinks I want to see.)

regards, tom lane

Tom,

> I think the real problem here is that it's hard to be all things to all
> people. If you suppress display of certain objects, that may be nice
> suppression of clutter for one user, yet render the view useless from
> the perspective of another user --- or even the same user on a different
> day, when he is looking for a particular built-in function for instance.
> (I know it's always bugged the heck out of me that \df editorializes on
> which functions it thinks I want to see.)

But all of the views we've composed as pg_user_* also have a pg_all_*. So
users can do what they want.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Wed, 4 May 2005 21:37:40 -0700, Josh Berkus <josh(at)agliodbs(dot)com>
wrote:
>As stated above, these system views, once incorporated into a pg distribution,
>are likely to be with us *forever*.

I don't think that this is doable. :-(

You might want to put the system views into a version specific schema,
say pg_views81. The next PG version will contain a new schema
pg_views82 plus a version of 8.1 views that have been adapted to new
features and catalog structures as far as possible without breaking
compatibility. Ideally the views in pg_views81 and pg_views82 will
look the same, but most likely there will be some differences. In PG
8.3 we will have schemas pg_views81, pg_views82, and pg_views83 ...

Obviously it will get harder and harder to maintain older system view
schemas with each new Postgres version. If in PG 8.7 it becomes clear
that carrying on pg_views81 doesn't make sense any more, you simply
drop it. By that time tool vendors should have had enough time to
make their tools compatible with pg_views8x, for some x >= 2.

Servus
Manfred