Thread: bug in information_schema?

Found this in 7.4.5, verified it's still in 8.0b4:

test=> select * from information_schema.referential_constraints;
test|public|$1|test|public|reftest1_pkey|NONE|NO ACTION|CASCADE
test|public|$1|test|public|reftest1_pkey|NONE|NO ACTION|NO ACTION
test|public|$1|test|public|reftest1_pkey|NONE|NO ACTION|NO ACTION

In that case there are three tables all referencing the same column of a
fourth - but there is no way at all to tell which row corresponds to which
table, and hence no way to join against, say, table_constraints.

A similar issue may exist with the check_constraints view, which was the
only other place I found where constraint_name is used without any table
name being present. I don't know what the spec says, but it seems that
something is assuming that constraint_name is unique within the schema,
which of course is not the case in the above example.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Sat, 27 Nov 2004 14:24:40 +0000, Andrew - Supernews wrote:

> A similar issue may exist with the check_constraints view, which was the
> only other place I found where constraint_name is used without any table
> name being present. I don't know what the spec says, but it seems that
> something is assuming that constraint_name is unique within the schema,
> which of course is not the case in the above example.

It has been discussed elsewhere some months ago:
http://thread.gmane.org/gmane.comp.db.postgresql.sql/11397

The problem makes the INFORMATION_SCHEMA rather useless for some kinds of
queries, like you have shown. However, I don't believe that schema-unique
constraint names will be an option any time soon, due to backwards
compatibility :-(

--
Greetings from Troels Arvin, Copenhagen, Denmark

Troels Arvin <troels@arvin.dk> writes:
> On Sat, 27 Nov 2004 14:24:40 +0000, Andrew - Supernews wrote:
>> something is assuming that constraint_name is unique within the schema,
>> which of course is not the case in the above example.

> The problem makes the INFORMATION_SCHEMA rather useless for some kinds of
> queries, like you have shown. However, I don't believe that schema-unique
> constraint names will be an option any time soon, due to backwards
> compatibility :-(

We have changed 8.0 to ensure that automatically-generated constraint
names are unique across a schema.  I doubt we will ever enforce that
against user-specified names, though.  If you want to use the
information schema to trace constraints, you'll have to impose that
discipline on yourself.

            regards, tom lane