Thread: psql: bogus descriptions displayed by \d+
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';
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
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
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
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
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
Attachment
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
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
Attachment
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
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
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
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.
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
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
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
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.
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. ;-)
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
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
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
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
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.
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
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.
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
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