Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

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.