Re: [HACKERS] psql: bogus descriptions displayed by \d+
On fre, 2011-08-12 at 16:14 -0400, Robert Haas wrote: On Fri, Aug 12, 2011 at 4:11 PM, Peter Eisentraut pete...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Sat, Aug 13, 2011 at 1:56 PM, Peter Eisentraut pete...@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 pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Fri, Aug 12, 2011 at 4:11 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
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. ;-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Thu, Aug 4, 2011 at 2:30 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Thu, Aug 4, 2011 at 2:26 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Thu, 2011-08-04 at 14:20 -0500, Kevin Grittner wrote: Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
Robert Haas robertmh...@gmail.com wrote: Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Tue, Jul 26, 2011 at 9:21 PM, Josh Kupershmidt schmi...@gmail.com wrote: On Tue, Jul 26, 2011 at 9:53 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt schmi...@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=parameterrelation_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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On tis, 2011-07-26 at 09:53 -0400, Robert Haas wrote: On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt schmi...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Wed, Jul 27, 2011 at 5:19 PM, Peter Eisentraut pete...@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 schmi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Wed, Jul 27, 2011 at 5:19 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt schmi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Tue, Jul 26, 2011 at 9:53 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jul 25, 2011 at 10:29 PM, Josh Kupershmidt schmi...@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=parameterrelation_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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Fri, Jul 22, 2011 at 12:44 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 21, 2011 at 9:17 PM, Josh Kupershmidt schmi...@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 diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index ab12614..736907e 100644 *** a/doc/src/sgml/ref/comment.sgml --- b/doc/src/sgml/ref/comment.sgml *** COMMENT ON *** 26,32 AGGREGATE replaceable class=PARAMETERagg_name/replaceable (replaceable class=PARAMETERagg_type/replaceable [, ...] ) | CAST (replaceablesource_type/replaceable AS replaceabletarget_type/replaceable) | COLLATION replaceable class=PARAMETERobject_name/replaceable | ! COLUMN replaceable class=PARAMETERtable_name/replaceable.replaceable class=PARAMETERcolumn_name/replaceable | CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable | CONVERSION replaceable class=PARAMETERobject_name/replaceable | DATABASE replaceable class=PARAMETERobject_name/replaceable | --- 26,32 AGGREGATE replaceable class=PARAMETERagg_name/replaceable (replaceable class=PARAMETERagg_type/replaceable [, ...] ) | CAST (replaceablesource_type/replaceable AS replaceabletarget_type/replaceable) | COLLATION replaceable class=PARAMETERobject_name/replaceable | ! COLUMN replaceable class=PARAMETERrelation_name/replaceable.replaceable class=PARAMETERcolumn_name/replaceable | CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable | CONVERSION replaceable class=PARAMETERobject_name/replaceable | DATABASE replaceable class=PARAMETERobject_name/replaceable | *** COMMENT ON *** 97,105 variablelist varlistentry - termreplaceable class=parameterobject_name/replaceable/term - termreplaceable class=parametertable_name.column_name/replaceable/term termreplaceable class=parameteragg_name/replaceable/term termreplaceable class=parameterconstraint_name/replaceable/term termreplaceable class=parameterfunction_name/replaceable/term termreplaceable class=parameteroperator_name/replaceable/term --- 97,104 variablelist varlistentry termreplaceable class=parameteragg_name/replaceable/term + termreplaceable class=parameterobject_name/replaceable/term termreplaceable class=parameterconstraint_name/replaceable/term termreplaceable class=parameterfunction_name/replaceable/term termreplaceable class=parameteroperator_name/replaceable/term *** COMMENT ON *** 143,148 --- 142,158 /para /listitem /varlistentry + + varlistentry + termreplaceablerelation_name.column_name/replaceable/term + listitem + para +For comments on columns, the name of the relation and column. Column +comments may be used with tables, views, composite types, and +foreign tables. + /para + /listitem + /varlistentry varlistentry termreplaceable
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Thu, Jul 21, 2011 at 9:17 PM, Josh Kupershmidt schmi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Sun, Jul 17, 2011 at 10:54 AM, Josh Kupershmidt schmi...@gmail.com wrote: On Sat, Jul 16, 2011 at 12:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: After a bit of review of the archives, the somebody was me: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=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 diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index ab12614..58a2f02 100644 *** a/doc/src/sgml/ref/comment.sgml --- b/doc/src/sgml/ref/comment.sgml *** COMMENT ON *** 26,32 AGGREGATE replaceable class=PARAMETERagg_name/replaceable (replaceable class=PARAMETERagg_type/replaceable [, ...] ) | CAST (replaceablesource_type/replaceable AS replaceabletarget_type/replaceable) | COLLATION replaceable class=PARAMETERobject_name/replaceable | ! COLUMN replaceable class=PARAMETERtable_name/replaceable.replaceable class=PARAMETERcolumn_name/replaceable | CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable | CONVERSION replaceable class=PARAMETERobject_name/replaceable | DATABASE replaceable class=PARAMETERobject_name/replaceable | --- 26,32 AGGREGATE replaceable class=PARAMETERagg_name/replaceable (replaceable class=PARAMETERagg_type/replaceable [, ...] ) | CAST (replaceablesource_type/replaceable AS replaceabletarget_type/replaceable) | COLLATION replaceable class=PARAMETERobject_name/replaceable | ! COLUMN replaceable class=PARAMETERtable_or_view_name/replaceable.replaceable class=PARAMETERcolumn_name/replaceable | CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable | CONVERSION replaceable class=PARAMETERobject_name/replaceable | DATABASE replaceable class=PARAMETERobject_name/replaceable | *** COMMENT ON *** 42,48 OPERATOR FAMILY replaceable class=PARAMETERobject_name/replaceable USING replaceable class=parameterindex_method/replaceable | [ PROCEDURAL ] LANGUAGE replaceable class=PARAMETERobject_name/replaceable | ROLE replaceable class=PARAMETERobject_name/replaceable | ! RULE replaceable class=PARAMETERrule_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable | SCHEMA replaceable class=PARAMETERobject_name/replaceable | SEQUENCE replaceable class=PARAMETERobject_name/replaceable | SERVER replaceable class=PARAMETERobject_name/replaceable | --- 42,48 OPERATOR FAMILY replaceable class=PARAMETERobject_name/replaceable USING replaceable class=parameterindex_method/replaceable | [ PROCEDURAL ] LANGUAGE replaceable class=PARAMETERobject_name/replaceable | ROLE replaceable class=PARAMETERobject_name/replaceable | ! RULE replaceable class=PARAMETERrule_name/replaceable ON replaceable class=PARAMETERtable_or_view_name/replaceable | SCHEMA replaceable class=PARAMETERobject_name/replaceable | SEQUENCE replaceable class=PARAMETERobject_name/replaceable | SERVER replaceable class=PARAMETERobject_name/replaceable | *** COMMENT ON *** 52,58 TEXT SEARCH DICTIONARY replaceable class=PARAMETERobject_name/replaceable | TEXT SEARCH PARSER replaceable
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Sat, Jul 16, 2011 at 12:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: After a bit of review of the archives, the somebody was me: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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'; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
Josh Kupershmidt schmi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
I wrote: Josh Kupershmidt schmi...@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.gita=commitdiffh=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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: bogus descriptions displayed by \d+
On Sat, Jul 16, 2011 at 12:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Josh Kupershmidt schmi...@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.gita=commitdiffh=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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers