From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: super19990310@163.com
Subject: BUG #16642: INFORMATION_SCHEMA-DESIGN
Date: Tue, 29 Sep 2020 17:31:48 +0000
Message-ID: <16642-24d8e3f3dea9660e@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16642
Logged by: YONGPENG SU
Email address: super19990310@163.com
PostgreSQL version: 12.4
Operating system: WIN10
Description:
Three table constraint_column_usage, key_column_usage and table_constrants
in information_schema describe all constraints of a table and it's column,
especially FOREIGN KEY constraints. A widely-used QUERY "A Primary Key of a
table is refferenced by other table and it's columns", we can esaily join
the the table mentioned above by "constriant_name" equaled to finish SQL.
But what's new above PG11, also in PG12 is Partition-Table Definition. The
Parent can define PK and FK constraints, and its Sub-Partition Table
inherits the FK constraints and has the same FK constraints name with the
parent, which causes the FK constraint name can't identify a FK constraint
and it is ambiguous in the literal meaning. in this case(Parent Partition
Table and Sub-Partition Table have the same FK constraint name), we can only
identify its id by the table name and constraint name. so can their be
represented more column to express the reation cleatly.
eg. information_schema.constraint_column_usage describe A table's id(PK) is
refferenced by constraints(constraint_name), can it add a column table_name
matched with constraint_name to identify a constraint. or the join by
constraint_name(if there are many sub-partition table of a table means many
same constraint_name. the join will be much slower, the join by the
constraint_name is not 1-1, becomes 1-n to multiple each row, the total
rows becomes n*n*n). Here is my practice, looking forward to your
improvement.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, super19990310@163.com
Subject: Re: BUG #16642: INFORMATION_SCHEMA-DESIGN
In-Reply-To: <16642-24d8e3f3dea9660e@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox