Thread: Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
Troels, Others, > 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 Actually, I did. However, the format and columns of INFORMATION_SCHEMA are defined by the SQL Standard, which will not cover a lot of PostgreSQL objects (such as custom types or operators) and covers a lot of others in rather awkward form. For that matter, your own editorial points out that we should really be UPPERCASEing all of the object names in information_schema, which would be SQL-spec but not generally useful. > 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. Well, you can always query pg_stats. > 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?) > - if it is subject to a (set of) CHECK constraints Yeah, I gave this some thought. The problem as I see it is that in the future we may have additional types of typemodifiers which aren't covered, and I don't want to get in the habit of adding more and more columns to the view. However, that's not really an excuse; it might be better to: pg_columns --> new viewschemanametablenamecolumnnamedatatypenotnullreferences (name which links pg_foreignkeys, or boolean?)defaultconstraints(array, references pg_constraints)othermodifiers (string of other column modifiers, for when suchexist)comment In a way, though, it might be better for "references" to be a boolean column, and users can query pg_foriegnkeys to find the exact reference. ==== BTW, People, I really don't see the point in prodiving a dual list -- that is, a list of OIDs in addition to the list of names provided in the columns of each view. The idea of these views is to keep the users *away* from technical details like OIDs, which can and will change with the advancing versions of PostgreSQL. -- Josh Berkus Aglio Database Solutions San Francisco
On Sun, Jan 23, 2005 at 12:43:15PM -0800, Josh Berkus wrote: > BTW, People, I really don't see the point in prodiving a dual list -- that is, > a list of OIDs in addition to the list of names provided in the columns of > each view. The idea of these views is to keep the users *away* from > technical details like OIDs, which can and will change with the advancing > versions of PostgreSQL. It's a question of if these views will also be used programatically. ISTM that OIDs are the preffered method of refering to things in code (in fact, aren't there some functions that only take OIDs?). If we want to make names the cannonical way to reference things in code, then I agree that there's not much use to OIDs. Is the long term plan to remove OIDs entirely? -- 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:
> Is the long term plan to remove OIDs entirely?
No. OIDs will be the real primary keys of most system catalogs for the
foreseeable future. The only discussion that's going on concerns
deprecating their use in user tables.
regards, tom lane
Jim, > It's a question of if these views will also be used programatically. > ISTM that OIDs are the preffered method of refering to things in code > (in fact, aren't there some functions that only take OIDs?). If we want > to make names the cannonical way to reference things in code, then I > agree that there's not much use to OIDs. Hmmm .... I think that you and I have different ideas about the purpose of the system views. My idea is to provide a stable (through multiple versions of pg), human-readable view of the system objects. You obviously want to do more -- I'd like details on what that more is, so that we can talk about it. > Is the long term plan to remove OIDs entirely? No, but we want to discourage users from using them actively. Where they're apparent, users will be inclined to write code that references OIDs *by number* which will survive neither backup/restore, nor upgrades in pg versions. So where we can encourage users to refer to objects by name, we should. I see the OIDs, in fact, as one of the reasons to create the additional system views -- so that users aren't confused by them. If there are functions that need OIDs, my inclination would be to write shell functions for those that accept fully-qualified object names. -- Josh Berkus Aglio Database Solutions San Francisco
On Sun, Jan 23, 2005 at 02:37:28PM -0800, Josh Berkus wrote: > Jim, > > > It's a question of if these views will also be used programatically. > > ISTM that OIDs are the preffered method of refering to things in code > > (in fact, aren't there some functions that only take OIDs?). If we want > > to make names the cannonical way to reference things in code, then I > > agree that there's not much use to OIDs. > > Hmmm .... I think that you and I have different ideas about the purpose of the > system views. My idea is to provide a stable (through multiple versions of > pg), human-readable view of the system objects. You obviously want to do > more -- I'd like details on what that more is, so that we can talk about it. Really, my only goal is to make using the system views/tables programatically easier by coming up with a better naming convention. This isn't directly related to the human-readable stuff, other than fields that would be common between both sets of views. Perhaps a good way to accomplish both goals is to have the set of human-readable views, and to add columns to the system tables/views that conform with the new, more logical naming convention. This way people accessing system information programmatically can use pg_catalog (and migrate to the new naming convention), while people who are doing ad-hoc queries can just hit the human-readable stuff. Make sense? -- 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, > Perhaps a good way to accomplish both goals is to have the set of > human-readable views, and to add columns to the system tables/views that > conform with the new, more logical naming convention. This way people > accessing system information programmatically can use pg_catalog (and > migrate to the new naming convention), while people who are doing ad-hoc > queries can just hit the human-readable stuff. If you think that anyone on this list is going to let us re-name columns in the system *tables*, you're on more pain meds than I realized ... And in what way is using fully qualified names programmatically a problem? -- Josh Berkus Aglio Database Solutions San Francisco
On Sun, Jan 23, 2005 at 02:53:11PM -0800, Josh Berkus wrote: > Jim, > > > Perhaps a good way to accomplish both goals is to have the set of > > human-readable views, and to add columns to the system tables/views that > > conform with the new, more logical naming convention. This way people > > accessing system information programmatically can use pg_catalog (and > > migrate to the new naming convention), while people who are doing ad-hoc > > queries can just hit the human-readable stuff. > > If you think that anyone on this list is going to let us re-name columns in > the system *tables*, you're on more pain meds than I realized ... What I figured. > And in what way is using fully qualified names programmatically a problem? It's not a problem; my only complaint is that the field names are awkward as hell, which is why I suggested a new naming convention. If it comes down to it, I'll settle for better names in the human readable stuff and hope it eventually can be migrated to pg_catalog stuff. I just figured changing both at the same time might make more sense. -- 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?"