Thread: Extending System Views: proposal for 8.1/8.2
Folks, This is for 8.1, or for 8.2 if we have a no-initdb cycle for 8.1. I'm proposing to expand both the coverage and number of "system views". Our system views are an extremely useful way to get data about the system if you're not on PSQL. They are a better idea than using the underlying system tables, both becuase the system table output can be kind of cryptic, and because the system tables may change but it will be easy to maintain the views the same. Therefore, I want to run my proposed design past the team, because I'd like to build system views we can live with for the next 3-4 versions, which will allow GUI and library builders to have a reliable, static interface onto the system objects. Suggestions & adjustments, please! It shouldn't take me long to write these with a clear spec. (oh, and information_schema really doesn't cover this because the SQL spec is rather limited in what objects it describes) pg_tables ADD comment pg_stats ADD statstarget for each column (the SET STATISTICS for each column) pg_user ADD groups (array) pg_functions --> create new view schemaname functionname functionowner parameters (array) returntype functionsettings (things like STABLE) functionsource comment pg_views ADD comment pg_columns --> new view ** schemaname tablename columnname datatype typemodifiers (NOT NULL, default, etc) comment pg_aggregates --> new view ** schemaname aggregatename aggregateowner datatype initvalue transfunction finalfunction comment pg_operators --> new view ** schemaname operatorname operatorowner operatortype datatypes (array) operatorfunction comment pg_schemas --> new view schemaname schemaowner defaulttablespace comment pg_triggers --> new view *** schemaname tablename triggername triggerowner triggerfunction conditions (update, insert, etc.) modifiers (deferrable, etc.) enabled comment pg_foriegnkeys --> new view **** parentschema parenttable parentcolumns (array) childschema childtable childcolumns (array) Views I think will be wanted by I've not really figured out how to define yet: pg_types pg_domains pg_constraints pg_groups NOTES & QUESTIONS: ** = for these three views, there are an enourmous number of system aggregates, operators, etc. I'm wondering if I should hide the system ones, or simply trust the user to filter by schema? *** = since there will be a seperate FK view, pg_triggers will omit FK constrainttriggers. **** = I've used the non-canon terms "parent" and "child" here. The problem is that the standard terms are completely confusing and unintuitive, such as "referring" and "referenced". Other suggestions are welcome. So, feedback before I start writing SQL? Oh, also what file are the system views defined in? -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Fri, Jan 21, 2005 at 12:17:08PM -0800, Josh Berkus wrote: > Folks, > > This is for 8.1, or for 8.2 if we have a no-initdb cycle for 8.1. > > I'm proposing to expand both the coverage and number of "system views". Our > system views are an extremely useful way to get data about the system if > you're not on PSQL. They are a better idea than using the underlying system > tables, both becuase the system table output can be kind of cryptic, and > because the system tables may change but it will be easy to maintain the > views the same. > > Therefore, I want to run my proposed design past the team, because I'd like to > build system views we can live with for the next 3-4 versions, which will > allow GUI and library builders to have a reliable, static interface onto the > system objects. Suggestions & adjustments, please! It shouldn't take me > long to write these with a clear spec. > > (oh, and information_schema really doesn't cover this because the SQL spec is > rather limited in what objects it describes) > > pg_tables > ADD comment > > pg_stats > ADD statstarget for each column > (the SET STATISTICS for each column) > > pg_user > ADD groups (array) > > pg_functions --> create new view > schemaname > functionname > functionowner > parameters (array) > returntype > functionsettings (things like STABLE) > functionsource > comment > > pg_views > ADD comment > > pg_columns --> new view ** > schemaname > tablename > columnname > datatype > typemodifiers (NOT NULL, default, etc) > comment > > pg_aggregates --> new view ** > schemaname > aggregatename > aggregateowner > datatype > initvalue > transfunction > finalfunction > comment > > pg_operators --> new view ** > schemaname > operatorname > operatorowner > operatortype > datatypes (array) > operatorfunction > comment > > pg_schemas --> new view > schemaname > schemaowner > defaulttablespace > comment > > pg_triggers --> new view *** > schemaname > tablename > triggername > triggerowner > triggerfunction > conditions (update, insert, etc.) > modifiers (deferrable, etc.) > enabled > comment > > pg_foriegnkeys --> new view **** > parentschema > parenttable > parentcolumns (array) > childschema > childtable > childcolumns (array) > > Views I think will be wanted by I've not really figured out how to define yet: > pg_types > pg_domains > pg_constraints > pg_groups I don't know how this fits in, but it would be *very* nice to have SQLSTATE meta-information available via SQL. I've sent in a patch for this. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
I'm glad to see a types view. A while ago I was trying to figure out a way to query the pg_* views to see if a particular function existed. I quickly got stuck trying to figure out how to properly handle the arguments array. The solution Tom gave in this case was just to hard-code the OID for the type I needed. It works, but it seems rather ugly. I think it would be good if pg_function included an array of parameter types of the standard parameter names, as well as the type OIDs. On Fri, Jan 21, 2005 at 12:17:08PM -0800, Josh Berkus wrote: > ** = for these three views, there are an enourmous number of system > aggregates, operators, etc. ? I'm wondering if I should hide the system ones, > or simply trust the user to filter by schema? I think it would be handy to have a set of views defined that shows everything, and have the 'normal set' (ie, the views with the easiest name to type in) defined to hide the system stuff. I suspect that most of the time people are using these views they don't care about the system stuff. > *** = since there will be a seperate FK view, pg_triggers will omit FK > constrainttriggers. I think it would be useful if there was a version of the view that showed what these triggers were. If you want to see all the triggers operating on a table, for example. I guess this is a bit of a grey area, since I don't know of any other database that handles RI using triggers. > **** = I've used the non-canon terms "parent" and "child" here. ? The problem > is that the standard terms are completely confusing and unintuitive, such as > "referring" and "referenced". ? ? Other suggestions are welcome. I think parent and child is fine. On another naming note; the naming convention for system stuff has always driven me nuts. Some the letter prefix (ie: tab for tables) in front of every field name, with no underscores or anything. Extensive use of abbreviations that you need to remember (ie: indnatts, indexprs, indpred). No use of underscores (indisunique). Yet the view and table names do use underscores. I realize that there's probably a pretty tight mapping between catalog *tables* and internals and that changing anything there would probably have a huge impact on code. Of course there's also existing code that uses the pg_catalog stuff that's defined today. What I'm hoping is that with the amount of work involved in the changes Josh is suggesting, instituting a more rational naming scheme wouldn't be that much extra effort, at least for things that are being added. One possibility might be to leave the existing views alone (and possibly deprecate them), and just create new views. What I'd like to see is names that are spelled out and underscore delimited. BTW, this is something I can actually work on myself, and I'd be happy to work on the code as consensus is reached on what the different views should look like. My $2.00. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Jim C. Nasby wrote: > On another naming note; the naming convention for system stuff has > always driven me nuts. Some the letter prefix (ie: tab for tables) in > front of every field name, with no underscores or anything. Extensive > use of abbreviations that you need to remember (ie: indnatts, indexprs, > indpred). No use of underscores (indisunique). Yet the view and table > names do use underscores. I agree the naming conventions for system catalog columns is less than optimal, but it seems a net loss to rename columns that already exist (given the amount of code that would need to be updated, both within the tree and in admin utilities and the like). Renaming all the system catalogs and providing backward compatibility views would mean you'd only need to modify the PG source, although of course those modifications would be pretty time-consuming... In any case, that's no reason not to try for better names in newly-added system objects. -Neil
On Fri, 21 Jan 2005 12:17:08 -0800, Josh Berkus wrote: > I'm proposing to expand both the coverage and number of "system views". Generally: Nice. But have you considered if the INFORMATION_SCHEMA could be used? Unfortunately, the INFORMATION_SCHEMA currently has a major problem in its usefulness in PostgreSQL: http://troels.arvin.dk/db/rdbms/#cli-list_of_tables-postgresql-gotchas However, that could (and in my opinion: _should_) be fixed be enforcing schema-wide constraint identifier uniqueness. > pg_stats > ADD statstarget for each column > (the SET STATISTICS for each column) This reminds me: It would be nice if it were somehow possible to determine when (if ever) statistics have been gathered for a given schema object. This needs changes to more than VIEWs, though. > pg_columns --> new view ** > schemaname > tablename > columnname > datatype > typemodifiers (NOT NULL, default, etc) > comment Do you propose that typemodifiers be one column? - If would prefer if it were several columns. And it would be useful if it were easy to determine if a column is- solely - or part of - a uniqueness constraint- solely - or part of - a foreign key (pointing where?)- ifit is subject to a (set of) CHECK constraints I could use this to more easily build user interfaces (forms). -- Greetings from Troels Arvin, Copenhagen, Denmark
On Sat, Jan 22, 2005 at 08:46:21PM +1100, Neil Conway wrote: > Jim C. Nasby wrote: > >On another naming note; the naming convention for system stuff has > >always driven me nuts. Some the letter prefix (ie: tab for tables) in > >front of every field name, with no underscores or anything. Extensive > >use of abbreviations that you need to remember (ie: indnatts, indexprs, > >indpred). No use of underscores (indisunique). Yet the view and table > >names do use underscores. > > I agree the naming conventions for system catalog columns is less than > optimal, but it seems a net loss to rename columns that already exist > (given the amount of code that would need to be updated, both within the > tree and in admin utilities and the like). Renaming all the system > catalogs and providing backward compatibility views would mean you'd > only need to modify the PG source, although of course those > modifications would be pretty time-consuming... > > In any case, that's no reason not to try for better names in newly-added > system objects. Just to be clear, I'm not suggesting renaming anything in any of the existing pg_catalog objects. I'm suggesting creating a new, easier to use set of views that would sit on top of 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?"
Jim, > Just to be clear, I'm not suggesting renaming anything in any of the > existing pg_catalog objects. I'm suggesting creating a new, easier to > use set of views that would sit on top of pg_catalog. I have no objection to using easier to read names for the system views.(This is the user-friendly views, folks, not the actualsystem objects!). The reason I suggested the names I did was to be consistent. Thing is, at least for the next version, if we are changing the naming conventions, we need to leave the old views alone, at least for one version (pg_tables, pg_views, etc.). This means a new view name scheme for the new views. Suggestions? I might suggest simply "tables" "triggers" "types" etc. The plurals of these reserved words are no, AFAIK, reserved. And if users are creating identically named objects in public, they just need to remember to use the schema. Oh, also for the "Parameters (array)" etc.? I was planning on having text names there, *not* an array of OIDs or whatever. The purpose of these views is to be user-friendly. --Josh
"Josh Berkus" <josh@agliodbs.com> writes: > I might suggest simply "tables" "triggers" "types" etc. The plurals > of these reserved words are no, AFAIK, reserved. And if users are > creating identically named objects in public, they just need to > remember to use the schema. Only if you put them in some other schema. We have specifically promised not to create any tables/views in pg_catalog that do not have names beginning with "pg_" --- see http://developer.postgresql.org/docs/postgres/ddl-schemas.html#DDL-SCHEMAS-CATALOG regards, tom lane
On Sat, Jan 22, 2005 at 01:36:54PM -0800, Josh Berkus wrote: > Jim, > > > Just to be clear, I'm not suggesting renaming anything in any of the > > existing pg_catalog objects. I'm suggesting creating a new, easier to > > use set of views that would sit on top of pg_catalog. > > I have no objection to using easier to read names for the system views. > (This is the user-friendly views, folks, not the actual system > objects!). The reason I suggested the names I did was to be > consistent. Out of curiosity, what's the relation between the tables in pg_catalog and the 'actual system objects'? I ass-u-me'd that these tables were the backing store for the real information, but maybe that's not the case. > Thing is, at least for the next version, if we are changing the naming > conventions, we need to leave the old views alone, at least for one > version (pg_tables, pg_views, etc.). This means a new view name scheme > for the new views. Suggestions? If we're dropping the pg_, maybe call the new schema just 'catalog'? > I might suggest simply "tables" "triggers" "types" etc. The plurals > of these reserved words are no, AFAIK, reserved. And if users are > creating identically named objects in public, they just need to > remember to use the schema. Actually, the view names don't bother me at all. Granted, pg_ is 3 extra characters to type, but the names are crystal clear. What I don't like are the field names inside the views, and especially inside the pg_catalog tables. > Oh, also for the "Parameters (array)" etc.? I was planning on having > text names there, *not* an array of OIDs or whatever. The purpose of > these views is to be user-friendly. I think these views are also very useful in certain programming situations, in which also having the OIDs might be very useful. Another option would be to have functions that given a array of names would return a array of OIDs. -- 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?"
In <20050122232132.GS67721@decibel.org>, on 01/22/05 at 05:21 PM, "Jim C. Nasby" <decibel@decibel.org> said: >On Sat, Jan 22, 2005 at 01:36:54PM -0800, Josh Berkus wrote: > Jim, >> >> > Just to be clear, I'm not suggesting renaming anything in any of the >> > existing pg_catalog objects. I'm suggesting creating a new, easier to >> > use set of views that would sit on top of pg_catalog. >> >> I have no objection to using easier to read names for the system views. >> (This is the user-friendly views, folks, not the actual system >> objects!). The reason I suggested the names I did was to be >> consistent. >Out of curiosity, what's the relation between the tables in pg_catalog >and the 'actual system objects'? I ass-u-me'd that these tables were the >backing store for the real information, but maybe that's not the case. >> Thing is, at least for the next version, if we are changing the naming >> conventions, we need to leave the old views alone, at least for one >> version (pg_tables, pg_views, etc.). This means a new view name scheme >> for the new views. Suggestions? >If we're dropping the pg_, maybe call the new schema just 'catalog'? That will break all of the older ODBC drivers. -- ----------------------------------------------------------- lsunley@mb.sympatico.ca -----------------------------------------------------------
On Sat, Jan 22, 2005 at 05:21:32PM -0600, Jim C. Nasby wrote: > Out of curiosity, what's the relation between the tables in pg_catalog > and the 'actual system objects'? I ass-u-me'd that these tables were the > backing store for the real information, but maybe that's not the case. They are. > I think these views are also very useful in certain programming > situations, in which also having the OIDs might be very useful. Another > option would be to have functions that given a array of names would > return a array of OIDs. So why not have both in the view? It's not like you are storing duplicated data anyway. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)
"Jim C. Nasby" <decibel@decibel.org> writes:
> If we're dropping the pg_, maybe call the new schema just 'catalog'?
Any new schemas introduced by PG itself will be named pg_something.
This is not open to negotiation --- it's what we've promised to users
to avoid tromping on their schema namespace.
regards, tom lane
Tom, > Any new schemas introduced by PG itself will be named pg_something. > This is not open to negotiation --- it's what we've promised to users > to avoid tromping on their schema namespace. I can see the sense in that. So, there's four ways I can see to do things: 1) leave the existing views (pg_tables, pg_views, etc.) the way they are except for adding columns. Create new views based on the naming scheme of the old. 2) create new views in pg_catalog, using new names. The problem with this is that the most intuitive names (pg_tables, pg_views) are taken by the old views and I'm not sure what to name the new ones. 3) create a new schema with the system views in it, called for example pg_system_views. This seems cluttered to me; a whole new schema just for a dozen views? 4) ignore backwards compatibility and just re-write the old views. I can hear the shouting already ... So, a choice of annoying options. Does anyone else on the channel have opinions? -- Josh Berkus Aglio Database Solutions San Francisco
I may be missing something here, but haven't we always stated that using 'SELECT *' should be frown'd upon for the most part? Is there a reason why adding a column/field to an existing view should be considered a bad thing? As long as we don't remove existing colums that an app could be using, but only adding a column, there shouldn't be any issues with backwards compatibility, shoudl there? On Sun, 23 Jan 2005, Josh Berkus wrote: > Tom, > >> Any new schemas introduced by PG itself will be named pg_something. >> This is not open to negotiation --- it's what we've promised to users >> to avoid tromping on their schema namespace. > > I can see the sense in that. So, there's four ways I can see to do things: > > 1) leave the existing views (pg_tables, pg_views, etc.) the way they are > except for adding columns. Create new views based on the naming scheme of > the old. > > 2) create new views in pg_catalog, using new names. The problem with this is > that the most intuitive names (pg_tables, pg_views) are taken by the old > views and I'm not sure what to name the new ones. > > 3) create a new schema with the system views in it, called for example > pg_system_views. This seems cluttered to me; a whole new schema just for a > dozen views? > > 4) ignore backwards compatibility and just re-write the old views. I can > hear the shouting already ... > > So, a choice of annoying options. Does anyone else on the channel have > opinions? > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Hi, On Sun, Jan 23, 2005 at 12:16:31PM -0800, Josh Berkus wrote: > > 4) ignore backwards compatibility and just re-write the old views. I can > hear the shouting already ... > > So, a choice of annoying options. Does anyone else on the channel have > opinions? Isn't it a usefull option to introduce a postgresql-conf parameter to set the pg-views version? I mean, in a pg 7.x-comaptibility-mode you would only see the known views with their old content. If you set it to 8.x, you will see the new versions. So developers will get more time to change their applications from the old views to the new ones while being able to use new features. Regards, Yann
I'm going to reply to 3 emails in one here... Out of Josh's 4 options, I think a new schema makes the most sense. Start with a clean plate. Yes, we'll end up with an ugly schema name, but after the exiting pg_catalog is removed in a few versions, we can go back to pg_catalog. The idea of using a GUC to control which version of the schema you get is also very interesting, though I don't know how workable it is. It does have a downside, though... if you have a bunch of code that's using pg_catalog, you'd have no choice but to migrate all of it at once. If you have both the old and new versions of these tables/views present at the same time then you can slowly migrate that code over. Only adding columns to the existing views/tables is also an interesting possibility. One issue is that it probably wouldn't work very well for the tables in pg_catalog, though I guess SELECT rules could be written to handle those. The other issue is that while SELECT * in code is almost always a bad idea, SELECT * is human-friendly. I suspect that basically every field in the existing tables/views will be renamed, which means SELECT * in psql will now give you 2x the number of columns you need. Though, this could be avoided by having a seperate set of 'human-readable' views. Having a seperate set of human-readable views would also mean we could provide more human-friendly formatting in one version, while the other version has everything you'd need to make it the most useful from a programatic standpoint. An example that comes to mind is the earlier discussion about function parameters. In a human-readable version of pg_function, you'd probably just want schema_name, function_name, parameters[], and return_type, where both parameters[] and return_type would be the name of the types. But if you're looking at functions programatically, it would be good to also have parameter_oids[] and return_type_oid. Personally, I'm leaning towards option 3: add new fields to pg_catalog.* to support the new naming convention and new features, and add pg_human. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <decibel@decibel.org> writes:
> Start with a clean plate. Yes, we'll end up with an ugly schema name,
> but after the exiting pg_catalog is removed in a few versions, we can go
> back to pg_catalog.
Huh? pg_catalog isn't going away, and none of this discussion has
anything to do with changing the system catalogs themselves. In any
case, creating these views with the idea that we will change their
locations later is a nonstarter. People are going to be putting
the fully qualified paths into their applications.
> The idea of using a GUC to control which version of the schema you get
> is also very interesting, though I don't know how workable it is.
It could be spelled "schema_path" ... otherwise I don't see any way to
do it. But I'm not sure it helps any to make two separate schemas.
Most admin-type apps wouldn't want to depend on the value of schema_path
(psql sure wouldn't, for instance) so they'd still have to change if
only to nail down the schema they want in each query.
regards, tom lane
Marc G. Fournier wrote: > I may be missing something here, but haven't we always stated that > using 'SELECT *' should be frown'd upon for the most part? No, we have never stated that. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes: > Marc G. Fournier wrote: >> I may be missing something here, but haven't we always stated that >> using 'SELECT *' should be frown'd upon for the most part? > No, we have never stated that. We do however point out in the docs that SELECT * is vulnerable to addition of columns, eg footnote 1 here: http://www.postgresql.org/docs/8.0/static/tutorial-select.html We've always felt free to add columns to system catalogs at need, and I don't see that adding some to system views is worse. regards, tom lane