Thread: Adding column comment to information_schema.columns

Adding column comment to information_schema.columns

From
Justin Clift
Date:
Hi all,

Not sure how worthwhile others will find this small patch (to CVS HEAD),
but we found it useful.  It adds the column comments to the
information_schema.columns view.

Hope it's useful.

:-)

Regards and best wishes,

Justin Clift
*** information_schema.sql.orig    2004-07-01 11:59:26.000000000 +1000
--- information_schema.sql    2004-07-01 12:33:01.000000000 +1000
***************
*** 442,448 ****

             CAST(null AS cardinal_number) AS maximum_cardinality,
             CAST(a.attnum AS sql_identifier) AS dtd_identifier,
!            CAST('NO' AS character_data) AS is_self_referencing

      FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
           pg_class c, pg_namespace nc, pg_user u,
--- 442,450 ----

             CAST(null AS cardinal_number) AS maximum_cardinality,
             CAST(a.attnum AS sql_identifier) AS dtd_identifier,
!            CAST('NO' AS character_data) AS is_self_referencing,
!
!            col_description(a.attrelid, a.attnum) AS column_comment

      FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum),
           pg_class c, pg_namespace nc, pg_user u,

Re: Adding column comment to information_schema.columns

From
Dennis Bjorklund
Date:
On Thu, 1 Jul 2004, Justin Clift wrote:

> but we found it useful.  It adds the column comments to the 
> information_schema.columns view.

Doesn't the specification say exactly what columns should exist?

Lots of things in the old system tables are not visible in the
information_schema because of this. Not that I think a comment column
would hurt, but anyway.

-- 
/Dennis Björklund



Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> Not sure how worthwhile others will find this small patch (to CVS HEAD), 
> but we found it useful.  It adds the column comments to the 
> information_schema.columns view.

Is column comment in the standard?  If not, we cannot of course add it...

Chris



Re: Adding column comment to information_schema.columns

From
Justin Clift
Date:
Tom Lane wrote:
> Justin Clift <jc@telstra.net> writes:
> 
>>Not sure how worthwhile others will find this small patch (to CVS HEAD), 
>>but we found it useful.  It adds the column comments to the 
>>information_schema.columns view.
> 
> 
> This question has been touched on before, but I guess it's time to face
> it fair and square: is it reasonable for an SQL implementation to add
> implementation-specific columns to an information_schema view?  One
> could certainly argue that the entire point of information_schema is
> to be *standard*, not more, not less.  OTOH I do not know if adding
> an extra column is likely to break anyone's application.  Comments?

Well, I suppose it reduces application portability if anyone starts 
relying on it.

?

Regards and best wishes,

Justin Clift


>             regards, tom lane
> 
> 




Re: Adding column comment to information_schema.columns

From
Tom Lane
Date:
Justin Clift <jc@telstra.net> writes:
> Not sure how worthwhile others will find this small patch (to CVS HEAD), 
> but we found it useful.  It adds the column comments to the 
> information_schema.columns view.

This question has been touched on before, but I guess it's time to face
it fair and square: is it reasonable for an SQL implementation to add
implementation-specific columns to an information_schema view?  One
could certainly argue that the entire point of information_schema is
to be *standard*, not more, not less.  OTOH I do not know if adding
an extra column is likely to break anyone's application.  Comments?
        regards, tom lane


Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> This question has been touched on before, but I guess it's time to face
> it fair and square: is it reasonable for an SQL implementation to add
> implementation-specific columns to an information_schema view?  One
> could certainly argue that the entire point of information_schema is
> to be *standard*, not more, not less.  OTOH I do not know if adding
> an extra column is likely to break anyone's application.  Comments?

I don't really see the point in adding it.  If that, why not everything?

Chris



Re: Adding column comment to information_schema.columns

From
Andreas Pflug
Date:
Justin Clift wrote:

> Tom Lane wrote:
>
>>
>> This question has been touched on before, but I guess it's time to face
>> it fair and square: is it reasonable for an SQL implementation to add
>> implementation-specific columns to an information_schema view?  One
>> could certainly argue that the entire point of information_schema is
>> to be *standard*, not more, not less.  OTOH I do not know if adding
>> an extra column is likely to break anyone's application.  Comments?
>
>
> Well, I suppose it reduces application portability if anyone starts 
> relying on it.


We're advertising to do pure ANSI, so we'd mislead people if we supplied 
non-standard columns.

Regards,
Andreas




Re: Adding column comment to information_schema.columns

From
"Dave Page"
Date:

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: 01 July 2004 05:33
> To: Justin Clift
> Cc: PostgreSQL Hackers Mailing List
> Subject: Re: [HACKERS] Adding column comment to
> information_schema.columns
>
> Justin Clift <jc@telstra.net> writes:
> > Not sure how worthwhile others will find this small patch (to CVS
> > HEAD), but we found it useful.  It adds the column comments to the
> > information_schema.columns view.
>
> This question has been touched on before, but I guess it's
> time to face it fair and square: is it reasonable for an SQL
> implementation to add implementation-specific columns to an
> information_schema view?  One could certainly argue that the
> entire point of information_schema is to be *standard*, not
> more, not less.  OTOH I do not know if adding an extra column
> is likely to break anyone's application.  Comments?

If you write code to use it, then by definition you are writing code
that is PostgreSQL specific and therefore not standard. If someone then
writes their code to PostgreSQLs implementation of the
information_schema, wanting reasonably portable code, but not realising
we've extended the standard then they could be in for surprise when they
come to run their code on another platform.

Ugh, just read that back - I need coffee....

I say keep it standard.

Regards Dave


Re: Adding column comment to information_schema.columns

From
jearl@bullysports.com
Date:
Andreas Pflug <pgadmin@pse-consulting.de> writes:

> Justin Clift wrote:
>
>> Tom Lane wrote:
>>
>>>
>>> This question has been touched on before, but I guess it's time to
>>> face it fair and square: is it reasonable for an SQL
>>> implementation to add implementation-specific columns to an
>>> information_schema view?  One could certainly argue that the
>>> entire point of information_schema is to be *standard*, not more,
>>> not less.  OTOH I do not know if adding an extra column is likely
>>> to break anyone's application.  Comments?
>>
>>
>> Well, I suppose it reduces application portability if anyone starts
>> relying on it.
>
>
> We're advertising to do pure ANSI, so we'd mislead people if we
> supplied non-standard columns.

Yes, but if folks wanted to stick to the standard PostgreSQL would
still work.  The only difference is that people who aren't concerned
about being more tied to PostgreSQL would get some extra features.

There is a huge difference between adhering to a standard and limiting
yourself to a standard.  The real question is whether PostgreSQL's
goal is to support SQL standards, or whether PostgreSQL's goal is to
give PostgreSQL users a useful set of tools.

Jason Earl


Re: Adding column comment to information_schema.columns

From
Tom Lane
Date:
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Thu, 1 Jul 2004 jearl@bullysports.com wrote:
>> There is a huge difference between adhering to a standard and limiting
>> yourself to a standard.

> Having pg specific system tables (as we do) is something we need of
> course, for things that are not in the specification. Can't we simply have
> that outside of the standard information_schema. No one is saying that the 
> comment and other properties should not be available.

I agree.  The stuff is certainly accessible in PG-specific tables, so
the argument that we are missing functionality doesn't hold any water
IMHO.  The question is whether we have to keep information_schema
pristine.  I think that you and Stephan have made enough concrete
points that the answer to that has to be "stick to the standard".
        regards, tom lane


Re: Adding column comment to information_schema.columns

From
Bruno Wolff III
Date:
On Thu, Jul 01, 2004 at 10:38:02 -0600, jearl@bullysports.com wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> 
> Yes, but if folks wanted to stick to the standard PostgreSQL would
> still work.  The only difference is that people who aren't concerned
> about being more tied to PostgreSQL would get some extra features.

Is there any provision in the information schema part of the standard for
vendor specific extensions?


Re: Adding column comment to information_schema.columns

From
Dennis Bjorklund
Date:
On Thu, 1 Jul 2004 jearl@bullysports.com wrote:

> > We're advertising to do pure ANSI, so we'd mislead people if we
> > supplied non-standard columns.
> 
> Yes, but if folks wanted to stick to the standard PostgreSQL would
> still work.  The only difference is that people who aren't concerned
> about being more tied to PostgreSQL would get some extra features.
> 
> There is a huge difference between adhering to a standard and limiting
> yourself to a standard.

What if we add a column and then in the next version of SQL they add a 
column with the same name but a different semantics (not likely with the 
name "comment", but that's not the question here).

Having pg specific system tables (as we do) is something we need of
course, for things that are not in the specification. Can't we simply have
that outside of the standard information_schema. No one is saying that the 
comment and other properties should not be available.

-- 
/Dennis Björklund



Re: Adding column comment to information_schema.columns

From
Stephan Szabo
Date:
On Thu, 1 Jul 2004 jearl@bullysports.com wrote:

> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
> > Justin Clift wrote:
> >
> >> Tom Lane wrote:
> >>
> >>>
> >>> This question has been touched on before, but I guess it's time to
> >>> face it fair and square: is it reasonable for an SQL
> >>> implementation to add implementation-specific columns to an
> >>> information_schema view?  One could certainly argue that the
> >>> entire point of information_schema is to be *standard*, not more,
> >>> not less.  OTOH I do not know if adding an extra column is likely
> >>> to break anyone's application.  Comments?
> >>
> >>
> >> Well, I suppose it reduces application portability if anyone starts
> >> relying on it.
> >
> >
> > We're advertising to do pure ANSI, so we'd mislead people if we
> > supplied non-standard columns.
>
> Yes, but if folks wanted to stick to the standard PostgreSQL would
> still work.  The only difference is that people who aren't concerned

That might not be true.  It is possible to write queries that might work
on a database without extra columns and would fail or act differently with
extra columns depending on things like the names of the added columns
(possibly altering natural join), the positions of the column (possibly
altering sql92 order by ordinal position behavior) and the existance of
the column itself (possibly altering queries that use select * on one
branch of a union/intersect/except query for example).


Re: Adding column comment to information_schema.columns

From
Jochem van Dieten
Date:
On Thu, 1 Jul 2004 12:23:10 -0500, Bruno Wolff III <bruno@wolff.to> wrote:
> 
> Is there any provision in the information schema part of the standard for
> vendor specific extensions?

Yes, there is:
   "An SQL-implementation may define objects that are associated with    INFORMATION_SCHEMA that are not defined in
thisClause. An   SQL-implementation or any future version of ISO/IEC 9075 may also   add columns to tables that are
definedin this Clause."
 
ISO/IEC 9075-11:2003    4.2    Introduction to the Information Schema

If we combine this with the promise that no object defined by the
standard will ever end in an underscore, I can think of no reason not
to add PostgreSQL specific columns as long as they end in an
underscore. The underscore will not only prevent conflicts with future
versions of the standard, it will also serve as a warning that that
column is a PostgreSQL extension.

Jochem

PS I think I spotted an inconsistency in the standard. It says "to
tables that are defined in this Clause", while the Clause only defines
views, not tables.


Re: Adding column comment to information_schema.columns

From
Peter Eisentraut
Date:
Jochem van Dieten wrote:
> PS I think I spotted an inconsistency in the standard. It says "to
> tables that are defined in this Clause", while the Clause only
> defines views, not tables.

Tables are "base tables", views are "derived tables", so this is OK.



Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> There is a huge difference between adhering to a standard and limiting
> yourself to a standard.  The real question is whether PostgreSQL's
> goal is to support SQL standards, or whether PostgreSQL's goal is to
> give PostgreSQL users a useful set of tools.

There are literally _hundreds_ of fields we could add to the 
information_schema.  Either we add them all or we add none of them.

Chris



Re: Adding column comment to information_schema.columns

From
Justin Clift
Date:
Christopher Kings-Lynne wrote:
>> There is a huge difference between adhering to a standard and limiting
>> yourself to a standard.  The real question is whether PostgreSQL's
>> goal is to support SQL standards, or whether PostgreSQL's goal is to
>> give PostgreSQL users a useful set of tools.
> 
> 
> There are literally _hundreds_ of fields we could add to the 
> information_schema.  Either we add them all or we add none of them.

Well, if we add them (and they would be very useful I reckon) should we 
ensure there's an obvious PG naming thing happening?

i.e.  pg_column_comment

or similar?  Maybe not "pg_" but you know what I mean.

:-)

Regards and best wishes,

Justin Clift


> Chris
> 
> 
> 




Re: Adding column comment to information_schema.columns

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I agree.  The stuff is certainly accessible in PG-specific tables, so
> the argument that we are missing functionality doesn't hold any water
> IMHO.  The question is whether we have to keep information_schema
> pristine.  I think that you and Stephan have made enough concrete
> points that the answer to that has to be "stick to the standard".
If there is that much clamor for this, why not make a new schema,
such as "pginformation_schema" People could then tweak the views
to their heart's content, while keeping 100% compliance.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200407012226
-----BEGIN PGP SIGNATURE-----
iD8DBQFA5MfzvJuQZxSWSsgRAmyaAKCjbKre5ZuDpJnVA6rGjnAgNaIwvACgvRJN
VxhiNWiYUCW8AfUBOUd/vEw=
=SUM9
-----END PGP SIGNATURE-----




Re: Adding column comment to information_schema.columns

From
Justin Clift
Date:
Greg Sabino Mullane wrote:
<snip>
> If there is that much clamor for this, why not make a new schema,
> such as "pginformation_schema" People could then tweak the views
> to their heart's content, while keeping 100% compliance.

Doesn't sound very neat.

If we add a pginformation_schema, then it'd probably contain all of the 
existing information_schema... plus more.  Reduplication?

I guess we could just leverage off the existing information_schema views:

i.e.

CREATE VIEW pg_information_schmema.some_view AS SELECT * FROM 
information_schema.some_view (then add extra bits).

But it still doesn't sound very neat.

?

Regards and best wishes,

Justin Clift



Re: Adding column comment to information_schema.columns

From
Hannu Krosing
Date:
On R, 2004-07-02 at 05:07, Justin Clift wrote:
> Christopher Kings-Lynne wrote:
> >> There is a huge difference between adhering to a standard and limiting
> >> yourself to a standard.  The real question is whether PostgreSQL's
> >> goal is to support SQL standards, or whether PostgreSQL's goal is to
> >> give PostgreSQL users a useful set of tools.
> > 
> > 
> > There are literally _hundreds_ of fields we could add to the 
> > information_schema.  Either we add them all or we add none of them.
> 
> Well, if we add them (and they would be very useful I reckon) should we 
> ensure there's an obvious PG naming thing happening?
> 
> i.e.  pg_column_comment
> 
> or similar?  Maybe not "pg_" but you know what I mean.

IIRC we were recently told (in this thread) that the SQL standard tells
to end local customisations with underscore, so it would be
'column_comment_'

---------------
Hannu



Re: Adding column comment to information_schema.columns

From
Robert Treat
Date:
On Fri, 2004-07-02 at 07:57, Hannu Krosing wrote:
> On R, 2004-07-02 at 05:07, Justin Clift wrote:
> > Christopher Kings-Lynne wrote:
> > >> There is a huge difference between adhering to a standard and limiting
> > >> yourself to a standard.  The real question is whether PostgreSQL's
> > >> goal is to support SQL standards, or whether PostgreSQL's goal is to
> > >> give PostgreSQL users a useful set of tools.
> > > 
> > > 
> > > There are literally _hundreds_ of fields we could add to the 
> > > information_schema.  Either we add them all or we add none of them.
> > 
> > Well, if we add them (and they would be very useful I reckon) should we 
> > ensure there's an obvious PG naming thing happening?
> > 
> > i.e.  pg_column_comment
> > 
> > or similar?  Maybe not "pg_" but you know what I mean.
> 
> IIRC we were recently told (in this thread) that the SQL standard tells
> to end local customisations with underscore, so it would be
> 'column_comment_'
> 

Yup... but before we go to far I think anyone who is thinking of adding
a column should see if there is a comparable column in
oracle/db2/$ql$erver.  The point of information_schema (at least one
point of it) is to help application writers to write code that works
across different database systems and I wouldn't be surprised if those
folks had already extended the information_schema in some way.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
>>Well, if we add them (and they would be very useful I reckon) should we 
>>ensure there's an obvious PG naming thing happening?

Why are they useful???? If you want PG specific stuff then use the PG 
specific catalogs!!!

Chris



Re: Adding column comment to information_schema.columns

From
Bruno Wolff III
Date:
On Fri, Jul 02, 2004 at 22:30:05 +0800, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >>Well, if we add them (and they would be very useful I reckon) should we 
> >>ensure there's an obvious PG naming thing happening?
> 
> Why are they useful???? If you want PG specific stuff then use the PG 
> specific catalogs!!!

The information schema could be used to provide a more stable interface.
The pg catalog changes from release to release and it would be nice to
be able to write code which is more future proof.


Re: Adding column comment to information_schema.columns

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
>   Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>> Why are they useful???? If you want PG specific stuff then use the PG 
>> specific catalogs!!!

> The information schema could be used to provide a more stable interface.
> The pg catalog changes from release to release and it would be nice to
> be able to write code which is more future proof.

Isn't this argument self-contradictory?  The reason information_schema
is stable is that it is standard.  If we start hacking it up to add
implementation-specific stuff, then you lose that stability.  You'll
need to consider the PG version when deciding what queries you can
issue, and there will undoubtedly be scenarios where we have to change
or remove columns that we've added to information_schema.  (We do not
whack the system catalogs around without good reason, after all.)
        regards, tom lane


Re: Adding column comment to information_schema.columns

From
Jochem van Dieten
Date:
On Fri, 02 Jul 2004 14:57:18 +0300, Hannu Krosing <hannu@tm.ee> wrote:
> 
> IIRC we were recently told (in this thread) that the SQL standard tells
> to end local customisations with underscore, so it would be
> 'column_comment_'

I didn't write that (or at least, I didn't mean to write that :-). The
SQL standard tells that they will not use trailing underscores,
therefore *my* conclusion is that it is future proof (from a standards
perspective) if all PostgreSQL extensions use a trailing underscore.
Which also gives an indication to the user that it is a PostgreSQL
extension.

"NOTE 77 - It is the intention that no <key word> specified in ISO/IEC
9075 or revisions thereto shall end with an <underscore>."
ISO/IEC 9075-2:2003   5.4   Names and Identifiers

Jochem


Re: Adding column comment to information_schema.columns

From
Hannu Krosing
Date:
On R, 2004-07-02 at 20:04, Tom Lane wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> >   Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >> Why are they useful???? If you want PG specific stuff then use the PG 
> >> specific catalogs!!!
> 
> > The information schema could be used to provide a more stable interface.
> > The pg catalog changes from release to release and it would be nice to
> > be able to write code which is more future proof.
> 
> Isn't this argument self-contradictory?  The reason information_schema
> is stable is that it is standard.  If we start hacking it up to add
> implementation-specific stuff, then you lose that stability.  You'll
> need to consider the PG version when deciding what queries you can
> issue, and there will undoubtedly be scenarios where we have to change
> or remove columns that we've added to information_schema.

Sure. But there are also many that change in pg_* tables but still can
be kept the same in information schema. The column numbering issues that
came up when adding "delete column" are one example.

Of course, just adding "everything" is a bad idea, but surely there are
some things that are not in standard information schema but could be
added as *_ extensions.

I think that extending information schema in a standard-stipulated way
is a better idea than having a separate pg_information_schema for schema
future-proofing use.

-------------------
Hannu



Re: Adding column comment to information_schema.columns

From
Tom Lane
Date:
Jochem van Dieten <jochemd@gmail.com> writes:
> I didn't write that (or at least, I didn't mean to write that :-). The
> SQL standard tells that they will not use trailing underscores,

Um ... actually that's not what it says:

> "NOTE 77 - It is the intention that no <key word> specified in ISO/IEC
> 9075 or revisions thereto shall end with an <underscore>."

This refers to keywords, NOT to column names which are just identifiers.
It may be that the SQL committee does not intend to standardize any
information_schema column names that end with underscore, but this
particular note certainly doesn't promise that.
        regards, tom lane


Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
>>Why are they useful???? If you want PG specific stuff then use the PG 
>>specific catalogs!!!
> 
> The information schema could be used to provide a more stable interface.
> The pg catalog changes from release to release and it would be nice to
> be able to write code which is more future proof.

Really? It will have the same problem!  That being that when we change 
stuff in the catalogs, we will need to change it in the info schema as 
well more than likely...

Chris



Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> I didn't write that (or at least, I didn't mean to write that :-). The
> SQL standard tells that they will not use trailing underscores,
> therefore *my* conclusion is that it is future proof (from a standards
> perspective) if all PostgreSQL extensions use a trailing underscore.
> Which also gives an indication to the user that it is a PostgreSQL
> extension.

All you need to do is add an oid_ column then you can join the 
info_schema tables to the catalogs however you like.

Chris



Re: Adding column comment to information_schema.columns

From
Bruno Wolff III
Date:
On Sat, Jul 03, 2004 at 18:02:01 +0800, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >>Why are they useful???? If you want PG specific stuff then use the PG 
> >>specific catalogs!!!
> >
> >The information schema could be used to provide a more stable interface.
> >The pg catalog changes from release to release and it would be nice to
> >be able to write code which is more future proof.
> 
> Really? It will have the same problem!  That being that when we change 
> stuff in the catalogs, we will need to change it in the info schema as 
> well more than likely...

That isn't my expectation. I would expect that in most cases the pg catalog
would be changed to include more information, not less and that the
old information affected by a change could still be obtained by using
a more complicated view.


Re: Adding column comment to information_schema.columns

From
Justin Clift
Date:
Christopher Kings-Lynne wrote:
>>> Well, if we add them (and they would be very useful I reckon) should 
>>> we ensure there's an obvious PG naming thing happening?
> 
> Why are they useful???? If you want PG specific stuff then use the PG 
> specific catalogs!!!

My take on this is that it's a LOT easier for people who don't know the 
internals of the PG catalogs to be able to query the information schema, 
as in the information schema things are generally explicitly named.

Much easier for non-experts, which most people don't want to have to 
invest the time in becoming.

Regards and best wishes,

Justin Clift

> Chris



Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> My take on this is that it's a LOT easier for people who don't know the 
> internals of the PG catalogs to be able to query the information schema, 
> as in the information schema things are generally explicitly named.
> 
> Much easier for non-experts, which most people don't want to have to 
> invest the time in becoming.

Anyone who's writing queries that are examing the schema of the database 
is by definition not a newbie...

Chris



Re: Adding column comment to information_schema.columns

From
Justin Clift
Date:
Christopher Kings-Lynne wrote:
<snip>
> Anyone who's writing queries that are examing the schema of the database 
> is by definition not a newbie...

By newbie here, I mean someone who's a PG "newbie" but has a reasonable 
understanding of databases (i.e. Oracle, etc) would generally find the 
"information_schema" much easier to locate and use information in 
compared to having to learn the PG internals.

There's a whole lot of difference between the skill level needed to 
query the information_schema and find out things like table and column 
names, vs looking into pg_namespace, pg_class and pg_attribute plus 
understanding the specific info there to work out table and column names.

I reckon that having information "pre-prepared" in views like those in 
information_schema is "of course" going to be easier for people than 
"raw" information our internal catalogs.

Do you get where I'm coming from with this?

Regards and best wishes,

Justin Clift

> Chris
> 
> 
> 




Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> There's a whole lot of difference between the skill level needed to 
> query the information_schema and find out things like table and column 
> names, vs looking into pg_namespace, pg_class and pg_attribute plus 
> understanding the specific info there to work out table and column names.
> 
> I reckon that having information "pre-prepared" in views like those in 
> information_schema is "of course" going to be easier for people than 
> "raw" information our internal catalogs.
> 
> Do you get where I'm coming from with this?

Yes, but I disagree.  Your opinion is as an experienced user anyway, and 
you're just putting words in novice mouths...

We've never had someone complain about querying stuff like that.  For 
example, why do you need the comments on columns?

Chris



Re: Adding column comment to information_schema.columns

From
Justin Clift
Date:
Christopher Kings-Lynne wrote:
>> There's a whole lot of difference between the skill level needed to 
>> query the information_schema and find out things like table and column 
>> names, vs looking into pg_namespace, pg_class and pg_attribute plus 
>> understanding the specific info there to work out table and column names.
>>
>> I reckon that having information "pre-prepared" in views like those in 
>> information_schema is "of course" going to be easier for people than 
>> "raw" information our internal catalogs.
>>
>> Do you get where I'm coming from with this?
> 
> 
> Yes, but I disagree.  Your opinion is as an experienced user anyway, and 
> you're just putting words in novice mouths...

That I directly disagree with.  I'm putting forth the viewpoint of the 
people I work with here, who aren't PG experienced.  They're Oracle 
experienced.

> We've never had someone complain about querying stuff like that.  For 
> example, why do you need the comments on columns?

The "comment on columns" addition to the constraint_column_usage view 
was a suggestion for our particular environment, where it's easier for 
some of the Perl programmers to have one view that shows them all of the 
needed info.

I'm not super caring either if we add this stuff or not to PG, it was 
just a suggestion from the "trying to be helpful POV".

However, saying that people who aren't experienced with PG can easily 
(i.e. time efficiently) figure out how to query table and column names 
from PG by going through the pg_catalog stuff in comparison to things 
like information_schema.* is just not right.

One other benefit of having more stuff in information_schema.* is that 
the stuff there is "easier" to look at and figure out what it is.  With 
the view definitions that are provided to things like psql and pgAdmin 
when people look at an information_schema view, it provides them a way 
of figuring out where in the internal tables stuff is if they want to 
look for it.  i.e. they can find a column in 
information_schema.constraint_column_usage and go "gee where is that in 
the real PostgreSQL tables"?  Then look at the code that generates it 
and so on.

:)

Regards and best wishes,

Justin Clift


> Chris
> 
> 
> 




Re: Adding column comment to information_schema.columns

From
Christopher Kings-Lynne
Date:
> One other benefit of having more stuff in information_schema.* is that 
> the stuff there is "easier" to look at and figure out what it is.  With 
> the view definitions that are provided to things like psql and pgAdmin 
> when people look at an information_schema view, it provides them a way 
> of figuring out where in the internal tables stuff is if they want to 
> look for it.  i.e. they can find a column in 
> information_schema.constraint_column_usage and go "gee where is that in 
> the real PostgreSQL tables"?  Then look at the code that generates it 
> and so on.

OK, so make a pg_columns system view.

Chris



Re: Adding column comment to information_schema.columns

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Do you get where I'm coming from with this?

> Yes, but I disagree.

Same here.  The portable information already is in information_schema,
and I don't really see that it's better to find unportable information
in information_schema views than in other catalogs.  By the time you
are interested in looking at unportable information, you are already
pretty seriously invested in Postgres...
        regards, tom lane


Re: Adding column comment to information_schema.columns

From
Andreas Pflug
Date:
Justin Clift wrote:

> Christopher Kings-Lynne wrote:
> <snip>
>
>> Anyone who's writing queries that are examing the schema of the 
>> database is by definition not a newbie...
>
>
> By newbie here, I mean someone who's a PG "newbie" but has a 
> reasonable understanding of databases (i.e. Oracle, etc) would 
> generally find the "information_schema" much easier to locate and use 
> information in compared to having to learn the PG internals.


Instead of querying views, a newbie should use some of the guis that 
deliver all information for free, in a plainly understandable manner.

Regards,
Andreas