Thread: Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
[ redirecting to pgsql-docs ] Peter Eisentraut <peter_e@gmx.net> writes: > On fre, 2010-09-03 at 13:53 -0400, Tom Lane wrote: >> It would probably be reasonable to put something into chapter 34 of the >> docs pointing out this limitation. I'm not sure exactly where though. >> Should we invent a "compatibility issues" section in that chapter, >> analogous to the ones we have for individual SQL commands? Are there >> other issues worth documenting for the information_schema views? > A very similar issue is already documented here: > http://developer.postgresql.org/pgdocs/postgres/infoschema-triggers.html That sort of annotation was my first thought, but we'd have to repeat ourselves in multiple places because there are several views that are affected by the constraint-name issue. (Come to think of it, isn't triggered_update_columns also affected by the trigger-name issue?) 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? regards, tom lane
On sön, 2010-09-05 at 22:16 -0400, Tom Lane wrote: > > A very similar issue is already documented here: > > http://developer.postgresql.org/pgdocs/postgres/infoschema-triggers.html > > That sort of annotation was my first thought, but we'd have to repeat > ourselves in multiple places because there are several views that are > affected by the constraint-name issue. (Come to think of it, isn't > triggered_update_columns also affected by the trigger-name issue?) > > 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.
>> 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. -- Fabien.
Fabien COELHO wrote: > > >> 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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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.
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> 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.
We do that already. See ChooseConstraintName and ChooseRelationName.
> 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.
What would be the point of that? IIRC, your complaint about this was in
connection with wanting to write some information-schema-using code that
could be used by anybody. Such code could hardly assume that the option
was set.
Basically, the only way to satisfy your request is to take freedom away
from our users, and we're unlikely to do that.
regards, tom lane
Hello, >> 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. > > We do that already. See ChooseConstraintName and ChooseRelationName. Very good news! I had not noticed that change yet. No more multiple $1 constraints then. >> 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. > > What would be the point of that? IIRC, your complaint about this was in > connection with wanting to write some information-schema-using code that > could be used by anybody. Such code could hardly assume that the option > was set. I was thinking the other way around: the default would be "true", and setting "false" would allow backward compatibility, if needed. In my mind, the "if desired" was yes by default. -- Fabien.
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 ----
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. +