Thread
-
Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T04:37:40Z
PG hackers, AndrewSN, Jim Nasby, Elein and I have been working for the last couple of months on a new set of system views for PostgreSQL. (primarily Andrew, who did the lion's share of the work and came up with many clever SQL workarounds) We'd like to include them in the 8.1 release, so we're going to post most of the definitions for your feedback now. Let me summarize: Goals of the New System Views --------------------------------------- 1. To be easily human-readable, unlike the system tables. 2. To provide a consistent API to PostgreSQL object definitions which will seldom (if ever) be changed, only added to. 3. To provide queryable definitions for all PostgreSQL objects. In more detail: 1. The current system tables are designed around performance and code requirements, and as such are optimized for code access, not comprehensability. Column names are obscure, special system data types are used, and everything is OIDs and typids. This is perfect for our code, but too many user-space applications are using these tables for comfort. Our first system views (pg_tables, for example) only went halfway in providing a user-friendly interface. So the new system views have the following requirements: a) all view and column names are as explicit and as readable as possible (e.g. "type_schema_name", not "typnsname") b) OIDs, typids, and other system codes are avoided wherever possible in preference to full object names c) In most places, "system" objects are segregated from "user" objects, e.g. pg_user_indexes 2. One of the issues with user applications querying the system tables is that they can be subject to significant changes from version to version. This has kept the pgAdmin and phpPgAdmin teams busy since 7.2, and means that GUI tools which fall out of maintenance (like Xpg) soon stop working. This is easily remedied through a set of system views which will remain consistent regardless of changes in the underlying system tables. This has the beneficial effect of giving us more freedom to make changes to the system tables. Further, we discovered when we proposed dropping the old system views that once these views are created we're stuck with them for several years, if not forever; people's not-easily-recoded tools rely on them. d) Columns may be added to the system views, but never dropped or changed in incompatible ways. Likewise, views will be added but not dropped or renamed. e) Users and app developers should be actively encouraged to use the system views rather than the system tables in the documentation. f) Existing projects, features and add-ons, where appropriate, should gradually be shifted to use the system views to minimize version maintenance. 3. The new system views (unlike, for example, \d) are designed to be a SQL interface to system objects. This means that: g) All views are as normalized as possible, using child views rather than arrays, and providing keys and consistent join columns. h) Each view or set of views provides all of the data required to replicate the appropriate CREATE statement. i) Column names are designed to be universal to a particular type of data, where this does not cause duplication. For example, pg_user_tables has "schema_name" rather than "table_schema". This was done to make joins easier (i.e. USING, NATURAL JOIN) Additional assumptions we worked with include: j) all view names are plural in order to prevent conflict with system tables. k) no procedural languages are used, as we don't want to make PLs mandatory. Currently everything is SQL (really!) and we may move a few functions to a C library eventually. l) internal functions required for the system views are named using a "_pg_" convention. m) We will be offering a "back-patch" for 7.4 and 8.0 via pgFoundry. What We Need From Hackers -------------------------------------- (other than patch approval, that is) As stated above, these system views, once incorporated into a pg distribution, are likely to be with us *forever*. As such, we really can't afford to do major refactoring of the column names and structure once they're released. So it's really, really, important for everyone on hackers to look over the definitions below and find stuff that we've missed or doesn't make any sense. Also, we'd like to know about 8.1 changes that affect these views. There are two additional other questions to discuss that our team as not settled: I) Should the new views be part of /contrib before they become part of the main source? II) Should the new views be in their own schema? This would make them easier to manage for DBAs who want to restrict access or dump them, but would add a second "system" schema to the template. Information_Schema note --------------------------------- Q: Why not just use information_schema? A: Because the columns and layout of information_schema is strictly defined by the SQL standard. This prevents it from covering all PostgreSQL objects, or from covering the existing objects adequately to replicate a CREATE statement. As examples, there is no "types" table in information_schema, and the "constraints" table assumes that constraint names are universally unique instead of table-unique as they are in PG. The View Definitions ---------------------------------- The column definitions of the views are below. Please examine them carefully. Currently, the following views are incomplete and thus not included: pg_functions pg_function_parameters pg_types pg_acl_modes Column | Type | -------------+------+----------- object_type | text | mode | text | granted | text | description | text | pg_all_aggregates Column | Type | Modifiers -----------------------+---------+----------- schema_name | name | aggregate_name | name | input_type_schema | name | input_type | name | output_type_schema | name | output_type | name | initial_value | text | trans_function_schema | name | trans_function_name | name | final_function_schema | name | final_function_name | name | is_system_aggregate | boolean | owner | name | pg_user_aggregates Column | Type | Modifiers -----------------------+---------+----------- schema_name | name | aggregate_name | name | input_type_schema | name | input_type | name | output_type_schema | name | output_type | name | initial_value | text | trans_function_schema | name | trans_function_name | name | final_function_schema | name | final_function_name | name | owner | name | pg_all_casts Column | Type | --------------------+---------+----------- source_schema | name | source_type | name | target_schema | name | target_type | name | function_schema | name | function_name | name | function_arguments | text | context | text | is_system_cast | boolean | pg_user_casts Column | Type | --------------------+---------+----------- source_schema | name | source_type | name | target_schema | name | target_type | name | function_schema | name | function_name | name | function_arguments | text | context | text | pg_all_conversions Column | Type | Modifiers ----------------------+---------+----------- schema_name | name | conversion_name | name | source_encoding | name | destination_encoding | name | is_default | boolean | function_schema | name | function_name | name | is_system_conversion | boolean | owner | name | pg_user_conversions Column | Type | ----------------------+---------+----------- schema_name | name | conversion_name | name | source_encoding | name | destination_encoding | name | is_default | boolean | function_schema | name | function_name | name | owner | name | pg_databases Column | Type | --------------------+---------+----------- database_name | name | encoding | name | default_tablespace | name | database_config | text[] | is_template | boolean | can_connect | boolean | owner | name | pg_database_config Column | Type | --------------------+------+----------- database_name | name | config_variable | text | config_value | text | pg_all_foreign_key_indexes, pg_user_foreign_key_indexes Column | Type | Modifiers ---------------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | num_columns | integer | num_indexed_columns | integer | index_name | name | pg_all_foreign_keys, pg_user_foreign_keys Column | Type | -----------------------------+---------+----------- foreign_key_schema_name | name | foreign_key_table_name | name | foreign_key_constraint_name | name | foreign_key_table_oid | oid | foreign_key_columns | name[] | key_schema_name | name | key_table_name | name | key_constraint_name | name | key_table_oid | oid | key_index_name | name | key_columns | name[] | match_type | text | on_delete | text | on_update | text | is_deferrable | boolean | is_deferred | boolean | pg_all_foreign_key_columns, pg_user_foreign_key_columns Column | Type | -----------------------------+---------+----------- foreign_key_schema_name | name | foreign_key_table_name | name | foreign_key_constraint_name | name | foreign_key_table_oid | oid | foreign_key_column | name | column_position | integer | key_schema_name | name | key_table_name | name | key_table_oid | oid | key_column | name | pg_all_grants, pg_user_grants Column | Type | --------------+---------+----------- object_type | name | object_oid | oid | schema_name | name | object_name | name | object_args | text | owner | name | grantor | text | grantee | text | is_group | boolean | privilege | text | grant_option | boolean | pg_groups Column | Type | ------------+---------+----------- group_name | name | gid | integer | pg_groups_users Column | Type | ------------+------+----------- group_name | name | user_name | name | pg_all_index_columns, pg_user_index_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | index_name | name | column_name | name | NULL if an expression column_position | integer | 1..n opclass_schema | name | opclass_name | name | definition | text | expression or column name pg_all_indexes Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | index_name | name | tablespace | name | index_method | name | num_columns | smallint | is_primary_key | boolean | is_unique | boolean | is_clustered | boolean | is_expression | boolean | is_partial | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | is_system_table | boolean | table_oid | oid | predicate | text | definition | text | owner | name | comment | text | pg_user_indexes Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | index_name | name | tablespace | name | index_method | name | num_columns | smallint | is_primary_key | boolean | is_unique | boolean | is_clustered | boolean | is_expression | boolean | is_partial | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | predicate | text | definition | text | owner | name | comment | text | pg_all_relation_columns, pg_user_relation_columns Column | Type | ---------------+---------+----------- schema_name | name | relation_name | name | column_name | name | relation_oid | oid | column_number | integer | is_view | boolean | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_relation_column_type_info, pg_user_relation_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | relation_name | name | column_name | name | relation_oid | oid | column_number | integer | is_view | boolean | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_relations Column | Type | --------------------+---------------+----------- schema_name | name | relation_name | name | is_system_relation | boolean | is_temporary | boolean | is_view | boolean | relation_oid | oid | owner | name | comment | text | pg_user_relations Column | Type | --------------------+---------------+----------- schema_name | name | relation_name | name | is_temporary | boolean | is_view | boolean | owner | name | comment | text | pg_all_rules, pg_user_rules Column | Type | ---------------+---------+----------- schema_name | name | relation_name | name | rule_name | name | rule_event | text | is_instead | boolean | condition | text | action | text | pg_all_schemas Column | Type | ---------------------+---------+----------- schema_name | name | is_system_schema | boolean | is_temporary_schema | boolean | owner | name | comment | text | pg_user_schemas Column | Type | ---------------------+---------+----------- schema_name | name | is_temporary_schema | boolean | owner | name | comment | text | pg_all_schema_contents, pg_user_schema_contents Column | Type | -------------+------+----------- schema_name | name | owner | name | object_type | name | object_name | name | object_args | text | pg_all_sequences Column | Type | --------------------+---------+----------- schema_name | name | sequence_name | name | is_system_sequence | boolean | is_temporary | boolean | pg_user_sequences Column | Type | --------------------+---------+----------- schema_name | name | sequence_name | name | is_temporary | boolean | pg_all_table_columns, pg_user_table_columns Column | Type | ---------------+---------+----------- schema_name | name | table_name | name | column_name | name | table_oid | oid | column_number | integer | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_table_column_type_info, pg_user_table_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | table_name | name | column_name | name | table_oid | oid | column_number | integer | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_table_constraints, pg_user_table_constraints Column | Type | -----------------+------+----------- schema_name | name | table_name | name | constraint_name | name | constraint_type | text | table_oid | oid | definition | text | pg_all_table_constraint_columns, pg_user_table_constraint_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | column_name | name | column_position | integer | constraint_type | text | table_oid | oid | pg_all_unique_constraint_columns, pg_user_unique_constraint_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | is_primary_key | boolean | column_name | name | column_position | integer | table_oid | oid | pg_all_primary_key_columns, pg_user_primary_key_columns Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | constraint_name | name | column_name | name | column_position | integer | table_oid | oid | pg_all_table_check_constraints, pg_user_table_check_constraints Column | Type | -----------------+--------+----------- schema_name | name | table_name | name | constraint_name | name | table_oid | oid | columns | name[] | predicate | text | pg_all_table_inheritance, pg_user_table_inheritance Column | Type | -------------------+---------+----------- schema_name | name | table_name | name | table_oid | oid | descendent_schema | name | descendent_table | name | descendent_oid | oid | ordinal_position | integer | pg_all_table_storage, pg_user_table_storage Column | Type | ------------------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | is_temporary | boolean | num_indexes | integer | clustered_on | name | estimated_rows | real | estimated_index_rows | real | estimated_total_mb | numeric | estimated_data_mb | numeric | estimated_main_mb | numeric | estimated_external_mb | numeric | estimated_index_mb | numeric | index_tablespaces | name[] | pg_all_tables Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | with_oids | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | includes toast but not indexes has_toast_table | boolean | has_descendents | boolean | is_system_table | boolean | is_temporary | boolean | table_oid | oid | owner | name | comment | text | pg_user_tables Column | Type | -----------------+---------------+----------- schema_name | name | table_name | name | tablespace | name | with_oids | boolean | estimated_rows | real | estimated_mb | numeric(12,1) | includes toast but not indexes has_toast_table | boolean | has_descendents | boolean | is_temporary | boolean | owner | name | comment | text | pg_tablespaces Column | Type | -------------+---------+----------- tablespace | name | location | text | is_writable | boolean | owner | name | comment | text | pg_tablespace_usage Column | Type | ------------+------+----------- tablespace | name | database | name | pg_all_tablespace_contents, pg_user_tablespace_contents Column | Type | ---------------+---------------+----------- tablespace | name | object_type | text | owner | name | object_schema | name | object_name | name | estimated_mb | numeric(12,1) | pg_all_triggers, pg_user_triggers Column | Type | -----------------+---------+----------- schema_name | name | table_name | name | trigger_name | name | function_schema | name | function_name | name | function_args | text[] | function_oid | oid | before | boolean | for_each_row | boolean | on_insert | boolean | on_delete | boolean | on_update | boolean | enabled | boolean | definition | text | comment | text | pg_users Column | Type | ------------------------+--------------------------+----------- user_name | name | uid | integer | create_datebase | boolean | create_user | boolean | superuser | boolean | update_system_catalogs | boolean | password_expires | timestamp with time zone | pg_user_config Column | Type | -----------------+------+----------- user_name | name | config_variable | text | config_value | text | pg_all_view_columns, pg_user_view_columns Column | Type | ---------------+---------+----------- schema_name | name | view_name | name | column_name | name | view_oid | oid | column_number | integer | nullable | boolean | declared_type | text | default_value | text | comment | text | pg_all_view_column_type_info, pg_user_view_column_type_info Column | Type | --------------------+---------+----------- schema_name | name | view_name | name | column_name | name | view_oid | oid | column_number | integer | nullable | boolean | domain_schema | name | domain_name | name | type_sqlname | text | "bare" SQL name, e.g. 'numeric' type_sqldef | text | full SQL name, e.g. 'numeric(10,2)' type_schema | name | type_name | name | type_oid | oid | type_length | integer | is_array | boolean | array_dimensions | integer | currently always 1 for arrays element_sqlname | text | element_sqldef | text | element_schema | name | element_name | name | element_oid | oid | element_length | integer | character_length | integer | bit_length | integer | integer_precision | integer | float_precision | integer | numeric_precision | integer | numeric_scale | integer | time_precision | integer | interval_precision | integer | interval_fields | text | pg_all_views Column | Type | -----------------+---------------+----------- schema_name | name | view_name | name | is_insertable | boolean | is_updateable | boolean | is_deleteable | boolean | definition | text | is_system_view | boolean | view_oid | oid | owner | name | comment | text | pg_user_views Column | Type | -----------------+---------------+----------- schema_name | name | view_name | name | is_insertable | boolean | is_updateable | boolean | is_deleteable | boolean | definition | text | owner | name | comment | text | -- Josh Berkus Aglio Database Solutions San Francisco -
Re: Views, views, views! (long)
Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-05T04:56:13Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T05:01:47Z
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
-
Re: Views, views, views! (long)
Tim Allen <tim@proximity.com.au> — 2005-05-05T05:03:49Z
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/
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T05:12:16Z
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
-
Re: Views, views, views! (long)
Oleg Bartunov <oleg@sai.msu.su> — 2005-05-05T10:26:56Z
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
-
Re: Views, views, views! (long)
Peter Eisentraut <peter_e@gmx.net> — 2005-05-05T12:09:44Z
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/
-
Re: Views, views, views! (long)
Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-05T13:50:28Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T16:11:13Z
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
-
Re: Views, views, views! (long)
Andreas Pflug <pgadmin@pse-consulting.de> — 2005-05-05T16:55:38Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T17:17:44Z
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
-
Re: Views, views, views! (long)
Andreas Pflug <pgadmin@pse-consulting.de> — 2005-05-05T17:32:28Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T17:48:55Z
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
-
Re: Views, views, views! (long)
Peter Eisentraut <peter_e@gmx.net> — 2005-05-05T18:58:01Z
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/
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T19:37:55Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T19:55:12Z
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
-
Re: Views, views, views! (long)
Bruno Wolff III <bruno@wolff.to> — 2005-05-05T20:02:10Z
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.
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-05T20:08:49Z
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
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-05T20:15:27Z
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
-
Re: Views, views, views! (long)
David Fetter <david@fetter.org> — 2005-05-05T22:36:47Z
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!
-
Re: Views, views, views! (long)
Greg Sabino Mullane <greg@turnstep.com> — 2005-05-05T22:43:07Z
-----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-----
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-05T23:17:35Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-05T23:21:42Z
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
-
Re: Views, views, views! (long)
Hannu Krosing <hannu@tm.ee> — 2005-05-05T23:37:45Z
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>
-
Re: Views, views, views! (long)
elein <elein@varlena.com> — 2005-05-05T23:54:38Z
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. -
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-05T23:55:27Z
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
-
Re: Views, views, views! (long)
Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-05-06T02:00:33Z
> 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
-
Re: Views, views, views! (long)
Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-05-06T02:02:22Z
> 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
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-06T02:43:19Z
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
-
Re: Views, views, views! (long)
Robert Treat <xzilla@users.sourceforge.net> — 2005-05-06T03:27:43Z
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 -
Re: Views, views, views! (long)
Robert Treat <xzilla@users.sourceforge.net> — 2005-05-06T03:29:34Z
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 -
Re: Views, views, views! (long)
Joshua D. Drake <jd@commandprompt.com> — 2005-05-06T03:45:39Z
> > 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/ >
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-06T04:06:46Z
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
-
Re: Views, views, views! (long)
Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-06T04:13:14Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-06T04:22:40Z
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
-
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-06T06:20:09Z
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?"
-
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-06T06:25:10Z
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?"
-
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-06T06:31:08Z
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?"
-
Re: Views, views, views! (long)
Dawid Kuroczko <qnex42@gmail.com> — 2005-05-06T09:04:51Z
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_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE a.attrelid = '6338117' AND a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = i.indexrelid ORDER BY a.attnum SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname, pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a WHERE i.indexrelid = c.oid AND c.oid = '6261315' AND c.relam = a.oid AND i.indrelid = c2.oid ...and so on. Then refashion them to do the needed query. Then again she may look inside information_schema.* (columns?), but it is not as natural as one would like. And then again, as most people are lazy, she would probably use: select schemaname,tablename,attname from pg_stats where n_distinct = -1 and schemaname='public'; Which is simply the stupidest way, and of course the wrong one. Yet it gives an illusion of returning "quite right" data the easy way... Sometimes it may be terribly tempting... I would certainly like to see these views in PostgreSQL. Maybe as a contrib package (just as there are tsearch2 or intarray). I think such views would not be of much use for, say pgAdmin. Yet again for querying from perl/php or over "human carrier" it would be benefitial, I guess. My 0.03 PLN. ;) Regards, Dawid -
Re: Views, views, views! (long)
Andreas Pflug <pgadmin@pse-consulting.de> — 2005-05-06T10:20:09Z
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
-
Re: Views, views, views! (long)
Greg Sabino Mullane <greg@turnstep.com> — 2005-05-06T11:34:49Z
-----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-----
-
Re: Views, views, views! (long)
Robert Treat <xzilla@users.sourceforge.net> — 2005-05-06T13:08:10Z
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 -
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-06T16:07:02Z
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?"
-
Re: Views, views, views! (long)
Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-06T16:21:55Z
"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
-
Re: Views, views, views! (long)
Andreas Pflug <pgadmin@pse-consulting.de> — 2005-05-06T16:56:46Z
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.oid IS NOT NULL THEN 'T'::text WHEN ty.oid IS NOT NULL THEN 'y'::text WHEN ns.oid IS NOT NULL THEN 'n'::text WHEN pr.oid IS NOT NULL THEN 'p'::text WHEN la.oid IS NOT NULL THEN 'l'::text WHEN rw.oid IS NOT NULL THEN 'R'::text WHEN co.oid IS NOT NULL THEN 'C'::text || contype ELSE '' END AS type, COALESCE(coc.relname, clrw.relname) AS ownertable, COALESCE(cl.relname, conname, proname, tgname, typname, lanname, rulename, ns.nspname) AS refname, COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname FROM pg_depend dep LEFT JOIN pg_class cl ON dep.objid=cl.oid LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid LEFT JOIN pg_proc pr on dep.objid=pr.oid LEFT JOIN pg_namespace nsp ON pronamespace=nsp.oid LEFT JOIN pg_trigger tg ON dep.objid=tg.oid LEFT JOIN pg_type ty on dep.objid=ty.oid LEFT JOIN pg_namespace nst ON typnamespace=nst.oid LEFT JOIN pg_constraint co on dep.objid=co.oid LEFT JOIN pg_class coc ON conrelid=coc.oid LEFT JOIN pg_namespace nso ON connamespace=nso.oid LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class LEFT JOIN pg_namespace nsrw ON cl.relnamespace=nsrw.oid LEFT JOIN pg_language la ON dep.refobjid=la.oid LEFT JOIN pg_namespace ns ON dep.objid=ns.oid Isn't it a shame that this widely usable query isn't included in pgsql since 7.0? ;-) Regards, Andreas -
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-06T17:37:38Z
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?"
-
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-06T17:43:00Z
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?"
-
Re: Views, views, views! (long)
elein <elein@varlena.com> — 2005-05-06T17:49:25Z
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 >
-
Re: Views, views, views! (long)
elein <elein@varlena.com> — 2005-05-06T19:07:54Z
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 >
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-06T19:50:21Z
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
-
Re: Views, views, views! (long)
Robert Treat <xzilla@users.sourceforge.net> — 2005-05-06T21:44:43Z
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 -
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-08T16:35:15Z
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?"
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-08T17:47:04Z
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
-
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-08T22:41:09Z
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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-10T04:59:51Z
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
-
Re: Views, views, views! (long)
Peter Eisentraut <peter_e@gmx.net> — 2005-05-10T14:55:40Z
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/
-
Re: Views, views, views! (long)
Peter Eisentraut <peter_e@gmx.net> — 2005-05-10T14:56:45Z
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/
-
Re: Views, views, views! (long)
decibel <decibel@decibel.org> — 2005-05-10T17:36:39Z
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?" -
Re: Views, views, views! (long)
Andrew - Supernews <andrew+nonews@supernews.com> — 2005-05-10T18:00:36Z
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
-
Re: Views, views, views! (long)
Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-10T19:58:50Z
"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
-
Re: Views, views, views! (long)
Josh Berkus <josh@agliodbs.com> — 2005-05-10T20:12:32Z
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
-
Re: Views, views, views! (long)
Manfred Koizar <mkoi-pg@aon.at> — 2005-05-15T19:21:53Z
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. Servus Manfred