Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

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:-)

OK, based on the view list above and Tom's request, I have added an
additional paragraph to the documenation naming the views which might be
affected by duplicated named constraints;  the second paragraph is the
paragraph I added:

 <note>
  <para>
   When querying the database for constraint information, it is possible
   for a standard-compliant query that expects to return one row to
   return several.  This is because the SQL standard requires constraint
   names to be unique within a schema, but
   <productname>PostgreSQL</productname> does not enforce this
   restriction.  <productname>PostgreSQL</productname>
   automatically-generated constraint names avoid duplicates in the
   same schema, but users can specify such duplicate names.
  </para>

  <para>
   This problem can appear when querying information schema views like
   <literal>referential_constraints</>, <literal>check_constraints</>,
   <literal>domain_constraints</>, and
   <literal>check_constraint_routine_usage</>.  Some other views have
   similar issues but contain the table name to help distinguish
   duplicate rows, e.g. <literal>table_constraints</>,
   <literal>constraint_table_usage</>, <literal>constraint_column_usage</>.
  </para>
 </note>


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

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