From: Fabien COELHO <coelho@cri.ensmp.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
Date: Wed, 1 Sep 2010 16:22:49 +0200 (CEST)
Message-ID: <alpine.DEB.2.00.1009011557480.2570@localhost.localdomain> (raw)
In-Reply-To: <2683.1283266019@sss.pgh.pa.us>
References: <alpine.DEB.2.00.1008311337530.2449@localhost.localdomain>
<2683.1283266019@sss.pgh.pa.us>
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
"bug".
So at least please fill in this as a "bug" somewhere, even if you do not
want to fix it.
--
Fabien.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: coelho@cri.ensmp.fr, tgl@sss.pgh.pa.us, pgsql-bugs@postgresql.org
Subject: Re: issue about information_schema REFERENTIAL_CONSTRAINTS
In-Reply-To: <alpine.DEB.2.00.1009011557480.2570@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox