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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
