Re: [HACKERS] Have \d show child tables that inherit from the specified parent
Peter Eisentraut writes: > On Tuesday 07 July 2009 19:35:54 Tom Lane wrote: >> I looked at this patch. I'm a bit disturbed by the >> ORDER BY c.oid::pg_catalog.regclass >> business. I doubt that does what the author thinks, and I'm not sure >> what we really want anyway. If we want the child table names sorted >> alphabetically then the regclass value needs to be further cast to text, >> ie >> ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text > Fixed; it was clearly meant to be by name. Testing shows that we still have a problem: the query now fails outright with pre-8.3 servers: regression=# \d+ a ERROR: cannot cast type regclass to text Perhaps it would be sufficient to ORDER BY c.relname. 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] Have \d show child tables that inherit from the specified parent
On Tuesday 07 July 2009 19:35:54 Tom Lane wrote: > Peter Eisentraut writes: > > On Sunday 10 May 2009 03:05:48 dam...@dalibo.info wrote: > >> Here's a second version. Main changes are : > >> > >> * Child tables are sorted by name > >> * \d only shows the number of child tables > >> * \d+ shows the full list > > > > Committed. > > I looked at this patch. I'm a bit disturbed by the > ORDER BY c.oid::pg_catalog.regclass > business. I doubt that does what the author thinks, and I'm not sure > what we really want anyway. If we want the child table names sorted > alphabetically then the regclass value needs to be further cast to text, > ie > ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text Fixed; it was clearly meant to be by name. Creation order might be useful, but we don't really support that anywhere else. -- 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] Have \d show child tables that inherit from the specified parent
Peter Eisentraut writes: > On Sunday 10 May 2009 03:05:48 dam...@dalibo.info wrote: >> Here's a second version. Main changes are : >> >> * Child tables are sorted by name >> * \d only shows the number of child tables >> * \d+ shows the full list > Committed. I looked at this patch. I'm a bit disturbed by the ORDER BY c.oid::pg_catalog.regclass business. I doubt that does what the author thinks, and I'm not sure what we really want anyway. If we want the child table names sorted alphabetically then the regclass value needs to be further cast to text, ie ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text As-is, it's sorting by the OID values, which is almost like sorting the children in creation order, which might possibly be thought preferable ... except that once the OID counter wraps around we won't be able to guarantee that anymore. Comments? 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] Have \d show child tables that inherit from the specified parent
On Sunday 10 May 2009 03:05:48 dam...@dalibo.info wrote: > Here's a second version. Main changes are : > > * Child tables are sorted by name > * \d only shows the number of child tables > * \d+ shows the full list Committed. -- 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] Have \d show child tables that inherit from the specified parent
Greg Sabino Mullane escreveu: > >> * Child tables are sorted by name >> * \d only shows the number of child tables >> * \d+ shows the full list > >> This table has 4 child table(s). Use \d+ to display them. > > Would be nice if you could get rid of that ugly '(s)' part. > What about "Child Tables: 4"? It is short and simple. >> Child tables: daughter, >> daughter2, >> daughter3, >> plop.daughter4 >> Has OIDs: no > > Since the child list can be rather long, it's probably better to put > everything else first, e.g. the "Has OIDs" line. > +1. Don't forget "Options". -- Euler Taveira de Oliveira http://www.timbira.com/ -- 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] Have \d show child tables that inherit from the specified parent
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > * Child tables are sorted by name > * \d only shows the number of child tables > * \d+ shows the full list > This table has 4 child table(s). Use \d+ to display them. Would be nice if you could get rid of that ugly '(s)' part. > Child tables: daughter, > daughter2, > daughter3, > plop.daughter4 > Has OIDs: no Since the child list can be rather long, it's probably better to put everything else first, e.g. the "Has OIDs" line. > For people having hundreds of child tables, the use of \d+ will still be > uncomfortable. But i must admit that i can't see any better way to > implement this feature, except by adding a new syntax ( \dh to show > specific information about inheritance ? ) I think the \d+ is a good compromise. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200905102120 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkoHfZ8ACgkQvJuQZxSWSsjIiACfWHH+YbUPMxanDdUTprbtHz+T FzwAn1GVzEArQLAPSJIAKWnMXGHvpdG1 =+oo1 -END PGP SIGNATURE- -- 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] Have \d show child tables that inherit from the specified parent
Hi, Here's a second version. Main changes are : * Child tables are sorted by name * \d only shows the number of child tables * \d+ shows the full list Which gives : postgres=# \d mother Table "public.mother" Column | Type | Modifiers +-+- id | integer | not null default nextval('mother_id_seq'::regclass) This table has 4 child table(s). Use \d+ to display them. postgres=# \d+ mother Table "public.mother" Column | Type | Modifiers | Storage | Description +-+-+-+- id | integer | not null default nextval('mother_id_seq'::regclass) | plain | Child tables: daughter, daughter2, daughter3, plop.daughter4 Has OIDs: no For people having hundreds of child tables, the use of \d+ will still be uncomfortable. But i must admit that i can't see any better way to implement this feature, except by adding a new syntax ( \dh to show specific information about inheritance ? ) Anyway let me know if you think it is not worth going forward on that feature :-) -- damien clochard http://dalibo.org | http://dalibo.com ? logfile Index: describe.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.212 diff -c -r1.212 describe.c *** describe.c 5 May 2009 02:29:06 - 1.212 --- describe.c 9 May 2009 23:57:36 - *** *** 1812,1817 --- 1812,1856 } PQclear(result); + /* print child tables */ + printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass;", oid); + + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + else + tuples = PQntuples(result); + + if (!verbose) + { + /* print the number of child tables, if any */ + if (tuples > 0) + { + printfPQExpBuffer(&buf, _("This table has %i child table(s). Use \\d+ to display them."),tuples); + printTableAddFooter(&cont, buf.data); + } + } + else + { + /* display the list of child tables*/ + for (i = 0; i < tuples; i++) + { + const char *ct = _("Child tables"); + + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", ct, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", (int) strlen(ct), "", PQgetvalue(result, i, 0)); + if (i < tuples - 1) + appendPQExpBuffer(&buf, ","); + + printTableAddFooter(&cont, buf.data); + } + } + PQclear(result); + + + if (verbose) { const char *s = _("Has OIDs"); -- 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] Have \d show child tables that inherit from the specified parent
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > That being said i recognize that people who have dozens of child tables > might get annoyed by this patch. Dozens is on the low end: there are often a dozen dozen or more. > Here's 3 ideas that could make things better : > > 1- When using \d , only show the numbers of child tables > > 2- Only display the list of child table when \d+ is used. (mode ???) I like these two ideas > 3- Display the child table names in line, instead of one line per table. No, this would be wrong. Please keep them one per line, and sort them by name. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200903230841 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAknHg2YACgkQvJuQZxSWSsj3fQCfY15MYLjGCELNAXhQWR10grJf +c4AoK/3sxtCNe4N+exmYx22nM4Z9RvD =clRO -END PGP SIGNATURE- -- 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] Have \d show child tables that inherit from the specified parent
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 This one is very basic, it just shows the child tables of a specific table when you type \d in psql : I'm not so jazzed about this, as I work on systems that have literally hundreds of child tables. hi everyone. Thanks for your comments. I must admit that i didn't asked myself much questions when i implemented that feature. As i said i picked that item almost randomly in the todo list. I choosed to show child table on \d instead \d+ in order to be consistant with the fact that \d shows the mother of the specified table. That being said i recognize that people who have dozens of child tables might get annoyed by this patch. Here's 3 ideas that could make things better : 1- When using \d , only show the numbers of child tables 2- Only display the list of child table when \d+ is used. (mode ???) 3- Display the child table names in line, instead of one line per table. Here's what it would look like : # \d mother Table "public.mother" Column | Type | Modifiers +-+- id | integer | not null default nextval('mother_id_seq'::regclass) This table has 5 child tables. Use \d+ to display them. #\d+ mother Table "public.mother" Column | Type | Modifiers +-+- id | integer | not null default nextval('mother_id_seq'::regclass) Child tables : daughter1, daughter2, daughter3, daughter4, daughter5 Does it look better ? -- damien clochard dalibo.org | dalibo.com -- 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] Have \d show child tables that inherit from the specified parent
On Thu, Feb 26, 2009 at 12:23 PM, Greg Sabino Mullane wrote: >> This one is very basic, it just shows the child tables of a specific >> table when you type \d in psql : > > I'm not so jazzed about this, as I work on systems that have literally > hundreds of child tables. When I do a \d on the master table, that's > not what I want to see. Frankly, a good naming system goes a long > way towards that, as children usually line up quick neatly in \d > output. Can we make this only showup in \d+ perhaps? Or not do > this at all? Or for \d, make it say "Use \d+ to view all children"? Yeah, there is already an awful lot of output from regular old \d Typically, I get indexes, check constraints, foreign key constraints, and triggers. It's not real easy to read. It would be nice if there were a way to suppress this output or only show the part you're interested in at the moment, but it's hard to think of what syntax might be reasonable. ...Robert -- 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] Have \d show child tables that inherit from the specified parent
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > This one is very basic, it just shows the child tables of a specific > table when you type \d in psql : I'm not so jazzed about this, as I work on systems that have literally hundreds of child tables. When I do a \d on the master table, that's not what I want to see. Frankly, a good naming system goes a long way towards that, as children usually line up quick neatly in \d output. Can we make this only showup in \d+ perhaps? Or not do this at all? Or for \d, make it say "Use \d+ to view all children"? - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200902261222 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkmm0AEACgkQvJuQZxSWSsjKSgCgrWjNIewEWmhZbIn1qO8fUQNf U4sAn2+AYLnCtq02e8s9IVA55EmgRlk3 =Y9Pi -END PGP SIGNATURE- -- 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] Have \d show child tables that inherit from the specified parent
David Fetter writes: > On Wed, Feb 25, 2009 at 07:30:30PM -0500, Tom Lane wrote: >> See the note at the top of that file that all queries are expected >> to work with server versions back to 7.4. > Would it be OK to have a different code path for pre-8.4 databases? I don't really see the point here. describe.c exists to get the job done, not to show off fancy features. BTW, has anyone thought about what's gonna happen if the table has got thousands of inheritance children? 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] Have \d show child tables that inherit from the specified parent
On Wed, Feb 25, 2009 at 07:30:30PM -0500, Tom Lane wrote: > David Fetter writes: > > Should the patch (and the feature) use WITH RECURSIVE in order to > > get the entire tree? > > See the note at the top of that file that all queries are expected > to work with server versions back to 7.4. Would it be OK to have a different code path for pre-8.4 databases? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Have \d show child tables that inherit from the specified parent
David Fetter writes: > Should the patch (and the feature) use WITH RECURSIVE in order to get > the entire tree? See the note at the top of that file that all queries are expected to work with server versions back to 7.4. 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] Have \d show child tables that inherit from the specified parent
On Thu, Feb 26, 2009 at 12:25:08AM +0100, damien clochard wrote: > Hello, > > Last week, i took some time to check if i was still able to write > some basic C code. So i looked into the TODO list and picked some > trivial items. > > This one is very basic, it just shows the child tables of a specific > table when you type \d in psql : > > > # create table mother(id SERIAL); > # create table daughter() inherits(mother); > # create table daughter2() inherits(mother); > # create schema plop; > # create table plop.daughter3() inherits(mother); > # \d mother > Table "public.mother" > Column | Type | Modifiers > +-+- > id | integer | not null default nextval('mother_id_seq'::regclass) > Child tables: daughter, > daughter2, > plop.daughter3 > > > I understand that it may not be the best moment to submit such a > trivial patch, as most of you are working hard on more important > topics. If you think i should submit it later, just let me know. > > BTW, this is the first patch i submit so any comment is more than > welcome. Should the patch (and the feature) use WITH RECURSIVE in order to get the entire tree? Cheers, David (yes, I know it's predictable from me, but WITH RECURSIVE really is a neat new toy ;) -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Have \d show child tables that inherit from the specified parent
Hello, Last week, i took some time to check if i was still able to write some basic C code. So i looked into the TODO list and picked some trivial items. This one is very basic, it just shows the child tables of a specific table when you type \d in psql : # create table mother(id SERIAL); # create table daughter() inherits(mother); # create table daughter2() inherits(mother); # create schema plop; # create table plop.daughter3() inherits(mother); # \d mother Table "public.mother" Column | Type | Modifiers +-+- id | integer | not null default nextval('mother_id_seq'::regclass) Child tables: daughter, daughter2, plop.daughter3 I understand that it may not be the best moment to submit such a trivial patch, as most of you are working hard on more important topics. If you think i should submit it later, just let me know. BTW, this is the first patch i submit so any comment is more than welcome. Regards, -- damien clochard dalibo.com | dalibo.org ? logfile ? psql Index: describe.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.199 diff -c -r1.199 describe.c *** describe.c 11 Feb 2009 19:12:04 - 1.199 --- describe.c 25 Feb 2009 22:53:50 - *** *** 1668,1673 --- 1668,1699 } PQclear(result); + /* print child tables */ + printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY i.inhseqno;", oid); + + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + else + tuples = PQntuples(result); + + for (i = 0; i < tuples; i++) + { + const char *s = _("Child tables"); + + if (i == 0) + printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s), "", PQgetvalue(result, i, 0)); + if (i < tuples - 1) + appendPQExpBuffer(&buf, ","); + + printTableAddFooter(&cont, buf.data); + } + PQclear(result); + + + if (verbose) { const char *s = _("Has OIDs"); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers