Thread: Views, views, views! (long)
PG hackers, AndrewSN, Jim Nasby, Elein and I have been working for the last couple of months on a new set of system views for PostgreSQL. (primarily Andrew, who did the lion's share of the work and came up with many clever SQL workarounds) We'd like to include them in the 8.1 release, so we're going to post most of the definitions for your feedback now. Let me summarize: Goals of the New System Views --------------------------------------- 1. To be easily human-readable, unlike the system tables. 2. To provide a consistent API to PostgreSQL object definitions which will seldom (if ever) be changed, only added to. 3. To provide queryable definitions for all PostgreSQL objects. In more detail: 1. The current system tables are designed around performance and code requirements, and as such are optimized for code access, not comprehensability. Column names are obscure, special system data types are used, and everything is OIDs and typids. This is perfect for our code, but too many user-space applications are using these tables for comfort. Our first system views (pg_tables, for example) only went halfway in providing a user-friendly interface. So the new system views have the following requirements: a) all view and column names are as explicit and as readable as possible (e.g. "type_schema_name", not "typnsname") b) OIDs, typids, and other system codes are avoided wherever possible in preference to full object names c) In most places, "system" objects are segregated from "user" objects, e.g. pg_user_indexes 2. One of the issues with user applications querying the system tables is that they can be subject to significant changes from version to version. This has kept the pgAdmin and phpPgAdmin teams busy since 7.2, and means that GUI tools which fall out of maintenance (like Xpg) soon stop working. This is easily remedied through a set of system views which will remain consistent regardless of changes in the underlying system tables. This has the beneficial effect of giving us more freedom to make changes to the system tables. Further, we discovered when we proposed dropping the old system views that once these views are created we're stuck with them for several years, if not forever; people's not-easily-recoded tools rely on them. d) Columns may be added to the system views, but never dropped or changed in incompatible ways. Likewise,views will be added but not dropped or renamed. e) Users and app developers should be actively encouraged to use the system views rather than the system tablesin the documentation. f) Existing projects, features and add-ons, where appropriate, should gradually be shifted to use the systemviews to minimize version maintenance. 3. The new system views (unlike, for example, \d) are designed to be a SQL interface to system objects. This means that: g) All views are as normalized as possible, using child views rather than arrays, and providing keys and consistentjoin columns. h) Each view or set of views provides all of the data required to replicate the appropriate CREATE statement. i) Column names are designed to be universal to a particular type of data, where this does not cause duplication. For example, pg_user_tables has "schema_name" rather than "table_schema". This was done to make joins easier (i.e. USING, NATURAL JOIN) Additional assumptions we worked with include: j) all view names are plural in order to prevent conflict with systemtables. k) no procedural languages are used, as we don't want to make PLs mandatory. Currently everything is SQL (really!)and we may move a few functions to a C library eventually. l) internal functions required for the systemviews are named using a "_pg_" convention. m) We will be offering a "back-patch" for 7.4 and 8.0 via pgFoundry. What We Need From Hackers -------------------------------------- (other than patch approval, that is) As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. As such, we really can't afford to do major refactoring of the column names and structure once they're released. So it's really, really, important for everyone on hackers to look over the definitions below and find stuff that we've missed or doesn't make any sense. Also, we'd like to know about 8.1 changes that affect these views. There are two additional other questions to discuss that our team as not settled: I) Should the new views be part of /contrib before they become part of the main source? II) Should the new views be in their own schema? This would make them easier to manage for DBAs who want to restrict access or dump them, but would add a second "system" schema to the template. Information_Schema note --------------------------------- Q: Why not just use information_schema? A: Because the columns and layout of information_schema is strictly defined by the SQL standard. This prevents it from covering all PostgreSQL objects, or from covering the existing objects adequately to replicate a CREATE statement. As examples, there is no "types" table in information_schema, and the "constraints" table assumes that constraint names are universally unique instead of table-unique as they are in PG. The View Definitions ---------------------------------- The column definitions of the views are below. Please examine them carefully. Currently, the following views are incomplete and thus not included: pg_functions pg_function_parameters pg_types pg_acl_modes Column | Type | -------------+------+----------- object_type | text | mode | text | granted | text | description | text | pg_all_aggregates Column | Type | Modifiers -----------------------+---------+----------- schema_name | name | aggregate_name | name | input_type_schema | name | input_type | name | output_type_schema | name | output_type | name | initial_value | text | trans_function_schema | name | trans_function_name | name | final_function_schema | name | final_function_name | name | is_system_aggregate | boolean | owner | name | pg_user_aggregates Column | Type | Modifiers -----------------------+---------+----------- schema_name | name | aggregate_name | name | input_type_schema | name | input_type | name | output_type_schema | name | output_type | name | initial_value | text | trans_function_schema | name | trans_function_name | name | final_function_schema | name | final_function_name | name | owner | name | pg_all_casts Column | Type | --------------------+---------+----------- source_schema | name | source_type | name | target_schema | name | target_type | name | function_schema | name | function_name | name | function_arguments | text | context | text | is_system_cast | boolean | pg_user_casts Column | Type | --------------------+---------+----------- source_schema | name | source_type | name | target_schema | name | target_type | name | function_schema | name | function_name | name | function_arguments | text | context | text | pg_all_conversions Column | Type | Modifiers ----------------------+---------+----------- schema_name | name | conversion_name | name | source_encoding | name | destination_encoding | name | is_default | boolean | function_schema | name | function_name | name | is_system_conversion | boolean | owner | name | pg_user_conversions Column | Type | ----------------------+---------+----------- schema_name | name | conversion_name | name | source_encoding | name | destination_encoding | name | is_default | boolean | function_schema | name | function_name | name | owner | name | pg_databases Column | Type | --------------------+---------+----------- database_name | name | encoding | name | default_tablespace | name | database_config | text[] | is_template | boolean | can_connect | boolean | owner | name | pg_database_config Column | Type | --------------------+------+----------- database_name | name | config_variable | text | config_value | text | pg_all_foreign_key_indexes, pg_user_foreign_key_indexes Column | Type | Modifiers ---------------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | num_columns | integer | num_indexed_columns | integer | index_name | name | pg_all_foreign_keys, pg_user_foreign_keys Column | Type | -----------------------------+---------+----------- foreign_key_schema_name | name | foreign_key_table_name | name | foreign_key_constraint_name | name | foreign_key_table_oid | oid | foreign_key_columns | name[] | key_schema_name | name | key_table_name | name | key_constraint_name | name | key_table_oid | oid | key_index_name | name | key_columns | name[] | match_type | text | on_delete | text | on_update | text | is_deferrable | boolean | is_deferred | boolean | pg_all_foreign_key_columns, pg_user_foreign_key_columns Column | Type | -----------------------------+---------+----------- foreign_key_schema_name | name | foreign_key_table_name | name | foreign_key_constraint_name | name | foreign_key_table_oid | oid | foreign_key_column | name | column_position | integer | key_schema_name | name | key_table_name | name | key_table_oid | oid | key_column | name | pg_all_grants, pg_user_grants Column | Type | --------------+---------+----------- object_type | name | object_oid | oid | schema_name | name | object_name | name | object_args | text | owner | name | grantor | text | grantee | text | is_group | boolean | privilege | text | grant_option | boolean | pg_groups Column | Type | ------------+---------+----------- group_name | name | gid | integer | pg_groups_users Column | Type | ------------+------+----------- group_name | name | user_name | name | pg_all_index_columns, pg_user_index_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | index_name | name | column_name | name | NULL if an expression column_position | integer | 1..n opclass_schema | name | opclass_name | name | definition | text | expression or column name pg_all_indexes Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | index_name | name | tablespace | name | index_method | name | num_columns | smallint | is_primary_key | boolean | is_unique | boolean | is_clustered | boolean | is_expression | boolean | is_partial | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | is_system_table | boolean | table_oid | oid | predicate | text | definition | text | owner | name | comment | text | pg_user_indexes Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | index_name | name | tablespace | name | index_method | name | num_columns | smallint | is_primary_key | boolean | is_unique | boolean | is_clustered | boolean | is_expression | boolean | is_partial | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | predicate | text | definition | text | owner | name | comment | text | pg_all_relation_columns, pg_user_relation_columns Column | Type | ---------------+---------+----------- schema_name | name | relation_name | name | column_name | name | relation_oid | oid | column_number | integer | is_view | boolean | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_relation_column_type_info, pg_user_relation_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | relation_name | name | column_name | name | relation_oid | oid | column_number | integer | is_view | boolean | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_relations Column | Type | --------------------+---------------+----------- schema_name | name | relation_name | name | is_system_relation | boolean | is_temporary | boolean | is_view | boolean | relation_oid | oid | owner | name | comment | text | pg_user_relations Column | Type | --------------------+---------------+----------- schema_name | name | relation_name | name | is_temporary | boolean | is_view | boolean | owner | name | comment | text | pg_all_rules, pg_user_rules Column | Type | ---------------+---------+----------- schema_name | name | relation_name | name | rule_name | name | rule_event | text | is_instead | boolean | condition | text | action | text | pg_all_schemas Column | Type | ---------------------+---------+----------- schema_name | name | is_system_schema | boolean | is_temporary_schema | boolean | owner | name | comment | text | pg_user_schemas Column | Type | ---------------------+---------+----------- schema_name | name | is_temporary_schema | boolean | owner | name | comment | text | pg_all_schema_contents, pg_user_schema_contents Column | Type | -------------+------+----------- schema_name | name | owner | name | object_type | name | object_name | name | object_args | text | pg_all_sequences Column | Type | --------------------+---------+----------- schema_name | name | sequence_name | name | is_system_sequence | boolean | is_temporary | boolean | pg_user_sequences Column | Type | --------------------+---------+----------- schema_name | name | sequence_name | name | is_temporary | boolean | pg_all_table_columns, pg_user_table_columns Column | Type | ---------------+---------+----------- schema_name | name | table_name | name | column_name | name | table_oid | oid | column_number | integer | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_table_column_type_info, pg_user_table_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | table_name | name | column_name | name | table_oid | oid | column_number | integer | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_table_constraints, pg_user_table_constraints Column | Type | -----------------+------+----------- schema_name | name | table_name | name | constraint_name | name | constraint_type | text | table_oid | oid | definition | text | pg_all_table_constraint_columns, pg_user_table_constraint_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | column_name | name | column_position | integer | constraint_type | text | table_oid | oid | pg_all_unique_constraint_columns, pg_user_unique_constraint_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | is_primary_key | boolean | column_name | name | column_position | integer | table_oid | oid | pg_all_primary_key_columns, pg_user_primary_key_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | column_name | name | column_position | integer | table_oid | oid | pg_all_table_check_constraints, pg_user_table_check_constraints Column | Type | -----------------+--------+----------- schema_name | name | table_name | name | constraint_name | name | table_oid | oid | columns | name[] | predicate | text | pg_all_table_inheritance, pg_user_table_inheritance Column | Type | -------------------+---------+----------- schema_name | name | table_name | name | table_oid | oid | descendent_schema | name | descendent_table | name | descendent_oid | oid | ordinal_position | integer | pg_all_table_storage, pg_user_table_storage Column | Type | ------------------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | is_temporary | boolean | num_indexes | integer | clustered_on | name | estimated_rows | real | estimated_index_rows | real | estimated_total_mb | numeric | estimated_data_mb | numeric | estimated_main_mb | numeric | estimated_external_mb | numeric | estimated_index_mb | numeric | index_tablespaces | name[] | pg_all_tables Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | with_oids | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | includes toast but not indexes has_toast_table | boolean | has_descendents | boolean | is_system_table | boolean | is_temporary | boolean | table_oid | oid | owner | name | comment | text | pg_user_tables Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | with_oids | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | includes toast but not indexes has_toast_table | boolean | has_descendents | boolean | is_temporary | boolean | owner | name | comment | text | pg_tablespaces Column | Type | -------------+---------+----------- tablespace | name | location | text | is_writable | boolean | owner | name | comment | text | pg_tablespace_usage Column | Type | ------------+------+----------- tablespace | name | database | name | pg_all_tablespace_contents, pg_user_tablespace_contents Column | Type | ---------------+---------------+----------- tablespace | name | object_type | text | owner | name | object_schema | name | object_name | name | estimated_mb | numeric(12,1) | pg_all_triggers, pg_user_triggers Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | trigger_name | name | function_schema | name | function_name | name | function_args | text[] | function_oid | oid | before | boolean | for_each_row | boolean | on_insert | boolean | on_delete | boolean | on_update | boolean | enabled | boolean | definition | text | comment | text | pg_users Column | Type | ------------------------+--------------------------+----------- user_name | name | uid | integer | create_datebase | boolean | create_user | boolean | superuser | boolean | update_system_catalogs | boolean | password_expires | timestamp with time zone | pg_user_config Column | Type | -----------------+------+----------- user_name | name | config_variable | text | config_value | text | pg_all_view_columns, pg_user_view_columns Column | Type | ---------------+---------+----------- schema_name | name | view_name | name | column_name | name | view_oid | oid | column_number | integer | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_view_column_type_info, pg_user_view_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | view_name | name | column_name | name | view_oid | oid | column_number | integer | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_views Column | Type | -----------------+---------------+----------- schema_name | name | view_name | name | is_insertable | boolean | is_updateable | boolean | is_deleteable | boolean | definition | text | is_system_view | boolean | view_oid | oid | owner | name | comment | text | pg_user_views Column | Type | -----------------+---------------+----------- schema_name | name | view_name | name | is_insertable | boolean | is_updateable | boolean | is_deleteable | boolean | definition | text | owner | name | comment | text | -- Josh Berkus Aglio Database Solutions San Francisco
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 userdoes 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 smallerchanges will likely require GUI updates or internal code changes that will also be necessary whether using views orthe 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 toprevent it displaying the path on newer servers as it is completely meaningless - however, do you proprose that your viewswould have retained this column forever? If so, it seems they could get very messy, and cluttered with notes in thedocs 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 youpropose. However, I'm sure some end users may well find them useful, so I do not believe your work is in vain. Regards, Dave.
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 acustomer 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
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? > -----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? > > http://www.postgresql.org/docs/faq
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)--------------------------- > TIP 8: explain analyze is your friend
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
On Thu, May 05, 2005 at 14:26:56 +0400, Oleg Bartunov <oleg@sai.msu.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.
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 2005-05-05, Peter Eisentraut <peter_e@gmx.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@agliodbs.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@agliodbs.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@fetter.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@turnstep.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@turnstep.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@tm.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@varlena.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@agliodbs.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
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers- > owner@postgresql.org] On Behalf Of Andrew - Supernews > Sent: Thursday, May 05, 2005 4:55 PM > To: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Views, views, views! (long) > > On 2005-05-05, Josh Berkus <josh@agliodbs.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. Equally interesting would be: How many duplicate keys do I have? An interesting database option might be: For any foreign key, if there is no existing inversion entry index then build one. Another interesting database option might be: Check the system catalogs for duplicate indexes and remove them if there are any two completely identical (in columns, asc/desc and type). > 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. Obviously, this is a clear oversight by the standards committee. PostgreSQL is clearly not the only database with this important need. > -- > Andrew, Supernews > http://www.supernews.com - individual and corporate NNTP services > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
INFORMATION_SCHEMA is what should be exposed to the end-users of PostgreSQL. Pg_schema (for lack of a better name for internal metadata) can be useful as well for all sorts of special purposes. Probably, INFORMATION_SCHEMA (as designed by the SQL Standards committee) does not need to worry about table inheritance, for example. The only danger I see is Pg_schema forging ahead and INFORMATION_SCHEMA lacking some crucial piece of data. Pg_schema should NOT be the end-user database catalog. I would even advocate hiding it from anything but internal processes. Anything that is totally crucial as a piece of functionality should be added to INFORMATION_SCHEMA as a kind of enhancement, if it cannot be stored in that schema as-is. I suspect that: 1. There is not a whole lot of stuff that cannot be directly stored in the INFORMATION_SCHEMA location without modifying it. 2. Almost all of the information that cannot fit will be useful to other database systems as well, and should be suggested to the ANSI/ISO committee. Since INFORMATION_SCHEMA is a very new idea (only two adopters that I know of so far) I expect it will need to grow and PostgreSQL could be one of the contributors. Whether the enhancements are accepted or not, it would be good to at least attempt to get them noticed. 3. Of the fragment that does not fall under 1 and 2, it should be a carefully documented extension that lives in the same place (INFORMATION_SCHEMA) so that we know where to look to find it. > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers- > owner@postgresql.org] On Behalf Of elein > Sent: Thursday, May 05, 2005 4:55 PM > To: PostgreSQL-development > Cc: Josh Berkus; Peter Eisentraut > Subject: Re: [HACKERS] Views, views, views! (long) > > 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@varlena.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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Dann, > 1. There is not a whole lot of stuff that cannot be directly stored in > the INFORMATION_SCHEMA location without modifying it. See Andrew's post. There is a whole lot of stuff not covered by I_S in a way that is useful to PGSQL users. Also this would require making information_schema part of the default user path. > 2. Almost all of the information that cannot fit will be useful to > other database systems as well, and should be suggested to the ANSI/ISO > committee. Since INFORMATION_SCHEMA is a very new idea (only two > adopters that I know of so far) I expect it will need to grow and > PostgreSQL could be one of the contributors. Whether the enhancements > are accepted or not, it would be good to at least attempt to get them > noticed. I like that idea, actually. However, there's not a whelk's chance in a supernova of our suggestions getting read, let alone accepted. But we could make the effort. You volunteering? > 3. Of the fragment that does not fall under 1 and 2, it should be a > carefully documented extension that lives in the same place > (INFORMATION_SCHEMA) so that we know where to look to find it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
> -----Original Message----- > From: Josh Berkus [mailto:josh@agliodbs.com] > Sent: Thursday, May 05, 2005 5:35 PM > To: Dann Corbit > Cc: elein; PostgreSQL-development; Peter Eisentraut > Subject: Re: [HACKERS] Views, views, views! (long) > > Dann, > > > 1. There is not a whole lot of stuff that cannot be directly stored in > > the INFORMATION_SCHEMA location without modifying it. > > See Andrew's post. There is a whole lot of stuff not covered by I_S in a > way > that is useful to PGSQL users. Also this would require making > information_schema part of the default user path. It is supposed to be part of the default user path for all users of the database. > > 2. Almost all of the information that cannot fit will be useful to > > other database systems as well, and should be suggested to the ANSI/ISO > > committee. Since INFORMATION_SCHEMA is a very new idea (only two > > adopters that I know of so far) I expect it will need to grow and > > PostgreSQL could be one of the contributors. Whether the enhancements > > are accepted or not, it would be good to at least attempt to get them > > noticed. > > I like that idea, actually. > > However, there's not a whelk's chance in a supernova of our suggestions > getting read, let alone accepted. But we could make the effort. You > volunteering? It's probably like ANSI/ISO C and C++ committees -- they only listen if you actually show up to the meetings. I doubt ifI could pay to go and attend in person. Maybe some of the Redhat people can, since they maintain the Redhat database. For sure, if the DB/2 or Oracle or MS or ... people hear about it and see that it is a good idea, they will push for it also. Otherwise, the competitors who adopt it will have a competitive advantage. > > 3. Of the fragment that does not fall under 1 and 2, it should be a > > carefully documented extension that lives in the same place > > (INFORMATION_SCHEMA) so that we know where to look to find it. > > -- > --Josh > > Josh Berkus > Aglio Database Solutions > San Francisco
On Thu, May 05, 2005 at 05:24:43PM -0700, Dann Corbit wrote: > INFORMATION_SCHEMA is what should be exposed to the end-users of > PostgreSQL. > > Pg_schema (for lack of a better name for internal metadata) can be > useful as well for all sorts of special purposes. Probably, > INFORMATION_SCHEMA (as designed by the SQL Standards committee) does not > need to worry about table inheritance, for example. > > The only danger I see is Pg_schema forging ahead and INFORMATION_SCHEMA > lacking some crucial piece of data. > > Pg_schema should NOT be the end-user database catalog. I would even > advocate hiding it from anything but internal processes. Anything that > is totally crucial as a piece of functionality should be added to > INFORMATION_SCHEMA as a kind of enhancement, if it cannot be stored in > that schema as-is. > > I suspect that: > 1. There is not a whole lot of stuff that cannot be directly stored in > the INFORMATION_SCHEMA location without modifying it. On close examination of PostgreSQL's extensibility capabilities will show you that this is actually not the case. > 2. Almost all of the information that cannot fit will be useful to > other database systems as well, and should be suggested to the ANSI/ISO > committee. Since INFORMATION_SCHEMA is a very new idea (only two > adopters that I know of so far) I expect it will need to grow and > PostgreSQL could be one of the contributors. Whether the enhancements > are accepted or not, it would be good to at least attempt to get them > noticed. The SQL committee is s l o w l y catching up to the OR capability that was designed into Postgres back in '86. As other databases finally get around to adopting similar models the items are added to the standards. In many ways, SQL2003 is finally catching up with some of what Postgres was designed to do. We already have been influencing the committee for years indirectly. > 3. Of the fragment that does not fall under 1 and 2, it should be a > carefully documented extension that lives in the same place > (INFORMATION_SCHEMA) so that we know where to look to find it. This is the bulk of what we are working on. Some is duplicated work with the information schema but it usually has more information or context. --elein elein@varlena.com > > > -----Original Message----- > > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers- > > owner@postgresql.org] On Behalf Of elein > > Sent: Thursday, May 05, 2005 4:55 PM > > To: PostgreSQL-development > > Cc: Josh Berkus; Peter Eisentraut > > Subject: Re: [HACKERS] Views, views, views! (long) > > > > 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@varlena.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. > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org >
> 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@familyhealth.com.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
"Dann Corbit" <DCorbit@connx.com> writes:
> 2. Almost all of the information that cannot fit will be useful to
> other database systems as well, and should be suggested to the ANSI/ISO
> committee. Since INFORMATION_SCHEMA is a very new idea (only two
> adopters that I know of so far) I expect it will need to grow and
> PostgreSQL could be one of the contributors.
[ raised eyebrow... ] INFORMATION_SCHEMA is in the SQL92 spec.
If only two systems have adopted it in the last 13 years, it's
a failure and the SQL committee is unlikely to want to spend any
time on it. In any case, they just issued a spec and so there is
very unlikely to be another update for another five or so years.
regards, tom lane
>>2. Almost all of the information that cannot fit will be useful to >>other database systems as well, and should be suggested to the ANSI/ISO >>committee. Since INFORMATION_SCHEMA is a very new idea (only two >>adopters that I know of so far) I expect it will need to grow and >>PostgreSQL could be one of the contributors. > > > [ raised eyebrow... ] INFORMATION_SCHEMA is in the SQL92 spec. > If only two systems have adopted it in the last 13 years, it's > a failure and the SQL committee is unlikely to want to spend any > time on it. In any case, they just issued a spec and so there is > very unlikely to be another update for another five or so years. INFORMATION_SCHEMA is also in MySQL 5, so that makes 3 :D Chris
On Thursday 05 May 2005 19:17, Andrew - Supernews wrote:
> On 2005-05-05, "Greg Sabino Mullane" <greg@turnstep.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@users.sourceforge.net> wrote: > On Thursday 05 May 2005 19:17, Andrew - Supernews wrote: >> On 2005-05-05, "Greg Sabino Mullane" <greg@turnstep.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@supernews.com> writes:
> On 2005-05-06, Robert Treat <xzilla@users.sourceforge.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@familyhealth.com.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@decibel.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@decibel.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@decibel.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 05:38:16PM -0700, Dann Corbit wrote: > > See Andrew's post. There is a whole lot of stuff not covered by I_S in a > > way > > that is useful to PGSQL users. Also this would require making > > information_schema part of the default user path. > > It is supposed to be part of the default user path for all users of the database. Huh? That seems like a really bad idea. Anything with names not starting with pg_ really needs to not be in the search_path by default. Speaking of which, any suggestions for names of internal-use objects? Right now we've got some things named _pg_* to signify that they're subject to change, but afaik the only naming convention PostgreSQL has laid claim to is pg_*. -- Jim C. Nasby, Database Consultant decibel@decibel.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@turnstep.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_attrdefd 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@decibel.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@decibel.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@decibel.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.oidIS 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_classcl 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
LEFTJOIN pg_type ty on dep.objid=ty.oid LEFT JOIN pg_namespace nst ON typnamespace=nst.oid LEFT JOIN pg_constraint
coon 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
nsrwON 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@decibel.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@decibel.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@decibel.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@familyhealth.com.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@decibel.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@varlena.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@decibel.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@decibel.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@decibel.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@gmx.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
On Friday the 6th of May 2005, Mr. Treat opined: > 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. Admin tools are in a sense already a gui "view" into the database, so whether they elect to use these new views seems rather immaterial to this discussion, to me at least. The more important aspect to me is as a user. As someone last week pointed out (but it seems to have been lost in the tool discussion) is that these views give the user easy access to system information from the command line. This is where typing a multi-join querey against the system catalog is error-prone and a PITA. An even bigger point is that these views can be used in scripts or procedures where the results can be used in another query or put into a table. The '\d' command might show me a list of tables, but I for one find the Oracle-style views such as ALL_TABLES, etc. more useful. I've always found the slash-style commands to be basically useful in showing what's there but using them to build reports or extract data for use in other queries is painful compared to having queriable views of the same data. Darren
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/
> 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. Not useless at all, I couldn't' agree with you more. In fact, there is a good security argument to be made here as well. In the current state of things, a user with no rights at all can see the entire database schema including all the source code to the stored procedures. There are means to circumvent this but they are very coarse and can cause unexpected problems. I think the information_schema approach to system metadata is ultimately the correct one...but I also agree with Josh in that the various client tools such as pgadmin and perhaps even pg_dump should be transitioned to using views. After that the true system columns will no longer need public access and everything becomes very elegant. To me, this is a much finer grained security model and nobody complains about extra security features. Merlin
Peter, Merlin, Andrew, > > > 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. There's a difference between restricting it to objects on which you have rights (which our views do as well) vs. restricting it to objects you *own*, which is what the information_schema does, according to Andrew. Yes? More in next e-mail. -- Josh Berkus Aglio Database Solutions San Francisco
Folks, We've meandered a bit on this, so I wanted to summarize the arguments presented on the new system views to date so that we might have some hope of consensus before feature freeze. As I see it, there are 3 main arguments about having the new system views at all. These obviously need to be settled before we go any further on security models, column names, etc. Please add if I've missed anyone's arguments, I'm trying to summarize across 2 weeks of discussion and am obviously not impartial. Argument (1): Are the views useful to users? Pro: Several people, particularly the proposers, contend that they are. They cite as evidence the popularity of related articles on General Bits, commercial precedent, and the prevalence of user-created system views. Mostly, the usefulness is aimed at new users. Con: A few people say that they are not useful, and that the system tables are easily understood. Argument (2): Do they provide sufficiently distinct functionality from the information_schema? Pro: The proposers contend that the information_schema, by SQL spec, is unable to show all PostgreSQL objects in sufficient detail. That the permissions and uniqueness models are wrong for PostgreSQL, and these things are not easily fixed by extension without breaking the SQL spec. That we don't want to confuse the information_schema with PostgreSQL-specific extensions. Con: Several people, most notably Peter, contend that much of the new system views are duplicative of information_schema, and that efforts should be made to extend infomation_schema instead of providing a parallel interface. That we should make serious efforts to support a standard rather than developing a proprietary interface. A few people claimed that there was nothing that information_schema didn't have, or that users didn't need that information anyway. Argument (3): Would the new system views be useful to interface designers? Pro: Christopher Kings-Lynne said yes for phpPgAdmin. Josh argued that we need to look at interface designers who are designing for 3rd-party multi-database products who are not supporting PostgreSQL yet and will be unlikely to learn the system tables. Con: Dave Page said no for pgAdmin. Several people pointed out issues with the idea of maintaining backwards compatibility through abstraction. Others cited argument (2) in favor of information_schema, above. ... thus, as I see it, the *primary* question is in fact argument (2). That is, is information_schema sufficient, and if not, can it be extended without breaking SQL standards? Argument (1) did not seem to have a lot of evidence on the "con" side, and the strongest argument against (3) is that we should use information_schema. Andrew, can you do a more cohesive set of points on the 2nd half of that question? That is, how much SQL spec would we have to break (other than extension) to cover all of the stuff that pg_sysviews currently covers? -- Josh Berkus Aglio Database Solutions San Francisco
> > ... thus, as I see it, the *primary* question is in fact argument (2). That > is, is information_schema sufficient, and if not, can it be extended without > breaking SQL standards? Argument (1) did not seem to have a lot of evidence > on the "con" side, and the strongest argument against (3) is that we should > use information_schema. (2) The information_schema is good but not sufficient. It either needs more info as suggested by this thread or we need an extended version for Pg specifically. (1) I can't see anyone in their right mind on the user space / support of users side arguing against the need for more information about PostgreSQL and the way it interacts. (3) If we can use the information_schema let's do so. However it should not be a stopping block. Sincerely, Joshua D. Drake Command Prompt. Inc. -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
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@decibel.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@gmx.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
On Tue, May 10, 2005 at 10:21:06AM -0700, Josh Berkus wrote: > Folks, > > We've meandered a bit on this, so I wanted to summarize the > arguments presented on the new system views to date so that we might > have some hope of consensus before feature freeze. > > As I see it, there are 3 main arguments about having the new system > views at all. These obviously need to be settled before we go any > further on security models, column names, etc. Please add if I've > missed anyone's arguments, I'm trying to summarize across 2 weeks of > discussion and am obviously not impartial. > > Argument (1): Are the views useful to users? I've used some of them already at work :) > Pro: Several people, particularly the proposers, contend that they > are. They cite as evidence the popularity of related articles on > General Bits, commercial precedent, and the prevalence of > user-created system views. Mostly, the usefulness is aimed at new > users. > Con: A few people say that they are not useful, and that the system > tables are easily understood. Anybody who contends that the system tables are easily understood is more than welcome to use this understanding, and will not be impeded by the existence of things they don't choose to use. The aforementioned understanding--quite rare, but that's almost beside the point--is not an argument for keeping tools out of the hands of people for whom the internals of the PostgreSQL implementation are not intuitively obvious. > Argument (2): Do they provide sufficiently distinct functionality > from the information_schema? > Pro: The proposers contend that the information_schema, by SQL > spec, is unable to show all PostgreSQL objects in sufficient detail. > That the permissions and uniqueness models are wrong for PostgreSQL, > and these things are not easily fixed by extension without breaking > the SQL spec. That we don't want to confuse the information_schema > with PostgreSQL-specific extensions. > Con: Several people, most notably Peter, contend that much of the > new system views are duplicative of information_schema, and that > efforts should be made to extend infomation_schema instead of > providing a parallel interface. That we should make serious efforts > to support a standard rather than developing a proprietary > interface. A few people claimed that there was nothing that > information_schema didn't have, or that users didn't need that > information anyway. > Argument (3): Would the new system views be useful to interface designers? > Pro: Christopher Kings-Lynne said yes for phpPgAdmin. Josh argued that we > need to look at interface designers who are designing for 3rd-party > multi-database products who are not supporting PostgreSQL yet and will be > unlikely to learn the system tables. > Con: Dave Page said no for pgAdmin. Several people pointed out issues with > the idea of maintaining backwards compatibility through abstraction. Others > cited argument (2) in favor of information_schema, above. > ... thus, as I see it, the *primary* question is in fact argument > (2). That is, is information_schema sufficient, Not by a long, long way. > and if not, can it be extended without breaking SQL standards? The information schema, by its nature, cannot contain information about things like indexes because that would be implementation- specific information, and the information schema is, by design, implementation-neutral. Just my $.02 :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
"Jim C. Nasby" <decibel@decibel.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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > interface designers who are designing for 3rd-party multi-database > products who are not supporting PostgreSQL yet and will be > unlikely to learn the system tables There's a scary thought. So they are willing to learn the new system views, but not the system tables? The above seems an argument for I_S, or at least an expanded I_S. So... the reason we don't want to expand (not alter) I_S is that it is a "standard" that very few RDBMS actually bother to implement, is already out of date, and is incomplete? Seems we bend the rules in other ways when needed (e.g. lowercase relation names), we could certainly add additional tables and columns here, while maintaining the "standard" set for applications looking for them. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200505100635 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCgI6UvJuQZxSWSsgRAsp3AJ9aY8qeVzpKTcq5yXkhmtkJvuFRWACfXPST TNNRK32VwbaHimNhB9hjWb8= =Saja -----END PGP SIGNATURE-----
On May 11, 2005, at 7:38, Greg Sabino Mullane wrote: > So they are willing to learn the new system views, but not the system > tables? The above seems an argument for I_S, or at least an expanded > I_S. > > So... the reason we don't want to expand (not alter) I_S is that it is > a > "standard" that very few RDBMS actually bother to implement, is already > out of date, and is incomplete? Seems we bend the rules in other ways > when > needed (e.g. lowercase relation names), we could certainly add > additional > tables and columns here, while maintaining the "standard" set for > applications > looking for them. > One of the reasons I've been impressed with PostgreSQL and its developers is that I've seen respect for the SQL specifications *except in cases where it would seriously break backwards compatibility*. In implementing new features, if the SQL spec has something to say about it, it's been my observation that good efforts have been made to comply, though sometimes other syntax or PostgreSQL extensions are made. This is not to say the SQL spec is perfect. (At heart I lean toward the Date/Darwin relational model, but that's just me :) However, to take something that *is* specified by SQL (and if I understand correctly, was *implemented in PostgreSQL specifically for SQL compliance*, it would be a shame to break that. I think PostgreSQL's spec compliance is a nice bragging point as well -- we do the spec, and more :) Additional views that depend where possible on the INFORMATION_SCHEMA could actually be a good thing, as the INFORMATION_SCHEMA follows the spec, it'd be less likely to change between versions and make maintenance easier. That said, I haven't looked at the work the new systems views people have done. I recognize their motivation, as the times I've needed to look at the current system tables, it's always been with the docs open right beside me, flipping between pages to see everything I need to join to get the information I want. I for one am happy and grateful that a group of people have taken it upon themselves to provide an easier way to view Postgres system information, and think that the additional views in some form would make a great addition to PostgreSQL. Michael Glaesemann grzm myrealbox com
I guess I'm having difficulty understanding why the system catalogs themselves and provision of support for information_schema are not sufficient for what exists in core. At one point, there was a stored procedure database for Pl/PgSQL. It seems like a system view service like that could easily be created and maintained independently of what is actually considered the core PostgreSQL distribution. If one of the primary issues is a lack of clarity in the documentation of the system catalogs, then that is certainly something that ought to be addressed. But if another of the primary issues is a need for easier access to the information contained in the system catalogs/information schema, then that can be addressed by a public repository that can certainly be moderated and maintained. Think VPAN (Views of PostgreSQL Archive Network)... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 10, 2005, at 12:21 PM, Josh Berkus wrote: > Folks, > > We've meandered a bit on this, so I wanted to summarize the arguments > presented on the new system views to date so that we might have some > hope of > consensus before feature freeze. > > As I see it, there are 3 main arguments about having the new system > views at > all. These obviously need to be settled before we go any further on > security > models, column names, etc. Please add if I've missed anyone's > arguments, > I'm trying to summarize across 2 weeks of discussion and am obviously > not > impartial. > > Argument (1): Are the views useful to users? > Pro: Several people, particularly the proposers, contend that they > are. They > cite as evidence the popularity of related articles on General Bits, > commercial precedent, and the prevalence of user-created system views. > Mostly, the usefulness is aimed at new users. > Con: A few people say that they are not useful, and that the system > tables are > easily understood. > > Argument (2): Do they provide sufficiently distinct functionality from > the > information_schema? > Pro: The proposers contend that the information_schema, by SQL spec, > is > unable to show all PostgreSQL objects in sufficient detail. That the > permissions and uniqueness models are wrong for PostgreSQL, and these > things > are not easily fixed by extension without breaking the SQL spec. That > we > don't want to confuse the information_schema with PostgreSQL-specific > extensions. > Con: Several people, most notably Peter, contend that much of the new > system > views are duplicative of information_schema, and that efforts should > be made > to extend infomation_schema instead of providing a parallel interface. > That > we should make serious efforts to support a standard rather than > developing a > proprietary interface. A few people claimed that there was nothing > that > information_schema didn't have, or that users didn't need that > information > anyway. > > Argument (3): Would the new system views be useful to interface > designers? > Pro: Christopher Kings-Lynne said yes for phpPgAdmin. Josh argued > that we > need to look at interface designers who are designing for 3rd-party > multi-database products who are not supporting PostgreSQL yet and will > be > unlikely to learn the system tables. > Con: Dave Page said no for pgAdmin. Several people pointed out > issues with > the idea of maintaining backwards compatibility through abstraction. > Others > cited argument (2) in favor of information_schema, above. > > ... thus, as I see it, the *primary* question is in fact argument (2). > That > is, is information_schema sufficient, and if not, can it be extended > without > breaking SQL standards? Argument (1) did not seem to have a lot of > evidence > on the "con" side, and the strongest argument against (3) is that we > should > use information_schema. > > Andrew, can you do a more cohesive set of points on the 2nd half of > that > question? That is, how much SQL spec would we have to break (other > than > extension) to cover all of the stuff that pg_sysviews currently covers? > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
Thomas, All, > I guess I'm having difficulty understanding why the system catalogs > themselves and provision of support for information_schema are not > sufficient for what exists in core. Because you can't answer the question: "What tables does user phil have update permissions on?" or "How many overloaded versions of function df_new_company() do I have?", and similar, without doing advanced queries on the system tables. Queries which are prone to mistakes: earlier on this thread a *pgsql hacker* posted a sample system catalog query which contained a mistake. Asking our general users to navigate the complexity of the system catalogs is just not good application practice. I don't really think a "VPAN" is any kind of solution for this purpose (though I'd like it for other things). The purpose of these views is to make PostgreSQL more user-friendly, and telling people: "Oh just go to http://name/of/obscure/site, lookup these 10 views, log in as superuser and load them and you're golden" is not much of an improvement in user-friendliness. To reiterate my point previously: these system views are NOT aimed at the people on *this* list; they are for the people on the -NOVICE and -GENERAL lists and IRC and the people who don't yet use PostgreSQL. Please stop thinking exclusively in terms of whether they would be useful to you, personally. -- Josh Berkus Aglio Database Solutions San Francisco
> > To reiterate my point previously: these system views are NOT aimed at the > people on *this* list; they are for the people on the -NOVICE and -GENERAL > lists and IRC and the people who don't yet use PostgreSQL. Please stop > thinking exclusively in terms of whether they would be useful to you, > personally. I think the above paragraph is very important. When doing development it is very easy to think only in terms of what would be useful to you. I have experienced this quite bit when working on the new book as well. It is very difficult for me to write a chapter on installing PostgreSQL for Windows, because for me it is completely useless and fairly, "If you can't figure it out, you shouldn't be using PostgreSQL" in the first place. However that is not the case with most of our users. Most of our users have no clue what is in the pg_ tables or even how to get access to them. A set of documented views would be very useful in this sense. It would be a lot easier for someone to say: select size from large_ojects_metainfo where loid = 12545; Then the other methods to get the large_object size. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
I'm not thinking exclusively in terms of whether they would be useful to me, personally. In fact, I'm certain that they would be useful to me, personally. What I question is whether they need to be a part of the internal development of PostgreSQL. To me, CPAN is an integral part of being able to do Perl development effectively. Whether or not a VPAN setup could come to seem as natural and easy for new users to use, I don't know. Regardless, these new views are going to need to be documented similarly so that new users are aware of them. And it still isn't clear (to me) how the debate over how to shape them as included by default will resolve, so punting to an externally maintained repository is just a suggestion as an alternative. I think it's important to consider the perspective of both developers and users, and the internal views clearly creates issues for the developers. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 11, 2005, at 11:35 AM, Josh Berkus wrote: > Thomas, All, > > >> I guess I'm having difficulty understanding why the system catalogs >> themselves and provision of support for information_schema are not >> sufficient for what exists in core. >> > > Because you can't answer the question: "What tables does user phil > have update > permissions on?" or "How many overloaded versions of function > df_new_company() do I have?", and similar, without doing advanced > queries on > the system tables. Queries which are prone to mistakes: earlier > on this > thread a *pgsql hacker* posted a sample system catalog query which > contained > a mistake. Asking our general users to navigate the complexity of > the system > catalogs is just not good application practice. > > I don't really think a "VPAN" is any kind of solution for this > purpose (though > I'd like it for other things). The purpose of these views is to make > PostgreSQL more user-friendly, and telling people: "Oh just go to > http://name/of/obscure/site, lookup these 10 views, log in as > superuser and > load them and you're golden" is not much of an improvement in > user-friendliness. > > To reiterate my point previously: these system views are NOT aimed > at the > people on *this* list; they are for the people on the -NOVICE and - > GENERAL > lists and IRC and the people who don't yet use PostgreSQL. Please > stop > thinking exclusively in terms of whether they would be useful to you, > personally. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> I think it's important to consider the perspective of both developers
> and users, and the internal views clearly creates issues for the
> developers.
FWIW, I don't see the issue as "internal vs external" at all. What's
bothering me is whether these views can be considered sufficiently
more stable and better designed than the physical system catalogs
to justify recommending that application designers should rely on
the views instead of the catalogs. That point doesn't seem to me
to have been proven. The recent arguments in favor seem to boil down to
"novices will find these easier to use", which is very possibly true,
but novices don't have the same needs as programs.
regards, tom lane
> FWIW, I don't see the issue as "internal vs external" at all. What's > bothering me is whether these views can be considered sufficiently > more stable and better designed than the physical system catalogs > to justify recommending that application designers should rely on > the views instead of the catalogs. That point doesn't seem to me > to have been proven. The recent arguments in favor seem to boil down to > "novices will find these easier to use", which is very possibly true, > but novices don't have the same needs as programs. As lead phpPgAdmin developer, I'm officially in favour of them. The main reason being all the extra fruit they have that shows database size, etc. That means we can display this meta information in phpPgAdmin and not worry about having to re-implement it all. Chris
On Thu, May 12, 2005 at 01:23:17AM -0400, Tom Lane wrote: > "Thomas F. O'Connell" <tfo@sitening.com> writes: > > I think it's important to consider the perspective of both developers > > and users, and the internal views clearly creates issues for the > > developers. > > FWIW, I don't see the issue as "internal vs external" at all. What's > bothering me is whether these views can be considered sufficiently > more stable and better designed than the physical system catalogs > to justify recommending that application designers should rely on > the views instead of the catalogs. That point doesn't seem to me > to have been proven. The recent arguments in favor seem to boil down to > "novices will find these easier to use", which is very possibly true, > but novices don't have the same needs as programs. > Would you rather help hone a set of PostgreSQL views or answer and correct every single dba and tool developer who needs to have the system catalog entity-relationship diagram (exists?) explained in detail in order to access system catalogs accurately. You obviously have no trouble throwing together system catalog queries, but this is not true of the general user base. It should also be acknowledged that the system catalogs are adequate, useful and obtuse. Also, if you do not trust the newsysview team to develop good views (with input for hackers), how can you possibly expect every dba and tool maker to access the system catalog in a consistent and accurate manner. If everyone is rolling their own queries into the system catalog, there is a lot of room for error and inconsistency between tools. I believe tool developers will confirm this. It would be better to spend time to work on really good system views and allow people build on them, starting from a higher level than the system catalogs. elein ============================================================= elein@varlena.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.
elein@varlena.com (elein) writes:
> Also, if you do not trust the newsysview team to develop good views
> (with input for hackers), how can you possibly expect every dba and tool
> maker to access the system catalog in a consistent and accurate manner.
I never said that they couldn't develop useful views. I do question
the assumption that they can be all things to all people. I think the
claims being made in this thread are highly overblown. In particular
I doubt that views that expose everything anyone might want to know
are going to be amazingly much simpler than the underlying catalogs.
So far the goals that have been presented include being complete, being
simple, and being stabler than the underlying catalogs. I think you
can have *one* of those; probably not two and definitely not all three.
So tell us the truth about what your priorities are and which goals will
lose when there's a conflict.
I see a whole lot of NIH (Not Invented Here) syndrome --- the developers
of these views love 'em, sure, because they fit the way those developers
think. But that doesn't prove everyone else will love 'em.
regards, tom lane
Tom, > I never said that they couldn't develop useful views. I do question > the assumption that they can be all things to all people. I think the > claims being made in this thread are highly overblown. In particular > I doubt that views that expose everything anyone might want to know > are going to be amazingly much simpler than the underlying catalogs. Well, we're actually trying to answer 2 seperate questions on this thread: 1) would some kind of new system views in theory be valuable and accepted, and 2) what needs to change in these particular system views. We need to answer question (1) first because there's no point on doing a lot of work to modify our proposal if it's just going to be rejected. > So far the goals that have been presented include being complete, being > simple, and being stabler than the underlying catalogs. I think you > can have *one* of those; probably not two and definitely not all three. > So tell us the truth about what your priorities are and which goals will > lose when there's a conflict. My personal goal is to have views which are complete and simpler than the system catalogs to the extent that it doesn't interfere with being complete. For super-simple, incomplete views we have the information_schema and the old system views. I think you've sucessfully punched several holes in the "more stable" argument, so that's really the lowest priority. > I see a whole lot of NIH (Not Invented Here) syndrome --- the developers > of these views love 'em, sure, because they fit the way those developers > think. But that doesn't prove everyone else will love 'em. Sure, but so far the arguments have not revolved around the specific views proposed, but rather the whole concept of new system views at all. In fact, I think Peter was the only person to give us any useful feedback on the actual design ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Thu, May 12, 2005 at 03:30:59PM -0400, Tom Lane wrote: > elein@varlena.com (elein) writes: > > Also, if you do not trust the newsysview team to develop good views > > (with input for hackers), how can you possibly expect every dba and tool > > maker to access the system catalog in a consistent and accurate manner. > > I never said that they couldn't develop useful views. I do question > the assumption that they can be all things to all people. I think the > claims being made in this thread are highly overblown. In particular > I doubt that views that expose everything anyone might want to know > are going to be amazingly much simpler than the underlying catalogs. > > So far the goals that have been presented include being complete, being > simple, and being stabler than the underlying catalogs. I think you > can have *one* of those; probably not two and definitely not all three. > So tell us the truth about what your priorities are and which goals will > lose when there's a conflict. In real life, as opposed to goals, the views will be mostly complete but will provide a simpler foundation for people to build on. Simplicity is an objective measurement. Simpler than the existing system catalogs is a clear objective. My own push and emphasis for this views is to simplify support and useabilty--to answer questions people (and tools) ask most often. Others lean toward completeness as more important. And lastly, the views will only be as stable as the system catalogs. If anyone says differently they are wrong. > > I see a whole lot of NIH (Not Invented Here) syndrome --- the developers > of these views love 'em, sure, because they fit the way those developers > think. But that doesn't prove everyone else will love 'em. > Usability must be the emphasis. That is why I push toward having the views answer questions that users need to ask. --elein ============================================================= elein@varlena.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.
Josh Berkus wrote: >1) would some kind of new system views in theory be valuable and accepted > > I still don't have any strong views, but I do want the target audience specified - I have seen conflicting messages on that. Power users? Admin Tool builders? Client library builders? These groups don't all have the same needs. cheers andrew
On Thursday 12 May 2005 01:32, Christopher Kings-Lynne wrote:
> > FWIW, I don't see the issue as "internal vs external" at all. What's
> > bothering me is whether these views can be considered sufficiently
> > more stable and better designed than the physical system catalogs
> > to justify recommending that application designers should rely on
> > the views instead of the catalogs. That point doesn't seem to me
> > to have been proven. The recent arguments in favor seem to boil down to
> > "novices will find these easier to use", which is very possibly true,
> > but novices don't have the same needs as programs.
>
> As lead phpPgAdmin developer, I'm officially in favour of them. The
> main reason being all the extra fruit they have that shows database
> size, etc.
>
As non-lead phpPgAdmin developer, I'd be against using them in phppgadmin.
(note this doesnt mean I am against them in pgsql itself)
> That means we can display this meta information in phpPgAdmin and not
> worry about having to re-implement it all.
>
You have to query something... doesn't bother me to go to the source.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Andrew, > I still don't have any strong views, but I do want the target audience > specified - I have seen conflicting messages on that. Power users? Admin > Tool builders? Client library builders? These groups don't all have the > same needs. DBAs, tool builders (primarily existing-tool-integrators), and PostgreSQL beginners. Power users could presumably find and install them from pgFoundry. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Thu, May 12, 2005 at 05:52:43PM -0400, Robert Treat wrote: > On Thursday 12 May 2005 01:32, Christopher Kings-Lynne wrote: > > > FWIW, I don't see the issue as "internal vs external" at all. > > > What's bothering me is whether these views can be considered > > > sufficiently more stable and better designed than the physical > > > system catalogs to justify recommending that application > > > designers should rely on the views instead of the catalogs. > > > That point doesn't seem to me to have been proven. The recent > > > arguments in favor seem to boil down to "novices will find these > > > easier to use", which is very possibly true, but novices don't > > > have the same needs as programs. > > > > As lead phpPgAdmin developer, I'm officially in favour of them. > > The main reason being all the extra fruit they have that shows > > database size, etc. > > > > As non-lead phpPgAdmin developer, I'd be against using them in > phppgadmin. (note this doesnt mean I am against them in pgsql > itself) On what grounds? > > That means we can display this meta information in phpPgAdmin and > > not worry about having to re-implement it all. > > > > You have to query something... doesn't bother me to go to the > source. A PostgreSQL developer has shown in this very thread that it is extremely easy to screw up a query against those catalogs. Maybe you're better than he is, but that's not a reason to keep something simpler out. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
>>As lead phpPgAdmin developer, I'm officially in favour of them. The >>main reason being all the extra fruit they have that shows database >>size, etc. > > As non-lead phpPgAdmin developer, I'd be against using them in phppgadmin. > (note this doesnt mean I am against them in pgsql itself) Hehe, talk about your democratic institutions :) Chris
Christopher Kings-Lynne wrote: >>> As lead phpPgAdmin developer, I'm officially in favour of them. The >>> main reason being all the extra fruit they have that shows database >>> size, etc. >> >> >> As non-lead phpPgAdmin developer, I'd be against using them in >> phppgadmin. (note this doesnt mean I am against them in pgsql itself) > > > Hehe, talk about your democratic institutions :) > > But you have more votes, right? cheers andrew
On Thursday 12 May 2005 19:59, David Fetter wrote:
> On Thu, May 12, 2005 at 05:52:43PM -0400, Robert Treat wrote:
> > On Thursday 12 May 2005 01:32, Christopher Kings-Lynne wrote:
> > > > FWIW, I don't see the issue as "internal vs external" at all.
> > > > What's bothering me is whether these views can be considered
> > > > sufficiently more stable and better designed than the physical
> > > > system catalogs to justify recommending that application
> > > > designers should rely on the views instead of the catalogs.
> > > > That point doesn't seem to me to have been proven. The recent
> > > > arguments in favor seem to boil down to "novices will find these
> > > > easier to use", which is very possibly true, but novices don't
> > > > have the same needs as programs.
> > >
> > > As lead phpPgAdmin developer, I'm officially in favour of them.
> > > The main reason being all the extra fruit they have that shows
> > > database size, etc.
> >
> > As non-lead phpPgAdmin developer, I'd be against using them in
> > phppgadmin. (note this doesnt mean I am against them in pgsql
> > itself)
>
> On what grounds?
>
See upthread response from the various pgadmin guys as to why they wouldn't
use them.
> > > That means we can display this meta information in phpPgAdmin and
> > > not worry about having to re-implement it all.
> >
> > You have to query something... doesn't bother me to go to the
> > source.
>
> A PostgreSQL developer has shown in this very thread that it is
> extremely easy to screw up a query against those catalogs. Maybe
> you're better than he is, but that's not a reason to keep something
> simpler out.
>
Out of phppgadmin or out of pgsql itself? The internals of phppgadmin dont
have to be simple (sure its a bonus when they are, but its not the primary
goal). Of course thats completly orthagonal to whether these views should be
in pgsql itself. Unless you think they should be in there for admin tools.
Personally I think at least a couple of these should be in there for the ease
of use factor (relacl is just evil), but the other reasons of unchanging
interface and defining all objects seem wrong headed to me and make me
skeptical of the whole thing.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Thu, May 12, 2005 at 16:59:07 -0700, David Fetter <david@fetter.org> wrote: > > A PostgreSQL developer has shown in this very thread that it is > extremely easy to screw up a query against those catalogs. Maybe > you're better than he is, but that's not a reason to keep something > simpler out. You could still study the views to see the correct way to do things, but then not actually use them in the tool. I think this is actually a good reason for developing the views, even if they end up as an add on project.
On Thu, May 12, 2005 at 04:03:39PM -0400, Andrew Dunstan wrote: > I still don't have any strong views, but I do want the target audience > specified - I have seen conflicting messages on that. Power users? Admin > Tool builders? Client library builders? These groups don't all have the > same needs. While their needs may not be identical, I don't think that means you can't provide views that serve all of their needs, unless there are requirements that are in direct opposition to on another. I can't think of any requirements in your examples that would fall under this catagory. Another use case I wanted to mention is using system views to assist in remote troubleshooting. If you need to know something about someone's database, it will be much easier to ask them to run a query against the system views as opposed to the catalog. -- Jim C. Nasby, Database Consultant decibel@decibel.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 Wed, 4 May 2005 21:37:40 -0700, Josh Berkus <josh@agliodbs.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. ServusManfred