Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

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. +
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 ----