Thread: Adding column comment to information_schema.columns
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,
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
> 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
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 > >
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
> 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
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, 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?
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, 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 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.
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 > Chris > > >
-----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 > Chris
> 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 > Chris > > >
> 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 > Chris > > >
> 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