From: Fabien COELHO <coelho@cri.ensmp.fr>
To: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Subject: issue about information_schema REFERENTIAL_CONSTRAINTS
Date: Tue, 31 Aug 2010 14:03:43 +0200 (CEST)
Message-ID: <alpine.DEB.2.00.1008311337530.2449@localhost.localdomain> (raw)
Hello,
I haven't found a bug management system about postgresql, so here is a
mail. Maybe this issue was already reported, sorry if it is the case.
I have seen anything about the information_schema in pg todo list.
This is tested on postgresql 8.4.4.
The REFERENTIAL_CONSTRAINTS table in the information_schema references a
constaint through its database/schema/name, but this information is not
unique, so it may identify several constraints, thus the information
derived may not be consistent.
CREATE TABLE destination(id SERIAL PRIMARY KEY);
CREATE TABLE source1
(id SERIAL PRIMARY KEY,
fk INTEGER CONSTRAINT to_destination REFERENCES destination);
CREATE TABLE source2
(id SERIAL PRIMARY KEY,
fk INTEGER CONSTRAINT to_destination REFERENCES destination);
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS;
-- contains two identical lines
Other tables about constraints may have the same issue.
The direct result is that this table leads to false result on joins, thus
is pretty useless. Usually I have plenty of "$1" constraints.
Suggestion: constraint names could be systematically prefixed with their
corresponding table so that they are indeed unique, possibly with some
escaping: '"<double-quoted-table-name>"."constraint_name"'. I'm not sure
about the possible consequences of changing the constraint names, but as
the information is a set of views on top of pg_catalog, there may be none.
--
Fabien.
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-docs@postgresql.org
Cc: coelho@cri.ensmp.fr, pgsql-bugs@postgresql.org
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
In-Reply-To: <alpine.DEB.2.00.1008311337530.2449@localhost.localdomain>
* 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