Обсуждение: 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@sss.pgh.pa.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_clinTable "wdm_networx.loc_base_clin" Column | Type | Modifiers
-------------+---------+-----------btable_id | integer | not nullloc_base_id | integer | not nullclin |
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 nulldescription |
charactervarying(254) | not nulllocations | 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@sss.pgh.pa.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,
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 4/11/07, Guillaume Smet <guillaume.smet@gmail.com> wrote:
On similar lines, maybe \d can also show the list of inheritors when invoked on a parent. On 4/10/07, Tom Lane <tgl@sss.pgh.pa.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.
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@nasby.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@momjian.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@momjian.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@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +