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.
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 |
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 |