Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
От | Bruce Momjian |
---|---|
Тема | Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS |
Дата | |
Msg-id | 201102220115.p1M1FEf06536@momjian.us обсуждение исходный текст |
Ответ на | Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS (Fabien COELHO <coelho@cri.ensmp.fr>) |
Список | pgsql-docs |
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 ----
В списке pgsql-docs по дате отправления:
Следующее
От: Bruce MomjianДата:
Сообщение: Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS