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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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