From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201102220115.p1M1FEf06536@momjian.us>
Subject: Re: [BUGS] issue about information_schema
 REFERENTIAL_CONSTRAINTS
In-Reply-To: <alpine.DEB.2.00.1102200825550.2423@localhost6.localdomain6>
To: Fabien COELHO <coelho@cri.ensmp.fr>
Date: Mon, 21 Feb 2011 20:15:14 -0500 (EST)
CC: Peter Eisentraut <peter_e@gmx.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	Alvaro Herrera <alvherre@commandprompt.com>, pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1298337314-2216-1_"
Content-Transfer-Encoding: 7bit


--ELM1298337314-2216-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


I have moved the text about duplicate constraints to the top of the
information schema section because it affects several tables (applied
patch attached).  I could not figure out how to get the actual error
concept to the front of the paragraph.

---------------------------------------------------------------------------

Fabien COELHO wrote:
> 
> Hello Bruce,
> 
> >>>> Is that the direction we want to go, or would it be better to factor
> >>>> the information out into a separate page about compatibility gotchas?
> >>>
> >>> It would probably be better to explain globally applicable issues in a
> >>> separate section.
> >>
> >> I agree that a general caveat is better, together with a one line
> >> reference in the documentation of each table with an issue.
> >
> > Oh, I just noticed this.  Can you give me a list of information_schema
> > tables that have this issue?  I am only aware of
> > referential_constraints.
> 
> Possibly any relation which references constraints with a (catalog, 
> schema, name) triplet expecting it to be unique should have this issue.
> 
> >From a quick scan on the information_schema, I would say:
>   - check_constraint_routine_usage
>   - check_constraints
>   - constraint_column_usage (*)
>   - constraint_table_usage (*)
>   - domain_constraints
>   - referential_constraints
>   - table_constraints (*)
> 
> For the three starred relations, the issue is not too big because a 
> constraint name is unique per table in pgsql, and the table name is also 
> given in these relations.
> 
> This issue makes the "information_schema" pretty useless for being really 
> use for serious work as the data can be ambiguous, so I still claim that 
> for me this is a real "bug" rather than just a "feature", which is the 
> status reached once a bug is documented:-)
> 
> When constraint names are generated by postgresql, ISTM that the software 
> is free to choose them so they could be chosen non ambiguous per schema.
> 
> When users choose colliding names, I agree that it would break existing 
> schemas, but there could be an option to enforce uniqueness of the name 
> per schema if desired.
> 
> I know there are some underlying issues with that that were discussed 
> previously.
> 
> Anyway I would appreciate something that it appears in the "todo" list, 
> even if it is never implemented:-)
> 
> -- 
> Fabien.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--ELM1298337314-2216-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/constraint.diff"

diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 91c2dd4..32e9083 100644
*** a/doc/src/sgml/information_schema.sgml
--- b/doc/src/sgml/information_schema.sgml
***************
*** 21,26 ****
--- 21,39 ----
    <productname>PostgreSQL</productname>-specific views.
   </para>
  
+  <note>
+   <para>
+    The SQL standard requires constraint names to be unique within a
+    schema;  <productname>PostgreSQL</productname>, however, does not
+    enforce this restriction.  If duplicate-named constraints are
+    stored in the same <productname>PostgreSQL</productname> schema,
+    a standard-compliant query that expects to return one matching
+    constraint row might return several, one row for each matching
+    constraint stored in the specified schema. 
+   </para>
+  </note>
+ 
+ 
   <sect1 id="infoschema-schema">
    <title>The Schema</title>
  
*************** ORDER BY c.ordinal_position;
*** 3212,3229 ****
     </tgroup>
    </table>
  
-   <note>
-    <para>
-     The SQL standard requires constraint names to be unique within a
-     schema;  <productname>PostgreSQL</productname>, however, does not
-     enforce this restriction.  If duplicate-named constraints are
-     stored in the same <productname>PostgreSQL</productname> schema, a
-     standard-compliant query that expects to return one row might
-     return several, one for each matching constraint stored in the
-     specified schema.
-    </para>
-   </note>
- 
   </sect1>
  
   <sect1 id="infoschema-role-column-grants">
--- 3225,3230 ----

--ELM1298337314-2216-1_--