issue about information_schema REFERENTIAL_CONSTRAINTS

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: issue about information_schema REFERENTIAL_CONSTRAINTS
Date: 2010-08-31 12:03:43
Message-ID: alpine.DEB.2.00.1008311337530.2449@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-docs


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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Dave Page 2010-08-31 13:18:15 Re: [BUGS] BUG #5305: Postgres service stops when closing Windows session
Previous Message vamsi krishna 2010-08-31 08:38:57 Estimation of Plan quality

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2010-08-31 14:46:59 Re: issue about information_schema REFERENTIAL_CONSTRAINTS
Previous Message Thom Brown 2010-08-30 17:37:51 Re: [pgsql-www] Example indenting