Idle idea for a feature

psql's \d command tells you about outgoing foreign key constraints
(ie, ones referencing another table from this one). It doesn't tell
you about incoming ones (ie, ones where another table references this
one). ISTM it'd be a good idea if it did, as "are there any incoming
foreign keys" seems to be a question we constantly ask when solving
update-performance problems, and there isn't any easy way to check for
such. I'm not real sure what the printout should look like, though.

regards, tom lane

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> one). ISTM it'd be a good idea if it did, as "are there any incoming
> foreign keys" seems to be a question we constantly ask when solving
> update-performance problems, and there isn't any easy way to check for
> such. I'm not real sure what the printout should look like, though.

Agreed. Suggestion:

networx=> \d wdm_networx.loc_base_clin
Table "wdm_networx.loc_base_clin"
Column | Type | Modifiers
-------------+---------+-----------
btable_id | integer | not null
loc_base_id | integer | not null
clin | integer | not null
Indexes:
"loc_base_clin_pkey" PRIMARY KEY, btree (btable_id, loc_base_id, clin)
"btable_id_clin_unique" UNIQUE, btree (btable_id, clin)
Foreign-key constraints:
"loc_base_clin_btable_id_fkey" FOREIGN KEY (btable_id) REFERENCES wdm_networx.btables_ref(btable_id)
"loc_base_clin_clin_fkey" FOREIGN KEY (clin) REFERENCES wdm_networx.clin(clin)
"loc_base_clin_loc_base_id_fkey" FOREIGN KEY (loc_base_id) REFERENCES wdm_networx.loc_base_dscr(loc_base_id)

networx=> \d wdm_networx.loc_base_dscr
Table "wdm_networx.loc_base_dscr"
Column | Type | Modifiers
----------------+------------------------+-----------
loc_base_id | integer | not null
description | character varying(254) | not null
locations | character varying(254) |
univ_mandatory | character varying(254) |
ent_mandatory | character varying(254) |
Indexes:
"loc_base_dscr_pkey" PRIMARY KEY, btree (loc_base_id)
Referenced by:
"loc_base_clin_loc_base_id_fkey" FOREIGN KEY (loc_base_id) BY wdm_networx.loc_base_clin(loc_base_id)
/|\ /|\
Referenced column(s) in *this* table <----| |---> column(s) in referencing table

Just my 2c.

Thanks!

Stephen

On 4/10/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> ISTM it'd be a good idea if it did, as "are there any incoming
> foreign keys" seems to be a question we constantly ask when solving
> update-performance problems, and there isn't any easy way to check for
> such.

Sure. We wrote a stored proc to do that and it could be really nice to
have it by defaut in the table definition.

Something like:
Incoming foreign keys:
"name_of_the_fkey" (column of this table) REFERENCED BY
table(columns of the other table)
could be nice.
Not sure about the title of the section. Another problem is that this
syntax is not based on valid SQL syntax.

--
Guillaume

Hi,

On 4/11/07, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com> wrote:
>
> On 4/10/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > ISTM it'd be a good idea if it did, as "are there any incoming
> > foreign keys" seems to be a question we constantly ask when solving
> > update-performance problems, and there isn't any easy way to check for
> > such.
>
> On similar lines, maybe \d can also show the list of inheritors when
invoked on a parent.
e.g:
postgres=# \d parent
Table "public.parent"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Indexes:
"parent_a_key" UNIQUE, btree (a)
Inherited by: child

Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com

On Apr 10, 2007, at 9:48 AM, Stephen Frost wrote:
> Referenced by:
> "loc_base_clin_loc_base_id_fkey" FOREIGN KEY (loc_base_id) BY
> wdm_networx.loc_base_clin(loc_base_id)
> /|
> \ /|\
> Referenced column(s) in *this* table
> <----| |---> column(s) in referencing table

+1, and I also like Nikhils' idea of \d reporting if a table inherits
or is inherited from.

Bruce, can we get a TODO?
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Tom Lane wrote:
> psql's \d command tells you about outgoing foreign key constraints
> (ie, ones referencing another table from this one). It doesn't tell
> you about incoming ones (ie, ones where another table references this
> one). ISTM it'd be a good idea if it did, as "are there any incoming
> foreign keys" seems to be a question we constantly ask when solving
> update-performance problems, and there isn't any easy way to check for
> such. I'm not real sure what the printout should look like, though.

Added to TODO:

o Have \d show foreign keys that reference a table's primary key

http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Jim Nasby wrote:
> On Apr 10, 2007, at 9:48 AM, Stephen Frost wrote:
> > Referenced by:
> > "loc_base_clin_loc_base_id_fkey" FOREIGN KEY (loc_base_id) BY
> > wdm_networx.loc_base_clin(loc_base_id)
> > /|
> > \ /|\
> > Referenced column(s) in *this* table
> > <----| |---> column(s) in referencing table
>
> +1, and I also like Nikhils' idea of \d reporting if a table inherits
> or is inherited from.
>
> Bruce, can we get a TODO?

Added to TODO:

o Have \d show child tables that inherit from the specified parent

We already show the parent table for inherited children.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Tom Lane wrote:

> Added to TODO:
>
> o Have \d show foreign keys that reference a table's primary key
>
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php

We have a patch for this:

http://archives.postgresql.org/pgsql-patches/2008-03/msg00005.php

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
>
> > Added to TODO:
> >
> > o Have \d show foreign keys that reference a table's primary key
> >
> > http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php
>
> We have a patch for this:
>
> http://archives.postgresql.org/pgsql-patches/2008-03/msg00005.php

Oh, it is in the patches queue already --- nice.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +