From: Alex Zeng <Alex.Zeng@datam.co.nz>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: information_schema.constraint_column_usage  shows wrong table name
Thread-Topic: information_schema.constraint_column_usage  shows wrong table
 name
Thread-Index: AQHXjZJ8lMlT2I3JkESr2rzHKt6Skw==
Date: Tue, 10 Aug 2021 02:57:00 +0000
Message-ID: 
 <SY4PR01MB7201EE307A0E5F28C4BF26B8B7F79@SY4PR01MB7201.ausprd01.prod.outlook.com>
Accept-Language: en-NZ, en-US
Content-Language: en-NZ
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-8859-1?Q?DCtd8LB2B+kkYfMr2Q5Nbc2BXQEBKPIjlIc+RjgJExq7dEpOpSYionw69S?=
 =?iso-8859-1?Q?STPm67rX5ITgip/6E5RPkePzeySylbeX/UaKAenXnoDvI7wUpZD4hla6AB?=
 =?iso-8859-1?Q?BKRi4h7uonsLkcNpg3q+8PnnKs2iM3WmLIaxHU1eGUdg39Oz0yDwXnD9Lw?=
 =?iso-8859-1?Q?55OyQhNHLUeIaFOOCB4bx5VLOLssYXNX20+q1YXb0/CGGuucCokr0tlkMH?=
 =?iso-8859-1?Q?UxjYC0e3I8AElZcOCGv4qw+my9C11WxaIehZRUcom6M8Wnp41PrBP+wC1A?=
 =?iso-8859-1?Q?r/wYPGxTNO+c+UM6iQMCFc3anEd6zOtQFSWGa8K6Kuvk/xV/oYkqv3jSl0?=
 =?iso-8859-1?Q?oqXI8EdWB23U20WQPuxwcAytbJPSoFBzyGgQ2uMTq400FRbSCwib3h7BGL?=
 =?iso-8859-1?Q?Gcd3PPLo05JMDohE0QQ9aGdep2gg3W44afp1cKjKj0Q1EGa6udRD8Qtncr?=
 =?iso-8859-1?Q?GY5x974jq+0EUWTJvEMNi5QRc6UPmZgAHsFuw/zXL4tx0ejLxih1slKuHN?=
 =?iso-8859-1?Q?GyYmvp6HBpICBbSK+xD/uleyCyrnFPAl4PfaQLgG9hkaxNqqgVF5sJXCAD?=
 =?iso-8859-1?Q?wLSdZufRnTB6yhDvzqDWAciP0m1Tv1UXiUuEw98ZozQxbfq+aGRqf7HUB3?=
 =?iso-8859-1?Q?DgH8RWzlRvF6DzM71JNmHSUSw7US+U93x4hMZFgbd2wR7w58txMzIzxh3A?=
 =?iso-8859-1?Q?69U2LnJiVcGiHbla4XHsiJXH1PMakcYF/f7PuRvfTrxGiG9m193wHaMe6W?=
 =?iso-8859-1?Q?36zvEHBfzhJKR4Vu//ajm80weJ1LLbOPx4uq2P3uAQS0p9a2UcK7y2HIsH?=
 =?iso-8859-1?Q?CScKTGtuajGLA3QEzFouQ89+uBgs7Hkag7eyNWn2+rbIbvnBOeigzf9OBt?=
 =?iso-8859-1?Q?Nkhfkg51ORVmOroHFBexMXFdnJUCWvhfzOyQiIbuQVk93xZKZ5JRHrR0sM?=
 =?iso-8859-1?Q?uI4D0grSmMQ0onS2GOk9saJrR/qT93ch2EByRzwr0UXUtS6+Y7TERPxvcl?=
 =?iso-8859-1?Q?wSSfjqCb+MgsEbO5sRcLiCVQJtdvu3dSijTcYZ2JNGaWsfs1cc56pOwkON?=
 =?iso-8859-1?Q?AiLN5jYII1o3N+IgPjGzvyke7KRVQofesN75FXS81P83ttII3xHutqALeN?=
 =?iso-8859-1?Q?CrMu12oag/No8JQZ2oAUIME0PanYtHk2/HDm659zChgQ8IrkkhRaCGGO01?=
 =?iso-8859-1?Q?RC028ETgEkZzKY+ppgi+x2q3LsNppUQZj21zruOvkMpbwDPdDAjr0qCzIs?=
 =?iso-8859-1?Q?LfODjneQn3wVxDy3RtFREdn1/rSzCzIfsp6Snd1VZFPAEUCHmVhvAY2bBH?=
 =?iso-8859-1?Q?b3BjqNj6q4TlCzBKQ4mXsgiDu/awTWIDlDMHAGLhuXmKbSyDHcC25OcdoF?=
 =?iso-8859-1?Q?0mN4RQlkX1?=
x-ms-exchange-transport-forked: True
Content-Type: multipart/alternative;
	boundary="_000_SY4PR01MB7201EE307A0E5F28C4BF26B8B7F79SY4PR01MB7201ausp_"
MIME-Version: 1.0
X-OriginatorOrg: datam.co.nz
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: SY4PR01MB7201.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 041cfc2f-7e5f-479f-441c-08d95baa863b
X-MS-Exchange-CrossTenant-originalarrivaltime: 10 Aug 2021 02:57:00.2940
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: b5e5df3e-0bd8-4eb9-acb8-e9792f3c84dd
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 hf+P/urSt7gDv128Jy4eQQANOZmA47Q9Bl9+F6t2R3ojr8jm8C4zoXbk5l3+N4S7OuBSbFboaQy6IMWuCJw/Eg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SYXPR01MB1709
X-Proofpoint-GUID: WhfWmuzRUrLPGnu93K4ep6qRyCY_-flx
X-Proofpoint-ORIG-GUID: WhfWmuzRUrLPGnu93K4ep6qRyCY_-flx
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.182.1,Aquarius:18.0.790,Hydra:6.0.391,FMLib:17.0.607.475
 definitions=2021-08-10_01,2021-08-06_01,2020-04-07_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 mlxscore=0 bulkscore=0
 mlxlogscore=805 spamscore=0 impostorscore=0 phishscore=0 adultscore=0
 lowpriorityscore=0 malwarescore=0 clxscore=1011 priorityscore=1501
 suspectscore=0 classifier=spam adjust=0 reason=mlx scancount=1
 engine=8.12.0-2107140000 definitions=main-2108100017
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/SY4PR01MB7201EE307A0E5F28C4BF26B8B7F79%40SY4PR01MB7201.ausprd01.prod.outlook.com>
Precedence: bulk

--_000_SY4PR01MB7201EE307A0E5F28C4BF26B8B7F79SY4PR01MB7201ausp_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

When creating a constraint from table A to table B, the table name and colu=
mn name in information_schema.constraint_column_usage  shows table B. That =
makes impossible to check whether a constraint exists as we can have a cons=
traint from table C to B and they will have same value in information_schem=
a.constraint_column_usage.

Should we make the table_name and column_name as A's name?

=3D# create table item(id integer, item_name character VARYING(64),cat_id i=
nteger);
CREATE TABLE
=3D# create table catagory(id integer, cat_name character VARYING(64),CONST=
RAINT catagory_pkey PRIMARY KEY (id) );
CREATE TABLE
=3D# alter table item add constraint fk_cat_id FOREIGN KEY (cat_id) REFEREN=
CES catagory(id);
ALTER TABLE
=3D# select * from information_schema.constraint_column_usage where constra=
int_name=3D'fk_cat_id';
 table_catalog | table_schema | table_name | column_name | constraint_catal=
og | constraint_schema | constraint_name
---------------+--------------+------------+-------------+-----------------=
---+-------------------+-----------------
 vretrieve     | public       | catagory   | id          | vretrieve       =
   | public            | fk_cat_id
(1 row)


This email with any attachments is confidential and may be subject to legal=
 privilege. If it is not intended for you please reply immediately, destroy=
 it and do not copy, disclose or use it in any way.

--_000_SY4PR01MB7201EE307A0E5F28C4BF26B8B7F79SY4PR01MB7201ausp_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
Hi,</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
When creating a constraint from table A to table B, the table name and colu=
mn name in&nbsp;<span style=3D"background-color:rgb(255, 255, 255);display:=
inline !important">information_schema.constraint_column_usage<span>&nbsp; s=
hows table B. That makes impossible to check
 whether a constraint exists as we can have a constraint from table C to B =
and they will have same value in&nbsp;<span style=3D"background-color:rgb(2=
55, 255, 255);display:inline !important">information_schema.constraint_colu=
mn_usage.</span></span></span></div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
Should we make the table_name and column_name as A's name?</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
=3D# create table item(id integer, item_name character VARYING(64),cat_id i=
nteger);
<div>CREATE TABLE</div>
<div>=3D# create table catagory(id integer, cat_name character VARYING(64),=
CONSTRAINT catagory_pkey PRIMARY KEY (id) );</div>
<div>CREATE TABLE</div>
<div>=3D# alter table item add constraint fk_cat_id FOREIGN KEY (cat_id) RE=
FERENCES catagory(id);</div>
<div>ALTER TABLE</div>
<div>=3D# select * from information_schema.constraint_column_usage where co=
nstraint_name=3D'fk_cat_id';</div>
<div>&nbsp;table_catalog | table_schema | table_name | column_name | constr=
aint_catalog | constraint_schema | constraint_name</div>
<div>---------------+--------------+------------+-------------+------------=
--------+-------------------+-----------------</div>
<div>&nbsp;vretrieve &nbsp; &nbsp; | public &nbsp; &nbsp; &nbsp; | catagory=
 &nbsp; | id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| vretrieve &nbsp; &nbsp; &n=
bsp; &nbsp; &nbsp;| public &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| fk_ca=
t_id</div>
<span>(1 row)</span><span></span><br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<span><br>
</span></div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<span><br>
</span></div>
This email with any attachments is confidential and may be subject to legal=
 privilege. If it is not intended for you please reply immediately, destroy=
 it and do not copy, disclose or use it in any way.
</body>
</html>

--_000_SY4PR01MB7201EE307A0E5F28C4BF26B8B7F79SY4PR01MB7201ausp_--