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.
