Date: Tue, 31 Aug 2010 14:03:43 +0200 (CEST)
From: Fabien COELHO <coelho@cri.ensmp.fr>
Reply-To: Fabien COELHO <coelho@cri.ensmp.fr>
To: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Subject: issue about information_schema REFERENTIAL_CONSTRAINTS
Message-ID: <alpine.DEB.2.00.1008311337530.2449@localhost.localdomain>
User-Agent: Alpine 2.00 (DEB 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; format=flowed; charset=US-ASCII


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.