Обсуждение: psql: bogus descriptions displayed by \d+

Поиск
Список
Период
Сортировка

psql: bogus descriptions displayed by \d+

От
Josh Kupershmidt
Дата:
Hi all,

The psql output for \d+ on indexes, sequences, and views is rather
bogus. Examples below from the SQL at bottom.

So, if you look at \d+ newtbl, the right-most column named Description
should display any comments attached to newtbl's columns. You should
see "bcol column comment" as the Description for column bcol. That
works OK.

Now, try this:

test=# \d+ newtbl_idx_bcol           Index "public.newtbl_idx_bcol"Column |  Type   | Definition | Storage |
Description
--------+---------+------------+---------+-------------bcol   | integer | bcol       | plain   |

What's the Description displayed in that table? Well, right now it's
totally broken (not displaying anything). I'm not sure if we should
try to display the comment attached to column bcol in this case: if
so, what would we do for e.g. functional indexes?

A similar situation exists for sequences and views displayed by \d+.
I'd be OK with just ripping out "Description" entirely in these cases;
if you want to see the comment attached to the index or sequence
itself, you can use \di+ or \ds+. Although now might also be a good
time to think about properly displaying sequence or index comments via
\d+, and how they should be displayed.

Thoughts?

Josh

-- Example SQL creating a few objects with comments. --
CREATE TABLE newtbl (acol  serial PRIMARY KEY,bcol int NOT NULL,ccol text DEFAULT NULL,dcol text NOT NULL);

COMMENT ON TABLE newtbl IS 'newtbl table comment';
COMMENT ON COLUMN newtbl.bcol IS 'bcol column comment';
COMMENT ON SEQUENCE newtbl_acol_seq IS 'sequence comment';

CREATE INDEX newtbl_idx_bcol ON newtbl (bcol);
COMMENT ON INDEX newtbl_idx_bcol IS 'single-column index on newtbl';

CREATE VIEW myview AS SELECT * FROM newtbl;
COMMENT ON VIEW myview IS 'view comment';


Re: psql: bogus descriptions displayed by \d+

От
Tom Lane
Дата:
Josh Kupershmidt <schmiddy@gmail.com> writes:
> So, if you look at \d+ newtbl, the right-most column named Description
> should display any comments attached to newtbl's columns. You should
> see "bcol column comment" as the Description for column bcol. That
> works OK.

Right.

> Now, try this:

> test=# \d+ newtbl_idx_bcol
>             Index "public.newtbl_idx_bcol"
>  Column |  Type   | Definition | Storage | Description
> --------+---------+------------+---------+-------------
>  bcol   | integer | bcol       | plain   |

> What's the Description displayed in that table?

What it ought to be is the comment (if any) attached to the index's
column.  Up through 8.4 this worked as expected, but in 9.0 and up
somebody seems to have disallowed comments on index columns.  Not
sure how carefully that was thought through.

> A similar situation exists for sequences and views displayed by \d+.

Again, the ability to stick comments on columns of those relkinds
seems to have been shut off as of 9.0.  In 8.4 all of these description
columns are functional.
        regards, tom lane


Re: psql: bogus descriptions displayed by \d+

От
Tom Lane
Дата:
I wrote:
> Josh Kupershmidt <schmiddy@gmail.com> writes:
>> What's the Description displayed in that table?

> What it ought to be is the comment (if any) attached to the index's
> column.  Up through 8.4 this worked as expected, but in 9.0 and up
> somebody seems to have disallowed comments on index columns.  Not
> sure how carefully that was thought through.

After a bit of review of the archives, the somebody was me:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b7d67954456f15762c04e5269b64adc88dcd0860

and this thread was the discussion about it:
http://archives.postgresql.org/pgsql-hackers/2009-12/msg01982.php

It looks like we thought about pg_dump, but did not think about psql.

I think it might be reasonable to remove the Description column from
\d+ output for indexes and sequences, on the grounds that (1) it's
useless against 9.x servers, and (2) for those relkinds we add other
columns and so the horizontal space is precious.

We could also consider showing Description only when talking to a
pre-9.0 server; but that's going to render the code even more
spaghetti-ish, and the value seems pretty limited.
        regards, tom lane


Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Sat, Jul 16, 2011 at 12:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Josh Kupershmidt <schmiddy@gmail.com> writes:
>>> What's the Description displayed in that table?
>
>> What it ought to be is the comment (if any) attached to the index's
>> column.  Up through 8.4 this worked as expected, but in 9.0 and up
>> somebody seems to have disallowed comments on index columns.  Not
>> sure how carefully that was thought through.
>
> After a bit of review of the archives, the somebody was me:
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b7d67954456f15762c04e5269b64adc88dcd0860
>
> and this thread was the discussion about it:
> http://archives.postgresql.org/pgsql-hackers/2009-12/msg01982.php
>
> It looks like we thought about pg_dump, but did not think about psql.
>
> I think it might be reasonable to remove the Description column from
> \d+ output for indexes and sequences, on the grounds that (1) it's
> useless against 9.x servers, and (2) for those relkinds we add other
> columns and so the horizontal space is precious.

Yeah, I think that's very reasonable.  We're talking about changing
this in 9.2, which will be the third release since that functionality
was deprecated.  Considering that the functionality is so minor that
there may be no one using it anyway, that seems more than generous.

> We could also consider showing Description only when talking to a
> pre-9.0 server; but that's going to render the code even more
> spaghetti-ish, and the value seems pretty limited.

I don't think we need to do that.  Backward compatibility is good, but
insisting that a 9.2 psql has to produce exactly the same output on an
8.3 server than an 8.3 psql would have done seems like it would be
taking things too far.  We should try to make it work and be useful,
but we shouldn't slavishly replicate obsolete functionality of
doubtful utility.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Josh Kupershmidt
Дата:
On Sat, Jul 16, 2011 at 12:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> After a bit of review of the archives, the somebody was me:
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b7d67954456f15762c04e5269b64adc88dcd0860
>
> and this thread was the discussion about it:
> http://archives.postgresql.org/pgsql-hackers/2009-12/msg01982.php
>
> It looks like we thought about pg_dump, but did not think about psql.

Ah, interesting. I didn't even know this functionality existed. And I
think there is some documentation lacking; in the 8.4 doc page:
http://www.postgresql.org/docs/8.4/static/sql-comment.html

I don't see any mention of comments on an index's columns. And the
docs also neglect to mention comments on a view's columns as well,
which is why I thought \d+ view_name was producing bogus output as
well (it's really looking for those column comments).

> I think it might be reasonable to remove the Description column from
> \d+ output for indexes and sequences, on the grounds that (1) it's
> useless against 9.x servers, and (2) for those relkinds we add other
> columns and so the horizontal space is precious.

AFAICT the extra Description column for \d+ sequence_name is bogus in
both 8.4 and 9.0, so there should be no objections to ripping that
out.

> We could also consider showing Description only when talking to a
> pre-9.0 server; but that's going to render the code even more
> spaghetti-ish, and the value seems pretty limited.

And as for \d+ index_name, I agree with Robert's sentiments here,
doesn't seem worth the bother.

Josh


Re: psql: bogus descriptions displayed by \d+

От
Josh Kupershmidt
Дата:
On Sun, Jul 17, 2011 at 10:54 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> On Sat, Jul 16, 2011 at 12:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> After a bit of review of the archives, the somebody was me:
>> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b7d67954456f15762c04e5269b64adc88dcd0860
>>
>> and this thread was the discussion about it:
>> http://archives.postgresql.org/pgsql-hackers/2009-12/msg01982.php
>>
>> It looks like we thought about pg_dump, but did not think about psql.
>
> Ah, interesting. I didn't even know this functionality existed. And I
> think there is some documentation lacking; in the 8.4 doc page:

Here's a small patch against branch 8.4 to mention support for COMMENT
ON index_name.column_name.

Also, a patch against master to:
 * get rid of the bogus "Description" outputs for \d+ sequence_name
and \d+ index_name
 * clarify in the COMMENT ON doc page that a table _or view_ name may
be used for comments on columns, rules, and triggers. If we allowed
constraints on views, we could have just put in a note explaining that
"table_name.column_name" applies to tables and views, but constraints
are the odd man out.
 * slightly reordered the listing of the first bunch of Parameters on
that page so that agg_name comes first, as it does in the Synopsis
section

I noticed that the synopsis for CREATE RULE:
  http://www.postgresql.org/docs/9.1/static/sql-createrule.html

uses the term "table", which could be a similar omission. However, on
that page the first sentence of the description specifies "table or
view" so it might be fine as-is.

And while I'm messing with this, some further nitpicks about psql not
addressed by these patches:
 * The "Storage" column for \d+ sequence_name is correct, I suppose,
but repetitive
 * The "Type" column for \dv+ view_name, \di+ index_name, \ds+
sequence_name , etc. seems borderline useless.. shouldn't you know
what type you're looking at based on the backslash command you're
using? Plus the table heading could be more specific than "List of
relations", e.g. "List of views".

Josh

Вложения

Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Thu, Jul 21, 2011 at 9:17 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> Here's a small patch against branch 8.4 to mention support for COMMENT
> ON index_name.column_name.

I am not in favor of this - because we'd also need to mention every
other relkind that can support comments.  I think if we want to do
something here we should change it to say relation_name, and then
clarify what that means further down.  Similarly with the patch for
master.

Also, if we're going to make a change here, we probably should make
sure it matches the actual behavior.  In master, that's to allow
comments on columns of tables, views, composite types, and foreign
tables.

> Also, a patch against master to:
>  * get rid of the bogus "Description" outputs for \d+ sequence_name
> and \d+ index_name

This part looks OK, but instead of doing a negative test (not-index,
not-sequence) let's have it do a positive test, for the same types
comment.c allows.

> And while I'm messing with this, some further nitpicks about psql not
> addressed by these patches:
>  * The "Storage" column for \d+ sequence_name is correct, I suppose,
> but repetitive

I'm OK with removing that.

>  * The "Type" column for \dv+ view_name, \di+ index_name, \ds+
> sequence_name , etc. seems borderline useless.. shouldn't you know
> what type you're looking at based on the backslash command you're
> using?

Not really.  You can do something like this, for example:

\dti+

...to show both indexes and tables.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Josh Kupershmidt
Дата:
On Fri, Jul 22, 2011 at 12:44 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Jul 21, 2011 at 9:17 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
>> Here's a small patch against branch 8.4 to mention support for COMMENT
>> ON index_name.column_name.
>
> I am not in favor of this - because we'd also need to mention every
> other relkind that can support comments.  I think if we want to do
> something here we should change it to say relation_name, and then
> clarify what that means further down.  Similarly with the patch for
> master.
>
> Also, if we're going to make a change here, we probably should make
> sure it matches the actual behavior.  In master, that's to allow
> comments on columns of tables, views, composite types, and foreign
> tables.

That seems like a good way to document this; patch for master updated.
I avoided mucking with the documentation for COMMENT ON RULE and
COMMENT ON TRIGGER this time; they both say "table" when they really
mean "table or view", but maybe trying to differentiate between
"table", "table_or_view", and "relation" will make things overly
complicated.

>> Also, a patch against master to:
>>  * get rid of the bogus "Description" outputs for \d+ sequence_name
>> and \d+ index_name
>
> This part looks OK, but instead of doing a negative test (not-index,
> not-sequence) let's have it do a positive test, for the same types
> comment.c allows.

Right, fixed.

>> And while I'm messing with this, some further nitpicks about psql not
>> addressed by these patches:
>>  * The "Storage" column for \d+ sequence_name is correct, I suppose,
>> but repetitive
>
> I'm OK with removing that.

Hrm, would it be better to keep that  Storage bit around in some
non-repetitive form, maybe on its own line below the table output?

>>  * The "Type" column for \dv+ view_name, \di+ index_name, \ds+
>> sequence_name , etc. seems borderline useless.. shouldn't you know
>> what type you're looking at based on the backslash command you're
>> using?
>
> Not really.  You can do something like this, for example:
>
> \dti+
>
> ...to show both indexes and tables.

I see. Didn't know about that trick.

Josh

Вложения

Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> That seems like a good way to document this; patch for master updated.
> I avoided mucking with the documentation for COMMENT ON RULE and
> COMMENT ON TRIGGER this time; they both say "table" when they really
> mean "table or view", but maybe trying to differentiate between
> "table", "table_or_view", and "relation" will make things overly
> complicated.

I think this is basically the right approach but I found what you did
here a bit wordy, so I simplified it, committed it, and back-patched
to 9.0 with suitable adjustment.  Hopefully I didn't simplify it into
a form you don't like.

>>> Also, a patch against master to:
>>>  * get rid of the bogus "Description" outputs for \d+ sequence_name
>>> and \d+ index_name
>>
>> This part looks OK, but instead of doing a negative test (not-index,
>> not-sequence) let's have it do a positive test, for the same types
>> comment.c allows.
>
> Right, fixed.

Committed this part to head with minor tweaks.

>>> And while I'm messing with this, some further nitpicks about psql not
>>> addressed by these patches:
>>>  * The "Storage" column for \d+ sequence_name is correct, I suppose,
>>> but repetitive
>>
>> I'm OK with removing that.
>
> Hrm, would it be better to keep that  Storage bit around in some
> non-repetitive form, maybe on its own line below the table output?

Well, I don't really see that it has any value.  I'd probably just
leave it the way it is, but if we're going to change something, I
would favor removing it over relocating it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Josh Kupershmidt
Дата:
On Tue, Jul 26, 2011 at 9:53 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> I think this is basically the right approach but I found what you did
> here a bit wordy, so I simplified it, committed it, and back-patched
> to 9.0 with suitable adjustment.  Hopefully I didn't simplify it into
> a form you don't like.

That looks fine. Minor grammar quibble about:

+      When commenting on a column,
+      <replaceable class="parameter">relation_name</replaceable> must refer
+      to a table, view, composite types, or foreign table.

"types" should probably be the singular "type".

>>>>  * get rid of the bogus "Description" outputs for \d+ sequence_name
>>>> and \d+ index_name

> Committed this part to head with minor tweaks.

Thanks for the commit.

>>>>  * The "Storage" column for \d+ sequence_name is correct, I suppose,
>>>> but repetitive
>>>
>>> I'm OK with removing that.
>>
>> Hrm, would it be better to keep that  Storage bit around in some
>> non-repetitive form, maybe on its own line below the table output?
>
> Well, I don't really see that it has any value.  I'd probably just
> leave it the way it is, but if we're going to change something, I
> would favor removing it over relocating it.

I notice the "Storage" information is also repeated for multi-column
indexes. I don't mind leaving this wart as-is for now, since
single-column indexes are probably the norm, and we would presumably
want to fix both types in one go.

Josh


Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Tue, Jul 26, 2011 at 9:21 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> On Tue, Jul 26, 2011 at 9:53 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
>> I think this is basically the right approach but I found what you did
>> here a bit wordy, so I simplified it, committed it, and back-patched
>> to 9.0 with suitable adjustment.  Hopefully I didn't simplify it into
>> a form you don't like.
>
> That looks fine. Minor grammar quibble about:
>
> +      When commenting on a column,
> +      <replaceable class="parameter">relation_name</replaceable> must refer
> +      to a table, view, composite types, or foreign table.
>
> "types" should probably be the singular "type".

Woops.  That was dumb.  Fixed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Peter Eisentraut
Дата:
On tis, 2011-07-26 at 09:53 -0400, Robert Haas wrote:
> On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt
> <schmiddy@gmail.com> wrote:
> > That seems like a good way to document this; patch for master
> updated.
> > I avoided mucking with the documentation for COMMENT ON RULE and
> > COMMENT ON TRIGGER this time; they both say "table" when they really
> > mean "table or view", but maybe trying to differentiate between
> > "table", "table_or_view", and "relation" will make things overly
> > complicated.
> 
> I think this is basically the right approach but I found what you did
> here a bit wordy, so I simplified it, committed it, and back-patched
> to 9.0 with suitable adjustment.  Hopefully I didn't simplify it into
> a form you don't like.

I would like to argue for reverting this.  If you want to word-smith
details like this, "relation" doesn't carry any additional meaning.  PG
hackers know that internally, a "relation" is a table, view, index,
sequence, etc., but for the user, it doesn't mean anything.

Note that we don't use relation_name anywhere else in SQL command
synopses.  So far, no one has complained that we don't mention that
views are allowed in the SELECT command or the GRANT command.

I think table_name is fine, and if you are very worried, add below that
a table_name also includes views (or whatever).

As a side note, backpatching this creates additional translation work in
backbranches.  So please keep it to a minimum if it's not outright
wrong.




Re: psql: bogus descriptions displayed by \d+

От
Josh Kupershmidt
Дата:
On Wed, Jul 27, 2011 at 5:19 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On tis, 2011-07-26 at 09:53 -0400, Robert Haas wrote:
>> On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt
>> <schmiddy@gmail.com> wrote:
>> > That seems like a good way to document this; patch for master
>> updated.
>> > I avoided mucking with the documentation for COMMENT ON RULE and
>> > COMMENT ON TRIGGER this time; they both say "table" when they really
>> > mean "table or view", but maybe trying to differentiate between
>> > "table", "table_or_view", and "relation" will make things overly
>> > complicated.
>>
>> I think this is basically the right approach but I found what you did
>> here a bit wordy, so I simplified it, committed it, and back-patched
>> to 9.0 with suitable adjustment.  Hopefully I didn't simplify it into
>> a form you don't like.
>
> I would like to argue for reverting this.  If you want to word-smith
> details like this, "relation" doesn't carry any additional meaning.  PG
> hackers know that internally, a "relation" is a table, view, index,
> sequence, etc., but for the user, it doesn't mean anything.

The original page used "table_name" in the synopsis in three places:
COMMENT ON {COLUMN, CONSTRAINT, and RULE}. If you're suggesting that
it's intuitively obvious what's meant by "table" in each of those
three cases, I respectfully disagree: I only think I know now because
I bothered to test all of these recently, and read a bit of comment.c.
(Hint: "table" means different things in all three cases).

I'll also note that you included "index" in your list of what a
"relation" is, and omitted "composite type" -- this is exactly the
confusion I was trying to avoid. COMMENT ON COLUMN no longer supports
indexes, and does support composite types. Plus, I don't think we
should be designing docs so that only "PG hackers" know what's really
meant. That hasn't seemed to be the modus operandi of maintaining the
rest of the docs.

> Note that we don't use relation_name anywhere else in SQL command
> synopses.  So far, no one has complained that we don't mention that
> views are allowed in the SELECT command or the GRANT command.

I actually complained upthread about CREATE RULE using the term
"table" in its synopsis, when it really means "table or view", but I
thought that was OK because there was an immediate clarification right
below the synopsis.

> I think table_name is fine, and if you are very worried, add below that
> a table_name also includes views (or whatever).

It includes tables, views, composite types, and foreign tables. Is
"table" really an appropriate description for all those objects?

> As a side note, backpatching this creates additional translation work in
> backbranches.  So please keep it to a minimum if it's not outright
> wrong.

That's a legitimate concern; I don't have a strong opinion about
whether stuff like this ought to be backpatched.

Josh


Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Wed, Jul 27, 2011 at 5:19 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> I would like to argue for reverting this.  If you want to word-smith
> details like this, "relation" doesn't carry any additional meaning.  PG
> hackers know that internally, a "relation" is a table, view, index,
> sequence, etc., but for the user, it doesn't mean anything.

Well, I don't think we're going to do very well trying to get by
without a generic term of some sort.  Calling it a table is more
confusing, because the user might easily be forgiven for thinking that
he knows what the word "table" means and reading no further.  If you
say relation, then either (a) the user knows what that means, or (b)
he'll read the text and find out.  I am not very excited about the
idea of documenting "table_name" as "either a table name, or the name
of some kind of object that isn't a table"; I think that's just weird.

Also, while it may be true that we haven't used the term specifically
in SQL sypnoses, it's been extensively used in other parts of the
documentation, in the names of system functions such as
pg_relation_size(), and in user-visible error messages (cd
src/backend/po; git grep relation), so I think you may be trying to
close the barn door after the horse has got out.

> Note that we don't use relation_name anywhere else in SQL command
> synopses.  So far, no one has complained that we don't mention that
> views are allowed in the SELECT command or the GRANT command.

Well, for the record, I have previously been *extremely* confused by
both that documentation and the actual syntax.

> I think table_name is fine, and if you are very worried, add below that
> a table_name also includes views (or whatever).
>
> As a side note, backpatching this creates additional translation work in
> backbranches.  So please keep it to a minimum if it's not outright
> wrong.

I was on the fence about whether this was important enough to
back-patch, and I'll add translation effort to my list of things to
worry about in future cases.  We do pretty commonly back-patch
documentation corrections to the then-current major release, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
"Kevin Grittner"
Дата:
Robert Haas <robertmhaas@gmail.com> wrote:
> Peter Eisentraut <peter_e@gmx.net> wrote:
>> I would like to argue for reverting this.  If you want to
>> word-smith details like this, "relation" doesn't carry any
>> additional meaning.  PG hackers know that internally, a
>> "relation" is a table, view, index, sequence, etc., but for the
>> user, it doesn't mean anything.
> 
> Well, I don't think we're going to do very well trying to get by
> without a generic term of some sort.  Calling it a table is more
> confusing, because the user might easily be forgiven for thinking
> that he knows what the word "table" means and reading no further. 
> If you say relation, then either (a) the user knows what that
> means, or (b) he'll read the text and find out.  I am not very
> excited about the idea of documenting "table_name" as "either a
> table name, or the name of some kind of object that isn't a
> table"; I think that's just weird.
+1 on that whole paragraph.
"relation" has been a "term of art" since 1969.  A table is a type
of relation variable.  I don't think it makes sense to invent new
terminology, although there's nothing wrong with the docs explaining
terms which might not be familiar to all readers.  Of course, we
don't want to come off as overly pedantic with our use of
terminology, but this one is pretty basic and commonly used.
-Kevin


Re: psql: bogus descriptions displayed by \d+

От
Peter Eisentraut
Дата:
On ons, 2011-07-27 at 17:57 -0400, Josh Kupershmidt wrote:
> > I think table_name is fine, and if you are very worried, add below
> that
> > a table_name also includes views (or whatever).
> 
> It includes tables, views, composite types, and foreign tables. Is
> "table" really an appropriate description for all those objects?

Well, the facts are:  According to the SQL standard, "table" includes
views and foreign tables.  According to scientific-ish database
literature, a table is a relation and vice versa.

So to someone new who doesn't know much about the PostgreSQL jargon,
neither "table" nor "relation" are very precise at all.

But I would suggest that there is more support outside of PostgreSQL
jargon for finding that replacing "table" by "relation" does not
increase precision.

And indeed, even if you know the PostgreSQL jargon, "relation" means
anything stored in pg_class.  And in almost all cases, a given command
does not successfully operate and any kind of pg_class object.  So using
"relation" here creates some kind of illusion that will eventually fail,
forcing the user to manually figure out what actually works.

So the bottom line is, I would avoid the term "relation" and look for
other ways to add clarity and precision to the documentation.



Re: psql: bogus descriptions displayed by \d+

От
Peter Eisentraut
Дата:
On ons, 2011-07-27 at 18:08 -0400, Robert Haas wrote:
> Also, while it may be true that we haven't used the term specifically
> in SQL sypnoses, it's been extensively used in other parts of the
> documentation, in the names of system functions such as
> pg_relation_size(),

Well, that thing is just the pinnacle of silliness, because we have
pg_relation_size() and pg_table_size(), which have confusingly different
behaviors.

>  and in user-visible error messages (cd
> src/backend/po; git grep relation), so I think you may be trying to
> close the barn door after the horse has got out. 

No, I'm trying to catch the horse. ;-)



Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Thu, Aug 4, 2011 at 2:30 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On ons, 2011-07-27 at 18:08 -0400, Robert Haas wrote:
>> Also, while it may be true that we haven't used the term specifically
>> in SQL sypnoses, it's been extensively used in other parts of the
>> documentation, in the names of system functions such as
>> pg_relation_size(),
>
> Well, that thing is just the pinnacle of silliness, because we have
> pg_relation_size() and pg_table_size(), which have confusingly different
> behaviors.

Yeah, I just got flummoxed by that yesterday.  Still, the name's out there...

>>  and in user-visible error messages (cd
>> src/backend/po; git grep relation), so I think you may be trying to
>> close the barn door after the horse has got out.
>
> No, I'm trying to catch the horse. ;-)

Fair enough, but I think you're not running fast enough (yet).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Thu, Aug 4, 2011 at 2:26 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On ons, 2011-07-27 at 17:57 -0400, Josh Kupershmidt wrote:
>> > I think table_name is fine, and if you are very worried, add below
>> that
>> > a table_name also includes views (or whatever).
>>
>> It includes tables, views, composite types, and foreign tables. Is
>> "table" really an appropriate description for all those objects?
>
> Well, the facts are:  According to the SQL standard, "table" includes
> views and foreign tables.  According to scientific-ish database
> literature, a table is a relation and vice versa.

So what are you supposed to call it if you mean, specifically, a table?

> So to someone new who doesn't know much about the PostgreSQL jargon,
> neither "table" nor "relation" are very precise at all.

That can be fixed by defining them better, of course...

> And indeed, even if you know the PostgreSQL jargon, "relation" means
> anything stored in pg_class.  And in almost all cases, a given command
> does not successfully operate and any kind of pg_class object.  So using
> "relation" here creates some kind of illusion that will eventually fail,
> forcing the user to manually figure out what actually works.

This argument doesn't impress me much, because it would be true of any
word we used here.  If we start using "table" to mean "a table, view,
or foreign table", then we're going to have to clarify that CLUSTER
only runs on tables that are actually, uh, tables.  And what about the
error messages that say "x is not a table or view"?

And, moreover, at least in English, it's common to make a statement
about a broader class of objects that does not necessarily apply to
every type of object in the class.  When I tell my wife "your cooking
is delicious", my statement is not intended to include her
tomato-and-vodka sauce, which IMHO is really terrible.  She doesn't
react with confusion and say "but wait, how can you say you like my
cooking when I know that you don't like my tomato-and-vodka sauce?";
rather, she understands that I'm talking about some probably fairly
broad subset of her cooking and that if she wants to know what I think
of a specific dish, she will need to inquire specifically about that
dish.  Similarly, I believe users will understand that when they see
relation_name, they might need to check the detailed description to
know which relation types are included.

I'm not averse to using some better terminology; I agree that relation
is kind of corny.  But if we're going to make an effort to be
consistent here, we need to come up with something that's actually
better, and then hopefully implement it fairly broadly.  We've fallen
into saying "relation" mostly for lack of a better term, but we can't
start getting rid of it until we have a replacement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
"Kevin Grittner"
Дата:
Peter Eisentraut <peter_e@gmx.net> wrote:
> According to scientific-ish database literature, a table is a
> relation and vice versa.
I've generally understood the terms more like what is described near
the top of this page:
http://en.wikipedia.org/wiki/Relation_%28database%29
"In SQL, [...] a relation variable is called a table."
I'll admit that how these terms are treated depends very much on the
source, and we should define our terms to avoid confusion.  But
defining a relation as set of records, and a table as a variable
which holds a maintainable "concrete" relation (or something more or
less to that effect) makes some sense to me.
-Kevin


Re: psql: bogus descriptions displayed by \d+

От
Jeff Davis
Дата:
On Thu, 2011-08-04 at 14:20 -0500, Kevin Grittner wrote:
> Peter Eisentraut <peter_e@gmx.net> wrote:
>  
> > According to scientific-ish database literature, a table is a
> > relation and vice versa.
>  
> I've generally understood the terms more like what is described near
> the top of this page:
>  
> http://en.wikipedia.org/wiki/Relation_%28database%29
>  
> "In SQL, [...] a relation variable is called a table."

The SQL spec also uses "table" to refer to a *value*. So we certainly
can't turn that around and say "a table in SQL is a relation variable".

It's all a bit loose anyway, because SQL tables aren't really relations
or relation variables (for instance, they can contain duplicates).

Regards,Jeff Davis



Re: psql: bogus descriptions displayed by \d+

От
Peter Eisentraut
Дата:
On tor, 2011-08-04 at 14:59 -0400, Robert Haas wrote:
> > Well, the facts are:  According to the SQL standard, "table"
> includes
> > views and foreign tables.  According to scientific-ish database
> > literature, a table is a relation and vice versa.
> 
> So what are you supposed to call it if you mean, specifically, a
> table?

"A table is either a base table, a derived table, a transient table, or
a viewed table." (SQL/MED adds "foreign table".)

Just FYI.




Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Fri, Aug 12, 2011 at 4:11 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On tor, 2011-08-04 at 14:59 -0400, Robert Haas wrote:
>> > Well, the facts are:  According to the SQL standard, "table"
>> includes
>> > views and foreign tables.  According to scientific-ish database
>> > literature, a table is a relation and vice versa.
>>
>> So what are you supposed to call it if you mean, specifically, a
>> table?
>
> "A table is either a base table, a derived table, a transient table, or
> a viewed table." (SQL/MED adds "foreign table".)
>
> Just FYI.

Base table seems clear enough, and a transient table sounds like a
temporary table, but what is a derived table?  Is a viewed table a
view?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Peter Eisentraut
Дата:
On fre, 2011-08-12 at 16:14 -0400, Robert Haas wrote:
> On Fri, Aug 12, 2011 at 4:11 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> > "A table is either a base table, a derived table, a transient table, or
> > a viewed table." (SQL/MED adds "foreign table".)
> >
> > Just FYI.
> 
> Base table seems clear enough, and a transient table sounds like a
> temporary table, but what is a derived table?  Is a viewed table a
> view?

A base table is either a permanent base table or (one of various kinds
of) a temporary base table.  A derived table is the result of a table
expression, so this is more of a notional syntactic term.  A transient
table is, roughly speaking, OLD or NEW in a trigger.  A viewed table is
a view.




Re: psql: bogus descriptions displayed by \d+

От
Robert Haas
Дата:
On Sat, Aug 13, 2011 at 1:56 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On fre, 2011-08-12 at 16:14 -0400, Robert Haas wrote:
>> On Fri, Aug 12, 2011 at 4:11 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> > "A table is either a base table, a derived table, a transient table, or
>> > a viewed table." (SQL/MED adds "foreign table".)
>> >
>> > Just FYI.
>>
>> Base table seems clear enough, and a transient table sounds like a
>> temporary table, but what is a derived table?  Is a viewed table a
>> view?
>
> A base table is either a permanent base table or (one of various kinds
> of) a temporary base table.  A derived table is the result of a table
> expression, so this is more of a notional syntactic term.  A transient
> table is, roughly speaking, OLD or NEW in a trigger.  A viewed table is
> a view.

Ah.

Well, I guess I'm somewhat open to the idea of making all the places
where we mean, specifically, a table say "base table".  Then we could
substitute the term "table" where we now say "relation".

On the other hand, I am also not entirely sure such a change in
terminology would be a net improvement in clarity, even though it does
seem better in some cases.  For example, the CREATE TABLE command does
not create a viewed table; nor is there any CREATE VIEWED TABLE
command.  On the flip side, for something like CLUSTER, "ERROR: %s is
not a base table" does seem like it could be more clear than just
"ERROR: %s is not a table".

So I'm not sure what to do.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql: bogus descriptions displayed by \d+

От
Tom Lane
Дата:
Robert Haas <robertmhaas@gmail.com> writes:
> On the other hand, I am also not entirely sure such a change in
> terminology would be a net improvement in clarity, even though it does
> seem better in some cases.  For example, the CREATE TABLE command does
> not create a viewed table; nor is there any CREATE VIEWED TABLE
> command.  On the flip side, for something like CLUSTER, "ERROR: %s is
> not a base table" does seem like it could be more clear than just
> "ERROR: %s is not a table".

> So I'm not sure what to do.

Yeah.  Even if the standard is consistent about using "base table"
versus just "table" when they mean a plain table, I do not believe that
that distinction is commonly understood among users.  I think people
would tend to think that "base table" means some subset of plain tables,
and would get confused.  One fairly likely interpretation would be that
"base table" means the parent table of an inheritance tree, for
instance.  If we have to stop and specify what we mean by "base table"
every other time we use the phrase, we have achieved nothing except
pedantry.

On the whole I prefer "table" for plain tables and "relation" for
everything table-ish.  We can quibble about whether indexes, say, are
relations within that meaning ... but the PG code and docs have long
used "relation" in the more general meaning, and I doubt that we'll get
far if we try to redefine its meaning now.
        regards, tom lane