Thread: Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Tom Lane
Date:
[ 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

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Peter Eisentraut
Date:
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.


Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Fabien COELHO
Date:
>> 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.

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Bruce Momjian
Date:
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. +

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Fabien COELHO
Date:
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.

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Tom Lane
Date:
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

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Fabien COELHO
Date:
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.

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Bruce Momjian
Date:
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 ----

Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS

From
Bruce Momjian
Date:
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. +