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@agliodbs.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@proximity.com.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
Hi Josh,
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: 05 May 2005 05:38
To: PostgreSQL-development
Subject: [HACKERS] Views, views, views! (long)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.
6.3/6.4 for pgAdmin. 7.2 is positively recent :-)
However, I cannot see us using these views for a couple of reasons:
1) Users may drop or change them, something they cannot do easily with system catalogs. Yes, I know this is then their fault, but it will stop admin tools that use the views from working even though the database itself is actually OK. If a user does manage to alter a system catalog, they are far more likely to see breakage in other places as well.
2) Catalog changes are infrequent but significant in other areas when they do occur. Consider the 7.2 -> 7.3 namespace changes. Regardless of using views or the catalogs we still have significant work to do to support namespaces. Other smaller changes will likely require GUI updates or internal code changes that will also be necessary whether using views or the catalogs.
3) One example of a catalog change that has caused a number of bug reports for us is the removal of pg_database.datpath. Whilst your views could have prevented the error itself, we would still have had to modify pgAdmin to prevent it displaying the path on newer servers as it is completely meaningless - however, do you proprose that your views would have retained this column forever? If so, it seems they could get very messy, and cluttered with notes in the docs telling users that a given column was only relevant up till version X.
After 8 or so years of dealing with problem, I'm really don't think we would gain anything worthwhile from the views you propose. However, I'm sure some end users may well find them useful, so I do not believe your work is in vain.
Regards, Dave.
Import Notes
Resolved by subject fallback
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@sai.msu.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@agliodbs.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
"Dave Page" <dpage@vale-housing.co.uk> writes:
3) One example of a catalog change that has caused a number of bug
reports for us is the removal of pg_database.datpath. Whilst your
views could have prevented the error itself, we would still have had
to modify pgAdmin to prevent it displaying the path on newer servers
as it is completely meaningless - however, do you proprose that your
views would have retained this column forever?
It's worth noting that we could have left datpath in the catalogs.
Its removal was deliberate: I *wanted* to break any applications
that were looking at it, to ensure that they got updated. If we'd
left it there but nonfunctional, we'd have had subtle bugs in apps
instead of obvious ones. We will have the same tradeoff to make
with respect to these views, anytime the underlying reality changes
in incompatible ways ... which it surely will.
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
This is the kind of thing that support engineers love. Instead of having to email a bunch of complex SQL statements to a customer that is having trouble, they can verbally walk through a system catalog type query.
My proprietary database has a very simple system catalog that very human readable, which saves time and improves accuracy. So, it isn't just end users, it is those that touch them as well.
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, May 05, 2005 11:11 AM
Cc: PostgreSQL-development
Subject: Re: Views, views, views! (long)
[...snip...]
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
Import Notes
Resolved by subject fallback
Why not tack on the missing functionality to the INFORMATION_SCHEMA views?
A couple of new tables and foreign keys should do it, n'est ce pas?
Show quoted text
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Wednesday, May 04, 2005 10:02 PM
To: Tom Lane
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Views, views, views! (long)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---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Import Notes
Resolved by subject fallback
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
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Thursday, May 05, 2005 10:49 AM
To: Andreas Pflug
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Views, views, views! (long)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,
COLUMN_PRIVILEGES Has one row for each column level permission granted
to or by the current user
TABLE_PRIVILEGES Has one row for each table level permission granted to
or by the current user
comments,
These tables contain commentary information:
CREATE TABLE SQL_FEATURES
CREATE TABLE SQL_IMPLEMENTATION_INFO
CREATE TABLE SQL_LANGUAGES
CREATE TABLE SQL_SIZING_PROFILES
CREATE VIEW SQL_FEATURES
CREATE VIEW SQL_IMPLEMENTATION_INFO
CREATE VIEW SQL_IMPL_INFO
CREATE VIEW SQL_PACKAGES
CREATE VIEW SQL_SIZING
CREATE VIEW SQL_SIZING_PROFILES
CREATE VIEW SQL_SIZING_PROFS
And you can tack on more tables as needed.
object owners,
Ownership stuff is contained in these:
CREATE TABLE SCHEMATA
CREATE VIEW ASSERTIONS
CREATE VIEW ATTRIBUTES
CREATE VIEW CHARACTER_SETS
CREATE VIEW CHECK_CONSTRAINTS
CREATE VIEW COLLATIONS
CREATE VIEW COLUMNS
CREATE VIEW COLUMN_DOMAIN_USAGE
CREATE VIEW COLUMN_UDT_USAGE
CREATE VIEW CONSTRAINT_COLUMN_USAGE
CREATE VIEW CONSTRAINT_TABLE_USAGE
CREATE VIEW DIRECT_SUPERTABLES
CREATE VIEW DIRECT_SUPERTYPES
CREATE VIEW DOMAINS
CREATE VIEW DOMAIN_CONSTRAINTS
CREATE VIEW DOMAIN_UDT_USAGE
CREATE VIEW KEY_COLUMN_USAGE
CREATE VIEW METHOD_SPECIFICATIONS
CREATE VIEW METHOD_SPECIFICATION_PARAMETERS
CREATE VIEW PARAMETERS
CREATE VIEW REFERENTIAL_CONSTRAINTS
CREATE VIEW ROUTINES
CREATE VIEW ROUTINE_COLUMN_USAGE
CREATE VIEW ROUTINE_TABLE_USAGE
CREATE VIEW SCHEMATA
CREATE VIEW SCHEMATA_S
CREATE VIEW TABLES
CREATE VIEW TABLE_CONSTRAINTS
CREATE VIEW TRANSFORMS
CREATE VIEW TRANSLATIONS
CREATE VIEW TRIGGERED_UPDATE_COLUMNS
CREATE VIEW TRIGGERS
CREATE VIEW TRIGGER_COLUMN_USAGE
CREATE VIEW TRIGGER_TABLE_USAGE
CREATE VIEW USER_DEFINED_TYPES
CREATE VIEW VIEWS
CREATE VIEW VIEW_COLUMN_USAGE
CREATE VIEW VIEW_TABLE_USAGE
If you need more than what is here, create an ownership table that is
connected to the others using key relationships.
functions,
ROUTINES Lists one row for each stored procedure or user-defined
function
ROUTINE_COLUMNS Contains one row for each column returned by any
table-valued functions
types,
DOMAIN_CONSTRAINTS Lists the user-defined datatypes that have rules
bound to them
DOMAINS Lists the user-defined datatypes
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.
Create a new relation that is tied to the table of interest with a key.
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?
Imagine (if you will) 100 different database systems, each of which has
a different way to access the system tables, and each of which changes
the tables whenever they want. If this picture is firm in mind, then
the absolute necessity of INFORMATION_SCHEMA will crystallize.
Whether or not OID values are published pales in comparison. Of course,
if they do become visible, they should not pollute the
INFORMATION_SCHEMA.
IMO-YMMV.
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco---------------------------(end of
broadcast)---------------------------
Show quoted text
TIP 8: explain analyze is your friend
Import Notes
Resolved by subject fallback
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
