Views, views, views! (long) - Mailing list pgsql-hackers
| From | Josh Berkus |
|---|---|
| Subject | Views, views, views! (long) |
| Date | |
| Msg-id | 200505042137.41026.josh@agliodbs.com Whole thread Raw |
| Responses |
Re: Views, views, views! (long)
Re: Views, views, views! (long) Re: Views, views, views! (long) Re: Views, views, views! (long) Re: Views, views, views! (long) |
| List | pgsql-hackers |
PG hackers, AndrewSN, Jim Nasby, Elein and I have been working for the last couple of months on a new set of system views for PostgreSQL. (primarily Andrew, who did the lion's share of the work and came up with many clever SQL workarounds) We'd like to include them in the 8.1 release, so we're going to post most of the definitions for your feedback now. Let me summarize: Goals of the New System Views --------------------------------------- 1. To be easily human-readable, unlike the system tables. 2. To provide a consistent API to PostgreSQL object definitions which will seldom (if ever) be changed, only added to. 3. To provide queryable definitions for all PostgreSQL objects. In more detail: 1. The current system tables are designed around performance and code requirements, and as such are optimized for code access, not comprehensability. Column names are obscure, special system data types are used, and everything is OIDs and typids. This is perfect for our code, but too many user-space applications are using these tables for comfort. Our first system views (pg_tables, for example) only went halfway in providing a user-friendly interface. So the new system views have the following requirements: a) all view and column names are as explicit and as readable as possible (e.g. "type_schema_name", not "typnsname") b) OIDs, typids, and other system codes are avoided wherever possible in preference to full object names c) In most places, "system" objects are segregated from "user" objects, e.g. pg_user_indexes 2. One of the issues with user applications querying the system tables is that they can be subject to significant changes from version to version. This has kept the pgAdmin and phpPgAdmin teams busy since 7.2, and means that GUI tools which fall out of maintenance (like Xpg) soon stop working. This is easily remedied through a set of system views which will remain consistent regardless of changes in the underlying system tables. This has the beneficial effect of giving us more freedom to make changes to the system tables. Further, we discovered when we proposed dropping the old system views that once these views are created we're stuck with them for several years, if not forever; people's not-easily-recoded tools rely on them. d) Columns may be added to the system views, but never dropped or changed in incompatible ways. Likewise,views will be added but not dropped or renamed. e) Users and app developers should be actively encouraged to use the system views rather than the system tablesin the documentation. f) Existing projects, features and add-ons, where appropriate, should gradually be shifted to use the systemviews to minimize version maintenance. 3. The new system views (unlike, for example, \d) are designed to be a SQL interface to system objects. This means that: g) All views are as normalized as possible, using child views rather than arrays, and providing keys and consistentjoin columns. h) Each view or set of views provides all of the data required to replicate the appropriate CREATE statement. i) Column names are designed to be universal to a particular type of data, where this does not cause duplication. For example, pg_user_tables has "schema_name" rather than "table_schema". This was done to make joins easier (i.e. USING, NATURAL JOIN) Additional assumptions we worked with include: j) all view names are plural in order to prevent conflict with systemtables. k) no procedural languages are used, as we don't want to make PLs mandatory. Currently everything is SQL (really!)and we may move a few functions to a C library eventually. l) internal functions required for the systemviews are named using a "_pg_" convention. m) We will be offering a "back-patch" for 7.4 and 8.0 via pgFoundry. What We Need From Hackers -------------------------------------- (other than patch approval, that is) As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. As such, we really can't afford to do major refactoring of the column names and structure once they're released. So it's really, really, important for everyone on hackers to look over the definitions below and find stuff that we've missed or doesn't make any sense. Also, we'd like to know about 8.1 changes that affect these views. There are two additional other questions to discuss that our team as not settled: I) Should the new views be part of /contrib before they become part of the main source? II) Should the new views be in their own schema? This would make them easier to manage for DBAs who want to restrict access or dump them, but would add a second "system" schema to the template. Information_Schema note --------------------------------- Q: Why not just use information_schema? A: Because the columns and layout of information_schema is strictly defined by the SQL standard. This prevents it from covering all PostgreSQL objects, or from covering the existing objects adequately to replicate a CREATE statement. As examples, there is no "types" table in information_schema, and the "constraints" table assumes that constraint names are universally unique instead of table-unique as they are in PG. The View Definitions ---------------------------------- The column definitions of the views are below. Please examine them carefully. Currently, the following views are incomplete and thus not included: pg_functions pg_function_parameters pg_types pg_acl_modes Column | Type | -------------+------+----------- object_type | text | mode | text | granted | text | description | text | pg_all_aggregates Column | Type | Modifiers -----------------------+---------+----------- schema_name | name | aggregate_name | name | input_type_schema | name | input_type | name | output_type_schema | name | output_type | name | initial_value | text | trans_function_schema | name | trans_function_name | name | final_function_schema | name | final_function_name | name | is_system_aggregate | boolean | owner | name | pg_user_aggregates Column | Type | Modifiers -----------------------+---------+----------- schema_name | name | aggregate_name | name | input_type_schema | name | input_type | name | output_type_schema | name | output_type | name | initial_value | text | trans_function_schema | name | trans_function_name | name | final_function_schema | name | final_function_name | name | owner | name | pg_all_casts Column | Type | --------------------+---------+----------- source_schema | name | source_type | name | target_schema | name | target_type | name | function_schema | name | function_name | name | function_arguments | text | context | text | is_system_cast | boolean | pg_user_casts Column | Type | --------------------+---------+----------- source_schema | name | source_type | name | target_schema | name | target_type | name | function_schema | name | function_name | name | function_arguments | text | context | text | pg_all_conversions Column | Type | Modifiers ----------------------+---------+----------- schema_name | name | conversion_name | name | source_encoding | name | destination_encoding | name | is_default | boolean | function_schema | name | function_name | name | is_system_conversion | boolean | owner | name | pg_user_conversions Column | Type | ----------------------+---------+----------- schema_name | name | conversion_name | name | source_encoding | name | destination_encoding | name | is_default | boolean | function_schema | name | function_name | name | owner | name | pg_databases Column | Type | --------------------+---------+----------- database_name | name | encoding | name | default_tablespace | name | database_config | text[] | is_template | boolean | can_connect | boolean | owner | name | pg_database_config Column | Type | --------------------+------+----------- database_name | name | config_variable | text | config_value | text | pg_all_foreign_key_indexes, pg_user_foreign_key_indexes Column | Type | Modifiers ---------------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | num_columns | integer | num_indexed_columns | integer | index_name | name | pg_all_foreign_keys, pg_user_foreign_keys Column | Type | -----------------------------+---------+----------- foreign_key_schema_name | name | foreign_key_table_name | name | foreign_key_constraint_name | name | foreign_key_table_oid | oid | foreign_key_columns | name[] | key_schema_name | name | key_table_name | name | key_constraint_name | name | key_table_oid | oid | key_index_name | name | key_columns | name[] | match_type | text | on_delete | text | on_update | text | is_deferrable | boolean | is_deferred | boolean | pg_all_foreign_key_columns, pg_user_foreign_key_columns Column | Type | -----------------------------+---------+----------- foreign_key_schema_name | name | foreign_key_table_name | name | foreign_key_constraint_name | name | foreign_key_table_oid | oid | foreign_key_column | name | column_position | integer | key_schema_name | name | key_table_name | name | key_table_oid | oid | key_column | name | pg_all_grants, pg_user_grants Column | Type | --------------+---------+----------- object_type | name | object_oid | oid | schema_name | name | object_name | name | object_args | text | owner | name | grantor | text | grantee | text | is_group | boolean | privilege | text | grant_option | boolean | pg_groups Column | Type | ------------+---------+----------- group_name | name | gid | integer | pg_groups_users Column | Type | ------------+------+----------- group_name | name | user_name | name | pg_all_index_columns, pg_user_index_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | index_name | name | column_name | name | NULL if an expression column_position | integer | 1..n opclass_schema | name | opclass_name | name | definition | text | expression or column name pg_all_indexes Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | index_name | name | tablespace | name | index_method | name | num_columns | smallint | is_primary_key | boolean | is_unique | boolean | is_clustered | boolean | is_expression | boolean | is_partial | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | is_system_table | boolean | table_oid | oid | predicate | text | definition | text | owner | name | comment | text | pg_user_indexes Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | index_name | name | tablespace | name | index_method | name | num_columns | smallint | is_primary_key | boolean | is_unique | boolean | is_clustered | boolean | is_expression | boolean | is_partial | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | predicate | text | definition | text | owner | name | comment | text | pg_all_relation_columns, pg_user_relation_columns Column | Type | ---------------+---------+----------- schema_name | name | relation_name | name | column_name | name | relation_oid | oid | column_number | integer | is_view | boolean | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_relation_column_type_info, pg_user_relation_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | relation_name | name | column_name | name | relation_oid | oid | column_number | integer | is_view | boolean | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_relations Column | Type | --------------------+---------------+----------- schema_name | name | relation_name | name | is_system_relation | boolean | is_temporary | boolean | is_view | boolean | relation_oid | oid | owner | name | comment | text | pg_user_relations Column | Type | --------------------+---------------+----------- schema_name | name | relation_name | name | is_temporary | boolean | is_view | boolean | owner | name | comment | text | pg_all_rules, pg_user_rules Column | Type | ---------------+---------+----------- schema_name | name | relation_name | name | rule_name | name | rule_event | text | is_instead | boolean | condition | text | action | text | pg_all_schemas Column | Type | ---------------------+---------+----------- schema_name | name | is_system_schema | boolean | is_temporary_schema | boolean | owner | name | comment | text | pg_user_schemas Column | Type | ---------------------+---------+----------- schema_name | name | is_temporary_schema | boolean | owner | name | comment | text | pg_all_schema_contents, pg_user_schema_contents Column | Type | -------------+------+----------- schema_name | name | owner | name | object_type | name | object_name | name | object_args | text | pg_all_sequences Column | Type | --------------------+---------+----------- schema_name | name | sequence_name | name | is_system_sequence | boolean | is_temporary | boolean | pg_user_sequences Column | Type | --------------------+---------+----------- schema_name | name | sequence_name | name | is_temporary | boolean | pg_all_table_columns, pg_user_table_columns Column | Type | ---------------+---------+----------- schema_name | name | table_name | name | column_name | name | table_oid | oid | column_number | integer | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_table_column_type_info, pg_user_table_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | table_name | name | column_name | name | table_oid | oid | column_number | integer | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_table_constraints, pg_user_table_constraints Column | Type | -----------------+------+----------- schema_name | name | table_name | name | constraint_name | name | constraint_type | text | table_oid | oid | definition | text | pg_all_table_constraint_columns, pg_user_table_constraint_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | column_name | name | column_position | integer | constraint_type | text | table_oid | oid | pg_all_unique_constraint_columns, pg_user_unique_constraint_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | is_primary_key | boolean | column_name | name | column_position | integer | table_oid | oid | pg_all_primary_key_columns, pg_user_primary_key_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | column_name | name | column_position | integer | table_oid | oid | pg_all_table_check_constraints, pg_user_table_check_constraints Column | Type | -----------------+--------+----------- schema_name | name | table_name | name | constraint_name | name | table_oid | oid | columns | name[] | predicate | text | pg_all_table_inheritance, pg_user_table_inheritance Column | Type | -------------------+---------+----------- schema_name | name | table_name | name | table_oid | oid | descendent_schema | name | descendent_table | name | descendent_oid | oid | ordinal_position | integer | pg_all_table_storage, pg_user_table_storage Column | Type | ------------------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | is_temporary | boolean | num_indexes | integer | clustered_on | name | estimated_rows | real | estimated_index_rows | real | estimated_total_mb | numeric | estimated_data_mb | numeric | estimated_main_mb | numeric | estimated_external_mb | numeric | estimated_index_mb | numeric | index_tablespaces | name[] | pg_all_tables Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | with_oids | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | includes toast but not indexes has_toast_table | boolean | has_descendents | boolean | is_system_table | boolean | is_temporary | boolean | table_oid | oid | owner | name | comment | text | pg_user_tables Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | with_oids | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | includes toast but not indexes has_toast_table | boolean | has_descendents | boolean | is_temporary | boolean | owner | name | comment | text | pg_tablespaces Column | Type | -------------+---------+----------- tablespace | name | location | text | is_writable | boolean | owner | name | comment | text | pg_tablespace_usage Column | Type | ------------+------+----------- tablespace | name | database | name | pg_all_tablespace_contents, pg_user_tablespace_contents Column | Type | ---------------+---------------+----------- tablespace | name | object_type | text | owner | name | object_schema | name | object_name | name | estimated_mb | numeric(12,1) | pg_all_triggers, pg_user_triggers Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | trigger_name | name | function_schema | name | function_name | name | function_args | text[] | function_oid | oid | before | boolean | for_each_row | boolean | on_insert | boolean | on_delete | boolean | on_update | boolean | enabled | boolean | definition | text | comment | text | pg_users Column | Type | ------------------------+--------------------------+----------- user_name | name | uid | integer | create_datebase | boolean | create_user | boolean | superuser | boolean | update_system_catalogs | boolean | password_expires | timestamp with time zone | pg_user_config Column | Type | -----------------+------+----------- user_name | name | config_variable | text | config_value | text | pg_all_view_columns, pg_user_view_columns Column | Type | ---------------+---------+----------- schema_name | name | view_name | name | column_name | name | view_oid | oid | column_number | integer | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_view_column_type_info, pg_user_view_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | view_name | name | column_name | name | view_oid | oid | column_number | integer | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_views Column | Type | -----------------+---------------+----------- schema_name | name | view_name | name | is_insertable | boolean | is_updateable | boolean | is_deleteable | boolean | definition | text | is_system_view | boolean | view_oid | oid | owner | name | comment | text | pg_user_views Column | Type | -----------------+---------------+----------- schema_name | name | view_name | name | is_insertable | boolean | is_updateable | boolean | is_deleteable | boolean | definition | text | owner | name | comment | text | -- Josh Berkus Aglio Database Solutions San Francisco
pgsql-hackers by date: