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