Re: 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 <tablename> 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

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 <tablename> 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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> 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

On Wed, Feb 25, 2009 at 07:30:30PM -0500, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> 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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)org> 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

-----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 <tablename> 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 greg(at)turnstep(dot)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-----

On Thu, Feb 26, 2009 at 12:23 PM, Greg Sabino Mullane <greg(at)turnstep(dot)com> wrote:
>> This one is very basic, it just shows the child tables of a specific
>> table when you type \d <tablename> 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

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 <tablename> 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

-----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 greg(at)turnstep(dot)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-----

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

-----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 greg(at)turnstep(dot)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-----

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/

On Sunday 10 May 2009 03:05:48 damien(at)dalibo(dot)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.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On Sunday 10 May 2009 03:05:48 damien(at)dalibo(dot)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

On Tuesday 07 July 2009 19:35:54 Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > On Sunday 10 May 2009 03:05:48 damien(at)dalibo(dot)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.

Peter Eisentraut <peter_e(at)gmx(dot)net> 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