|
|
 | | From: | Josh Berkus | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Sun, 23 Jan 2005 12:43:15 -0800 |
|
|
 | 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 view
schemaname
tablename
columnname
datatype
notnull
references (name which links pg_foreignkeys, or boolean?)
default
constraints (array, references pg_constraints)
othermodifiers (string of other column modifiers, for when such exist)
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | Jim C. Nasby | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Sun, 23 Jan 2005 15:42:48 -0600 |
|
|
| 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?"
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
| | From: | Tom Lane | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Sun, 23 Jan 2005 17:06:02 -0500 |
|
|
| "Jim C. Nasby" 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
| | From: | Josh Berkus | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Sun, 23 Jan 2005 14:37:28 -0800 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
|
| | From: | Jim C. Nasby | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Sun, 23 Jan 2005 16:45:26 -0600 |
|
|
| 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?"
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | Josh Berkus | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Sun, 23 Jan 2005 14:53:11 -0800 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
| | From: | Jim C. Nasby | | Subject: | Re: [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2 | | Date: | Mon, 24 Jan 2005 00:21:07 -0600 |
|
|
| 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?"
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
|
