Extending System Views: proposal for 8.1/8.2

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?)
- if it 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 actual system
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

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 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?"

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

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

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?"

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:

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

"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?"

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