pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedissue about information_schema REFERENTIAL_CONSTRAINTS
33+ messages / 8 participants
[nested] [flat]
* issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-08-31 12:03 Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-08-31 12:03 UTC (permalink / raw)
To: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Hello,
I haven't found a bug management system about postgresql, so here is a
mail. Maybe this issue was already reported, sorry if it is the case.
I have seen anything about the information_schema in pg todo list.
This is tested on postgresql 8.4.4.
The REFERENTIAL_CONSTRAINTS table in the information_schema references a
constaint through its database/schema/name, but this information is not
unique, so it may identify several constraints, thus the information
derived may not be consistent.
CREATE TABLE destination(id SERIAL PRIMARY KEY);
CREATE TABLE source1
(id SERIAL PRIMARY KEY,
fk INTEGER CONSTRAINT to_destination REFERENCES destination);
CREATE TABLE source2
(id SERIAL PRIMARY KEY,
fk INTEGER CONSTRAINT to_destination REFERENCES destination);
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS;
-- contains two identical lines
Other tables about constraints may have the same issue.
The direct result is that this table leads to false result on joins, thus
is pretty useless. Usually I have plenty of "$1" constraints.
Suggestion: constraint names could be systematically prefixed with their
corresponding table so that they are indeed unique, possibly with some
escaping: '"<double-quoted-table-name>"."constraint_name"'. I'm not sure
about the possible consequences of changing the constraint names, but as
the information is a set of views on top of pg_catalog, there may be none.
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-08-31 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 2 replies; 33+ messages in thread
From: Tom Lane @ 2010-08-31 14:46 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> The REFERENTIAL_CONSTRAINTS table in the information_schema references a
> constaint through its database/schema/name, but this information is not
> unique, so it may identify several constraints, thus the information
> derived may not be consistent.
Postgres does not enforce that constraints have unique names within a
schema. The SQL spec does say that they should be unique per-schema,
and the information_schema views are designed on that assumption.
If you use spec-compliant names for your constraints, you won't have a
problem. If you don't, well, the information_schema views will be of
limited use to you.
> Suggestion: constraint names could be systematically prefixed with their
> corresponding table so that they are indeed unique,
We are not going to try to enforce uniqueness. This has been debated
before, and most people like the current behavior just fine, or at least
better than the alternatives.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-01 14:22 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-01 14:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Dear Tom,
>> The REFERENTIAL_CONSTRAINTS table in the information_schema references a
>> constaint through its database/schema/name, but this information is not
>> unique, so it may identify several constraints, thus the information
>> derived may not be consistent.
>
> Postgres does not enforce that constraints have unique names within a
> schema. The SQL spec does say that they should be unique per-schema,
> and the information_schema views are designed on that assumption.
Hence a contradiction.
> If you use spec-compliant names for your constraints, you won't have a
> problem. If you don't, well, the information_schema views will be of
> limited use to you.
I'm writing a schema analyzer which gives false results. I do not write
the constraints, I'm analyzing existing schemas. I cannot change it.
>> Suggestion: constraint names could be systematically prefixed with their
>> corresponding table so that they are indeed unique,
>
> We are not going to try to enforce uniqueness.
I'm not asking for uniqueness in "pg_catalog", esp as that would break
existing applications.
I'm suggesting uniqueness in the "information_schema", which can be
provided independently by some tweaking in the view construction, I think,
for instance by adding the oid of the constraint or maybe the table_name.
> This has been debated before, and most people like the current behavior
> just fine, or at least better than the alternatives.
I do not know "most people". I guess "most people" just do not use the
"information_schema", so they really do not care!
For the "few people" who do use the information_schema, I can assure you
that having a false information is a severe drawback, and it is called a
"bug".
So at least please fill in this as a "bug" somewhere, even if you do not
want to fix it.
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-01 19:38 Peter Eisentraut <peter_e@gmx.net>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 33+ messages in thread
From: Peter Eisentraut @ 2010-09-01 19:38 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
On ons, 2010-09-01 at 16:22 +0200, Fabien COELHO wrote:
> I'm suggesting uniqueness in the "information_schema", which can be
> provided independently by some tweaking in the view construction, I
> think, for instance by adding the oid of the constraint or maybe the
> table_name.
The view is defined by the SQL standard. We cannot change it.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-02 05:54 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-02 05:54 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Dear Peter,
>> I'm suggesting uniqueness in the "information_schema", which can be
>> provided independently by some tweaking in the view construction, I
>> think, for instance by adding the oid of the constraint or maybe the
>> table_name.
>
> The view is defined by the SQL standard.
No. The result of the view (the definition of the expected attributes) is
defined in the standard. But it is really a view on top of "pg_catalog".
You are right that some views of the information_schema are defined in the
standard, but they deal with restrictions of other relations, say the
privileges for the current user...
> We cannot change it.
Yes we can! It, it is 100% postgresql:
\d information_schema.referential_constraints
View "information_schema.referential_constraints"
...
FROM pg_namespace ncon
JOIN pg_constraint con ON ncon.oid = con.connamespace
JOIN pg_class c ON con.conrelid = c.oid
--
Fabien Coelho - CRI, Maths & Systèmes, MINES ParisTech
From pgsql-bugs-owner@postgresql.org Thu Sep 2 07:41:38 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
by mail.postgresql.org (Postfix) with ESMTP id 16994632AF0
for <pgsql-bugs-postgresql.org@mail.postgresql.org>; Thu, 2 Sep 2010 07:41:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024)
with ESMTP id 81079-04 for <pgsql-bugs-postgresql.org@mail.postgresql.org>;
Thu, 2 Sep 2010 10:41:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from wwwmaster.postgresql.org (wwwmaster.postgresql.org [217.196.146.204])
by mail.postgresql.org (Postfix) with ESMTP id 598D2632A2B
for <pgsql-bugs@postgresql.org>; Thu, 2 Sep 2010 07:41:37 -0300 (ADT)
Received: from wwwmaster.postgresql.org (wwwmaster.postgresql.org [217.196.146.204])
by wwwmaster.postgresql.org (8.14.3/8.14.3) with ESMTP id o82AfXM0085948
for <pgsql-bugs@postgresql.org>; Thu, 2 Sep 2010 10:41:33 GMT
(envelope-from www@wwwmaster.postgresql.org)
Received: (from www@localhost)
by wwwmaster.postgresql.org (8.14.3/8.14.3/Submit) id o82AfX1n085947;
Thu, 2 Sep 2010 10:41:33 GMT
(envelope-from www)
Date: Thu, 2 Sep 2010 10:41:33 GMT
Message-Id: <201009021041.o82AfX1n085947@wwwmaster.postgresql.org>
To: pgsql-bugs@postgresql.org
Subject: BUG #5636: extra "(" or missing ")"
From: "KOIZUMI Satoru" <koizumistr@minos.ocn.ne.jp>
Content-Type: text/plain; charset=utf-8
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 201009/8
X-Sequence-Number: 27972
The following bug has been logged online:
Bug reference: 5636
Logged by: KOIZUMI Satoru
Email address: koizumistr@minos.ocn.ne.jp
PostgreSQL version: 9.0RC1
Operating system: MacOS X
Description: extra "(" or missing ")"
Details:
At 33.8.5. The decimal type of PostgreSQL 9.0RC1 Documentation, "(" is
before "by means".
But there is no ")".
| the decimal type can be created either on the stack
| or on the heap (by means of the functions
| PGTYPESdecimal_new() and PGTYPESdecimal_free().
Maybe "(" is not needed or ")" is needed after "PGTYPESdecimal_free()".
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-02 14:40 Peter Eisentraut <peter_e@gmx.net>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 33+ messages in thread
From: Peter Eisentraut @ 2010-09-02 14:40 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
On tor, 2010-09-02 at 07:54 +0200, Fabien COELHO wrote:
> > We cannot change it.
>
> Yes we can! It, it is 100% postgresql:
>
> \d information_schema.referential_constraints
> View "information_schema.referential_constraints"
> ...
> FROM pg_namespace ncon
> JOIN pg_constraint con ON ncon.oid = con.connamespace
> JOIN pg_class c ON con.conrelid = c.oid
Well, for the sake of argument, how would you propose to change it?
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-02 18:34 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-02 18:34 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Hallo,
>> \d information_schema.referential_constraints
>> View "information_schema.referential_constraints"
>> ...
>> FROM pg_namespace ncon
>> JOIN pg_constraint con ON ncon.oid = con.connamespace
>> JOIN pg_class c ON con.conrelid = c.oid
>
> Well, for the sake of argument, how would you propose to change it?
For the sake of argument:
For the information_schema to be useful, expected keys & foreign keys must
work properly so that the constraints can be joined meaningfully
(otherwise, what is the point?). So any change would have to be be
consistent over all definitions.
The standard expects the triplet <catalog,schema,constraint> to be unique,
but in Pg, <catalog,schema,table,constraint> is unique (I think). Too late
to change that, obviously. So that let building a unique constraint name
just for the information_schema, which is fine with me.
(1) use the OID as the "constraint name" everywhere, it would work, it
would not look so good for display, but it is simple and fast.
(2) otherwise something built on top of <table,constraint>. To be on the
safe side, I would build a string (sql_identifier?) with something like:
quote_ident(<table name>) || '.' || quote_ident(<constraint name>)
And the display would be reasonnable, like : "TableName"."$1" instead of a
big bunch of $1.
The affected information_schema views would be, as far as I can see:
- check_constraint_routine_usage
- check_constraints
- constraint_column_usage
- constraint_table_usage
- domain_constraints
- key_column_usage
- referential_constraints
- table_constraints
With one or two affected columns each.
Note that there may be the same issue with 'triggers' which are also
identified by a <catalog,schema,trigger> triplet. Maybe others.
I can send a patch on the information_schema definition in the coming days
if someone feel that this may deserve a "test". Otherwise, ISTM that it is
a "bug" to be recorded somewhere.
Have a nice day,
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-02 19:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2010-09-02 19:12 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> (1) use the OID as the "constraint name" everywhere, it would work, it
> would not look so good for display, but it is simple and fast.
> (2) otherwise something built on top of <table,constraint>. To be on the
> safe side, I would build a string (sql_identifier?) with something like:
> quote_ident(<table name>) || '.' || quote_ident(<constraint name>)
Note that (2) fails for long names; you have to do something to
compress to NAMEDATALEN.
The big problem with either of these is that it's no longer easily
possible to extract the actual constraint name from the view.
In any case, I am fairly sure that not having the constraint_name column
show the actual constraint name is a violation of the spirit of the SQL
spec, whether or not you can claim that it meets the letter.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 12:08 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-03 12:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Dear Tom,
Still for the sake of argument:
> Note that (2) fails for long names; you have to do something to
> compress to NAMEDATALEN.
Indeed.
What if the type is changed to TEXT? It is just a view after all.
How important is it to stick to "sql_identifier"?
> The big problem with either of these is that it's no longer easily
> possible to extract the actual constraint name from the view.
Sure. A function is provided to do so, say
information_schema_constraint_name_to_pg_constraint_name.
> In any case, I am fairly sure that not having the constraint_name column
> show the actual constraint name is a violation of the spirit of the SQL
> spec, whether or not you can claim that it meets the letter.
Well, one must choose between to evil:
(1) the constraint_name is changed in the view to be unique as expected
by the spec, and the data can be joined meaningfully, and some reliable
information can be derived.
(2) the constraint_name looks nice but is not unique, and
the information in the view is ambiguous and cannot be relied upon,
so one is back to square "postgresql supports the information_schema,
but there is no point to query it and expecting the results to
reflect the contents of the catalogs".
If you want to stick to both the letter and the spirit of the spec, that
would mean enforcing unique constraint names in pg and break every
applications. Not good.
ISTM that the "spirit" of the information schema is more to be useful (1)
than to look beautiful (2).
Another technical proposal, a little more subtle and with possible
underlying issues I cannot foresee: have the constraint_name be a "pair of
sql_identifiers".
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 14:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 2 replies; 33+ messages in thread
From: Tom Lane @ 2010-09-03 14:58 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Fabien COELHO <coelho@cri.ensmp.fr> writes:
>> Note that (2) fails for long names; you have to do something to
>> compress to NAMEDATALEN.
> Indeed.
> What if the type is changed to TEXT? It is just a view after all.
> How important is it to stick to "sql_identifier"?
It's a view defined by the SQL standard, and one of the properties
defined by the standard is the type of that column.
> Well, one must choose between to evil:
Yeah, exactly. I think that the current tradeoff is just fine.
If you want SQL-standard behavior, pick SQL-standard constraint names,
and there you are.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 15:14 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 33+ messages in thread
From: Kevin Grittner @ 2010-09-03 15:14 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah, exactly. I think that the current tradeoff is just fine.
> If you want SQL-standard behavior, pick SQL-standard constraint
> names, and there you are.
I see that as the crux if it -- the current implementation *allows*
standard-conforming behavior, even though it doesn't *enforce*
conforming naming. The proposed alternative does not allow
standard-conforming behavior. If you're going to use something
which is PostgreSQL-specific, you may as well write your own views
or use the "native" tables and views directly.
-Kevin
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 17:39 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-03 17:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
>> Well, one must choose between to evil:
>
> Yeah, exactly. I think that the current tradeoff is just fine.
Hmmm. I think exactly the contrary. There is no point in having a non
reliable feature.
ISTM that very few people use the information schema, and if the query
results is not reliable, it will stay this way. If you have zero user, no
one will complain, which prooves that everything is fine:-) QED.
By the way, do you use the information schema?
> If you want SQL-standard behavior, pick SQL-standard constraint names,
> and there you are.
I tried to explained that I'm analyzing other people's schemas. I cannot
ask all other people on the planet to rewrite their schemas, I pick them
as they are.
Could you register this "bug" somewhere please?
Thanks for your time.
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 17:45 Alvaro Herrera <alvherre@commandprompt.com>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 2 replies; 33+ messages in thread
From: Alvaro Herrera @ 2010-09-03 17:45 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Excerpts from Fabien COELHO's message of vie sep 03 13:39:19 -0400 2010:
> I tried to explained that I'm analyzing other people's schemas. I cannot
> ask all other people on the planet to rewrite their schemas, I pick them
> as they are.
Maybe you shouldn't be using the information_schema in the first place.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 17:50 Fabien COELHO <fabien.coelho@ensmp.fr>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-03 17:50 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
> If you're going to use something which is PostgreSQL-specific, you may
> as well write your own views or use the "native" tables and views
> directly.
I wish I could write portable code, if possible:-)
I'm basically writing views on top of the information_schema under the
assumption that what is expected to be a key is a key. The information
schema is *useless* otherwise as wrong tuples are built on join, and
derived information is not reliable.
I guess I must the only actual user of the information_schema, and it will
soon be back to zero user, which will be fine from the developers point of
view.
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 17:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 33+ messages in thread
From: Tom Lane @ 2010-09-03 17:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; Peter Eisentraut <peter_e@gmx.net>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Fabien COELHO's message of vie sep 03 13:39:19 -0400 2010:
>> I tried to explained that I'm analyzing other people's schemas. I cannot
>> ask all other people on the planet to rewrite their schemas, I pick them
>> as they are.
> Maybe you shouldn't be using the information_schema in the first place.
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?
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-03 17:53 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 33+ messages in thread
From: Fabien COELHO @ 2010-09-03 17:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
> Maybe you shouldn't be using the information_schema in the first place.
Sure, I could write non standard code for every database instead of trying
to write a portable code which work on all of them directly:-)
I think that trying to do the portable way, under the assumption that the
standard implementation would be okay, was a reasonnable choice, even if
proved wrong afterwards.
--
Fabien.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-05 18:47 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Peter Eisentraut @ 2010-09-05 18:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Fabien COELHO <coelho@cri.ensmp.fr>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
On fre, 2010-09-03 at 13:53 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from Fabien COELHO's message of vie sep 03 13:39:19 -0400 2010:
> >> I tried to explained that I'm analyzing other people's schemas. I cannot
> >> ask all other people on the planet to rewrite their schemas, I pick them
> >> as they are.
>
> > Maybe you shouldn't be using the information_schema in the first place.
>
> 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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-06 02:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2010-09-06 02:16 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Fabien COELHO <coelho@cri.ensmp.fr>; pgsql-docs
[ 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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-07 14:49 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Peter Eisentraut @ 2010-09-07 14:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Fabien COELHO <coelho@cri.ensmp.fr>; pgsql-docs
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.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-09 08:27 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 33+ messages in thread
From: Fabien COELHO @ 2010-09-09 08:27 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
>> 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.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-12 00:24 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 33+ messages in thread
From: Robert Haas @ 2010-09-12 00:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
On Tue, Aug 31, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> We are not going to try to enforce uniqueness. This has been debated
> before, and most people like the current behavior just fine, or at least
> better than the alternatives.
Really? I thought the issue was that no one had figured out how to do
it, or that no one had written the patch, not that anyone thought the
current behavior was particularly desirable. What happens if you say
ALTER TABLE .. DROP CONSTRAINT or COMMENT ON CONSTRAINT? You just
pick one at random? That's really what most people want?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-12 04:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2010-09-12 04:40 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Aug 31, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We are not going to try to enforce uniqueness. This has been debated
>> before, and most people like the current behavior just fine, or at least
>> better than the alternatives.
> Really? I thought the issue was that no one had figured out how to do
> it, or that no one had written the patch, not that anyone thought the
> current behavior was particularly desirable. What happens if you say
> ALTER TABLE .. DROP CONSTRAINT or COMMENT ON CONSTRAINT? You just
> pick one at random?
No, because those syntaxes constrain the choice to one single
constraint. Perhaps if the SQL committee had designed 'em,
there'd be an issue; but they are Postgres-isms.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2010-09-13 00:27 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 33+ messages in thread
From: Robert Haas @ 2010-09-13 00:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
On Sun, Sep 12, 2010 at 12:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Aug 31, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> We are not going to try to enforce uniqueness. This has been debated
>>> before, and most people like the current behavior just fine, or at least
>>> better than the alternatives.
>
>> Really? I thought the issue was that no one had figured out how to do
>> it, or that no one had written the patch, not that anyone thought the
>> current behavior was particularly desirable. What happens if you say
>> ALTER TABLE .. DROP CONSTRAINT or COMMENT ON CONSTRAINT? You just
>> pick one at random?
>
> No, because those syntaxes constrain the choice to one single
> constraint. Perhaps if the SQL committee had designed 'em,
> there'd be an issue; but they are Postgres-isms.
Hrm. I was thinking of this old thread, but maybe that's not the same issue.
http://archives.postgresql.org/pgsql-hackers/2008-10/msg00256.php
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-19 23:22 Bruce Momjian <bruce@momjian.us>
parent: Fabien COELHO <fabien.coelho@ensmp.fr>
0 siblings, 0 replies; 33+ messages in thread
From: Bruce Momjian @ 2011-02-19 23:22 UTC (permalink / raw)
To: Fabien COELHO <fabien.coelho@ensmp.fr>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
Fabien COELHO wrote:
>
> > If you're going to use something which is PostgreSQL-specific, you may
> > as well write your own views or use the "native" tables and views
> > directly.
>
> I wish I could write portable code, if possible:-)
>
> I'm basically writing views on top of the information_schema under the
> assumption that what is expected to be a key is a key. The information
> schema is *useless* otherwise as wrong tuples are built on join, and
> derived information is not reliable.
>
> I guess I must the only actual user of the information_schema, and it will
> soon be back to zero user, which will be fine from the developers point of
> view.
Based on this report and later discussion, I have applied the attached
documentation patch to warn users about the Postgres behavior of
information_schema.referential_constraints.
--
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, 890B) download
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 52407a7..91c2dd4 100644
*** a/doc/src/sgml/information_schema.sgml
--- b/doc/src/sgml/information_schema.sgml
*************** ORDER BY c.ordinal_position;
*** 3211,3216 ****
--- 3211,3229 ----
</tbody>
</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">
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-20 00:11 Bruce Momjian <bruce@momjian.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
0 siblings, 1 reply; 33+ messages in thread
From: Bruce Momjian @ 2011-02-20 00:11 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
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. +
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-20 07:48 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 3 replies; 33+ messages in thread
From: Fabien COELHO @ 2011-02-20 07:48 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
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.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-20 15:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
2 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2011-02-20 15:35 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Bruce Momjian <bruce@momjian.us>; Peter Eisentraut <peter_e@gmx.net>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-21 08:28 Fabien COELHO <coelho@cri.ensmp.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 33+ messages in thread
From: Fabien COELHO @ 2011-02-21 08:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Peter Eisentraut <peter_e@gmx.net>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
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.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-22 01:15 Bruce Momjian <bruce@momjian.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
2 siblings, 0 replies; 33+ messages in thread
From: Bruce Momjian @ 2011-02-22 01:15 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; 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. +
Attachments:
/rtmp/constraint.diff (text/x-diff, 1.5K) download
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
@ 2011-02-22 14:07 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 33+ messages in thread
From: Bruce Momjian @ 2011-02-22 14:07 UTC (permalink / raw)
To: bruce@momjian.us; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
bruce wrote:
>
> 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.
I found a way to reword the paragraph to be clearer about its purpose:
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.
Applied.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-22 16:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2011-02-22 16:56 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; Peter Eisentraut <peter_e@gmx.net>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
Bruce Momjian <bruce@momjian.us> writes:
> I found a way to reword the paragraph to be clearer about its purpose:
It would still be a good idea to mention which views have the issue.
If you're not certain you know them all, you could use weasel wording
like "Views affected by this issue include foo, bar, and baz".
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-23 04:33 Bruce Momjian <bruce@momjian.us>
parent: Fabien COELHO <coelho@cri.ensmp.fr>
2 siblings, 0 replies; 33+ messages in thread
From: Bruce Momjian @ 2011-02-23 04:33 UTC (permalink / raw)
To: Fabien COELHO <coelho@cri.ensmp.fr>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
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. +
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
@ 2011-02-23 04:35 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 33+ messages in thread
From: Bruce Momjian @ 2011-02-23 04:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Fabien COELHO <coelho@cri.ensmp.fr>; Peter Eisentraut <peter_e@gmx.net>; Alvaro Herrera <alvherre@commandprompt.com>; pgsql-docs
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I found a way to reword the paragraph to be clearer about its purpose:
>
> It would still be a good idea to mention which views have the issue.
> If you're not certain you know them all, you could use weasel wording
> like "Views affected by this issue include foo, bar, and baz".
I have made the change you requested and posted the new text in another
email, just sent. Thanks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 33+ messages in thread
end of thread, other threads:[~2011-02-23 04:35 UTC | newest]
Thread overview: 33+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-31 12:03 issue about information_schema REFERENTIAL_CONSTRAINTS Fabien COELHO <coelho@cri.ensmp.fr>
2010-08-31 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-01 14:22 ` Fabien COELHO <coelho@cri.ensmp.fr>
2010-09-01 19:38 ` Peter Eisentraut <peter_e@gmx.net>
2010-09-02 05:54 ` Fabien COELHO <coelho@cri.ensmp.fr>
2010-09-02 14:40 ` Peter Eisentraut <peter_e@gmx.net>
2010-09-02 18:34 ` Fabien COELHO <coelho@cri.ensmp.fr>
2010-09-02 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 12:08 ` Fabien COELHO <coelho@cri.ensmp.fr>
2010-09-03 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-03 15:14 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-09-03 17:50 ` Fabien COELHO <fabien.coelho@ensmp.fr>
2011-02-19 23:22 ` Bruce Momjian <bruce@momjian.us>
2010-09-03 17:39 ` Fabien COELHO <coelho@cri.ensmp.fr>
2010-09-03 17:45 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-09-03 17:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-05 18:47 ` Peter Eisentraut <peter_e@gmx.net>
2010-09-06 02:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-07 14:49 ` Peter Eisentraut <peter_e@gmx.net>
2010-09-09 08:27 ` Fabien COELHO <coelho@cri.ensmp.fr>
2011-02-20 00:11 ` Bruce Momjian <bruce@momjian.us>
2011-02-20 07:48 ` Fabien COELHO <coelho@cri.ensmp.fr>
2011-02-20 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-21 08:28 ` Fabien COELHO <coelho@cri.ensmp.fr>
2011-02-22 01:15 ` Bruce Momjian <bruce@momjian.us>
2011-02-23 04:33 ` Bruce Momjian <bruce@momjian.us>
2010-09-03 17:53 ` Fabien COELHO <coelho@cri.ensmp.fr>
2010-09-12 00:24 ` Robert Haas <robertmhaas@gmail.com>
2010-09-12 04:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 00:27 ` Robert Haas <robertmhaas@gmail.com>
2011-02-22 14:07 Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS Bruce Momjian <bruce@momjian.us>
2011-02-22 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-23 04:35 ` Bruce Momjian <bruce@momjian.us>
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