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?

regards, tom lane

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

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

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

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

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

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

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

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).

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

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?

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

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 this Clause. An
SQL-implementation or any future version of ISO/IEC 9075 may also
add columns to tables that are defined in 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.

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.

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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