Skip site navigation (1) Skip section navigation (2)

Re: issue about information_schema REFERENTIAL_CONSTRAINTS

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
Date: 2010-09-01 14:22:49
Message-ID: alpine.DEB.2.00.1009011557480.2570@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-docs
Dear Tom,

>> 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.
> Postgres does not enforce that constraints have unique names within a
> schema.  The SQL spec does say that they should be unique per-schema,
> and the information_schema views are designed on that assumption.

Hence a contradiction.

> If you use spec-compliant names for your constraints, you won't have a
> problem.  If you don't, well, the information_schema views will be of
> limited use to you.

I'm writing a schema analyzer which gives false results. I do not write 
the constraints, I'm analyzing existing schemas. I cannot change it.

>> Suggestion: constraint names could be systematically prefixed with their
>> corresponding table so that they are indeed unique,
> We are not going to try to enforce uniqueness.

I'm not asking for uniqueness in "pg_catalog", esp as that would break 
existing applications.

I'm suggesting uniqueness in the "information_schema", which can be 
provided independently by some tweaking in the view construction, I think, 
for instance by adding the oid of the constraint or maybe the table_name.

> This has been debated before, and most people like the current behavior 
> just fine, or at least better than the alternatives.

I do not know "most people". I guess "most people" just do not use the 
"information_schema", so they really do not care!

For the "few people" who do use the information_schema, I can assure you 
that having a false information is a severe drawback, and it is called a 

So at least please fill in this as a "bug" somewhere, even if you do not 
want to fix it.


In response to


pgsql-docs by date

Next:From: Thom BrownDate: 2010-09-01 14:28:19
Subject: Re: [pgsql-www] Example indenting
Previous:From: Thom BrownDate: 2010-09-01 14:06:57
Subject: Re: [pgsql-www] Example indenting

pgsql-bugs by date

Next:From: Cristian BittelDate: 2010-09-01 14:49:52
Subject: Re: [BUGS] BUG #5305: Postgres service stops when closing Windows session
Previous:From: Euler Taveira de OliveiraDate: 2010-09-01 14:18:10
Subject: Re: BUG #5629: ALTER SEQUENCE foo START execute a RESTART

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group