From: Bruce Momjian <bruce@momjian.us>
To: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
Date: Mon, 21 Feb 2011 20:15:14 -0500 (EST)
Message-ID: <201102220115.p1M1FEf06536@momjian.us> (raw)
In-Reply-To: <alpine.DEB.2.00.1102200825550.2423@localhost6.localdomain6>
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. +
Attachments:/rtmp/constraint.diff (text/x-diff, 1.5K) downloaddiff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgmlindex 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 ----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, coelho@cri.ensmp.fr, peter_e@gmx.net, tgl@sss.pgh.pa.us, alvherre@commandprompt.com
Subject: Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
In-Reply-To: <201102220115.p1M1FEf06536@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox