Thread: constraints and sql92 information_schema compliance
Hello all. I've got a question with regard to the INFORMATION_SCHEMA
of PostgreSQL, specificially related to constraints. In the SQL92
specification, the DEFINITION_SCHEMA.DOMAIN_CONSTRAINTS (the "imaginary"
base for INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS), has a primary key: CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME
This would leave me to believe that at constraints must have a unique
name within a given schema; however, this seems not to be the case:
# create domain test_one text - constraint test check (value is not null); CREATE DOMAIN
# create domain test_two text - constraint test check (value is not null); CREATE DOMAIN
# select constraint_catalog, constraint_schema, constraint_name - from information_schema.domain_constraints -
where domain_name like 'test_%';
constraint_catalog | constraint_schema | constraint_name
--------------------+-------------------+----------------- cce | public | test cce
| public | test (2 rows)
So it would seem that naming rules for constraints in PostgreSQL
isn't exactly compliant with SQL92. I'm curious what sorts of
constraints are enforced...
Thank you so much,
Clark
Clark, > So it would seem that naming rules for constraints in PostgreSQL > isn't exactly compliant with SQL92. I'm curious what sorts of > constraints are enforced... Correct. Our uniqueness on constraints is: schema_name | table_name | constraint_name We're aware that it's a violation of SQL92, but there's no way for us to change it now without making it very hard for people to upgrade. And, frankly, aside from the very occasional information_schema complaint, nobody seems to care. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Fri, Feb 24, 2006 at 04:23:19PM -0800, Josh Berkus wrote: | Correct. Our uniqueness on constraints is: | schema_name | table_name | constraint_name | | We're aware that it's a violation of SQL92, but there's no way for us to | change it now without making it very hard for people to upgrade. And, | frankly, aside from the very occasional information_schema complaint, | nobody seems to care. Thank you for the quick response; I'm sure you've considered contatinating the internal pg_type name with the pg_constraint name? If so, is there areason this was rejected, since it is a constraint it isn't like you'd reference it in an SQL query (just trying to figure out what I should do in my application). On a related note, this view seems to be filtering by user, I'm curious what the rule is (I'm not that familiar /w PostgreSQL's internal meta-model)? While the textual description of this view "Identify domain constraints in this catalog accessable to a given user." has not changed between SQL-1992 and SQL-2003, the actual critera specified is quite different: In SQL 1992, it seems to show only domains that are in schemas owned by the current user. In SQL 2003, it seems to be more intelligent: showing all constraints that are visible to the current user. I'm curious which rule PostgreSQL's information_schema is using? I think the SQL-2003 rules more properly follow the textual description and are more useful; the SQL-1999 rules are effectively useless in all but trivial cases. Kind Regards, Clark
On Fri, Feb 24, 2006 at 19:40:33 -0500, "Clark C. Evans" <cce@clarkevans.com> wrote: > > While the textual description of this view "Identify domain constraints > in this catalog accessable to a given user." has not changed between > SQL-1992 and SQL-2003, the actual critera specified is quite different: > In SQL 1992, it seems to show only domains that are in schemas owned by > the current user. In SQL 2003, it seems to be more intelligent: showing > all constraints that are visible to the current user. I'm curious which > rule PostgreSQL's information_schema is using? I think the SQL-2003 > rules more properly follow the textual description and are more useful; > the SQL-1999 rules are effectively useless in all but trivial cases. This has been discussed previously in a couple of threads. I believe the desire is to make it work as specified in SQL-2003, but I do not remember whether or not anyone volunteered to do the work to make it happen.
Bruno, > This has been discussed previously in a couple of threads. I believe the > desire is to make it work as specified in SQL-2003, but I do not remember > whether or not anyone volunteered to do the work to make it happen. I believe that the newsysviews follow the SQL03 permissions structure. -- Josh Berkus Aglio Database Solutions San Francisco
On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: | > This has been discussed previously in a couple of threads. I believe the | > desire is to make it work as specified in SQL-2003, but I do not remember | > whether or not anyone volunteered to do the work to make it happen. | | I believe that the newsysviews follow the SQL03 permissions structure. Fantastic! The SQL92 permission structure was braindead. After some time working with the information schema, I have three suggestions: * for foreign-key and check constraints, the default names are $1, $2, etc.; it would be great if they were "upgraded" to use the default names given by primary and unique key constraints: table_uk_1stcol, table_pk -> the problem with $1 is that they arn't unique across tables, and hence won't work /w information_schema nicelyunless you manually name the constraints * when creating a foreign key constraint on two columns, say from A (x, y) to B (x, y), if the unique index on B is (x,y) you can make a foreign key from A->B using (y,x) -> this might seem correct, but it makes it impossible to deterine from the information schema which columns to join on -- and you might infer the wrong relation ie, FROM A JOIN B ON (b.x = a.y and b.y = a.x) * it would be great to add a "warning" if a constraint is not unique within its schema (obviously, making it an erroris a bad idea) I think with these changes no updates to the views are necessary; and compliance with the information_schema is more or less automatic unless someone is ignoring the warnings. Kind Regards, Clark
On Sat, 25 Feb 2006, Clark C. Evans wrote: > On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: > | > This has been discussed previously in a couple of threads. I believe the > | > desire is to make it work as specified in SQL-2003, but I do not remember > | > whether or not anyone volunteered to do the work to make it happen. > | > | I believe that the newsysviews follow the SQL03 permissions structure. > > Fantastic! The SQL92 permission structure was braindead. > > After some time working with the information schema, I have > three suggestions: > > * for foreign-key and check constraints, the default names > are $1, $2, etc.; it would be great if they were "upgraded" > to use the default names given by primary and unique key > constraints: table_uk_1stcol, table_pk Err... what version are you using? I get constraint names like tt_a_fkey from devel, and I thought at least 8.1 does the same. > * when creating a foreign key constraint on two columns, say > from A (x, y) to B (x, y), if the unique index on B is (x,y) > you can make a foreign key from A->B using (y,x) I don't understand which particular case you're complaining about, but as far as I can see, we have to allow that case by the rest of the spec. If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) and B(y), all of A(x,y)->B(x,y), A(y,x)->B(x,y), A(x,y)->B(y,x) and A(y,x)->B(y,x) seem to be allowed by the definition in the constraint section (as only the sets must be equal, with no mention of ordering).
On Sat, 25 Feb 2006, Stephan Szabo wrote: > > On Sat, 25 Feb 2006, Clark C. Evans wrote: > > > On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: > > | > This has been discussed previously in a couple of threads. I believe the > > | > desire is to make it work as specified in SQL-2003, but I do not remember > > | > whether or not anyone volunteered to do the work to make it happen. > > | > > | I believe that the newsysviews follow the SQL03 permissions structure. > > > > Fantastic! The SQL92 permission structure was braindead. > > > > After some time working with the information schema, I have > > three suggestions: > > > > * for foreign-key and check constraints, the default names > > are $1, $2, etc.; it would be great if they were "upgraded" > > to use the default names given by primary and unique key > > constraints: table_uk_1stcol, table_pk > > Err... what version are you using? I get constraint names like tt_a_fkey > from devel, and I thought at least 8.1 does the same. > > > * when creating a foreign key constraint on two columns, say > > from A (x, y) to B (x, y), if the unique index on B is (x,y) > > you can make a foreign key from A->B using (y,x) > > I don't understand which particular case you're complaining about, but as > far as I can see, we have to allow that case by the rest of the spec. If > A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) and > B(y), all of A(x,y)->B(x,y), A(y,x)->B(x,y), A(x,y)->B(y,x) and > A(y,x)->B(y,x) seem to be allowed by the definition in the constraint > section (as only the sets must be equal, with no mention of ordering). The sets in this case being the referenced columns and the unique columns in the unique constraint.
On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: | > > * for foreign-key and check constraints, the default names | > > are $1, $2, etc.; it would be great if they were "upgraded" | > > to use the default names given by primary and unique key | > > constraints: table_uk_1stcol, table_pk | > | > Err... what version are you using? I get constraint names like tt_a_fkey | > from devel, and I thought at least 8.1 does the same. 7.4.8, so it's a bit old -- glad to hear this made it! | > > * when creating a foreign key constraint on two columns, say | > > from A (x, y) to B (x, y), if the unique index on B is (x,y) | > > you can make a foreign key from A->B using (y,x) | > | > I don't understand which particular case you're complaining about, but as | > far as I can see, we have to allow that case by the rest of the spec. To be clear, I'm talking about... CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); CREATE TABLE a (b text, c text); ALTER TABLE a ADD FOREIGN KEY(b, c) REFERENCES x(z, y); For this case, the information schema details: 1. the foreign key constraint as a reference to the primary key constraintand lists the tuple (b,c) 2. the primary key constraint lists the keys (y,z) In particular, the column ordering (z, y) in the reference clause is *lost*. Hence, if you were to blindly reconstruct a join critiera from the information schema, you'd wrongly assume that useful join critiera is: ON (a.b == x.y AND a.c == x.z) when the correct join critiera should be: ON (a.b == x.z AND a.c == x.y) I assert the problem here is that the FOREIGN KEY constraint construction should have *failed* since the *tuple* (z,y) does not infact match any unique key in table x. | > If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) | > and B(y), all of A(x,y)->B(x,y), A(y,x)->B(x,y), A(x,y)->B(y,x) and | > A(y,x)->B(y,x) seem to be allowed by the definition in the constraint | > section (as only the sets must be equal, with no mention of ordering). Ordering of tuples (fields within a row object) are significant in SQL; and hence the two above are not comparable. | The sets in this case being the referenced columns and the unique | columns in the unique constraint. Not sure I get this; sorry about being so obscure in my first email. I hope this one clarifies the problem. Cheers, Clark
On Sat, 2006-02-25 at 16:35 -0500, Clark C. Evans wrote:
> On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote:
> | > > * for foreign-key and check constraints, the default names
> | > > are $1, $2, etc.; it would be great if they were "upgraded"
> | > > to use the default names given by primary and unique key
> | > > constraints: table_uk_1stcol, table_pk
> | >
> | > Err... what version are you using? I get constraint names like tt_a_fkey
> | > from devel, and I thought at least 8.1 does the same.
>
> 7.4.8, so it's a bit old -- glad to hear this made it!
>
> | > > * when creating a foreign key constraint on two columns, say
> | > > from A (x, y) to B (x, y), if the unique index on B is (x,y)
> | > > you can make a foreign key from A->B using (y,x)
> | >
> | > I don't understand which particular case you're complaining about, but as
> | > far as I can see, we have to allow that case by the rest of the spec.
>
> To be clear, I'm talking about...
>
> CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
> CREATE TABLE a (b text, c text);
> ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
>
> For this case, the information schema details:
>
> 1. the foreign key constraint as a reference to the
> primary key constraint and lists the tuple (b,c)
>
> 2. the primary key constraint lists the keys (y,z)
I'm afraid I don't follow what the issue is.
Can out point it out in the below psql output?
k=# CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
for table "x"
CREATE TABLE
k=# CREATE TABLE a (b text, c text);
CREATE TABLE
k=# ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
ALTER TABLE
k=# \d x Table "public.x"Column | Type | Modifiers
--------+------+-----------y | text | not nullz | text | not null
Indexes: "x_pkey" PRIMARY KEY, btree (y, z)
k=# \d a Table "public.a"Column | Type | Modifiers
--------+------+-----------b | text |c | text |
Foreign-key constraints: "a_b_fkey" FOREIGN KEY (b, c) REFERENCES x(z, y)
k=# insert into x values ('foo', 'bar');
INSERT 0 1
k=# insert into a values ('foo', 'bar');
ERROR: insert or update on table "a" violates foreign key constraint
"a_b_fkey"
DETAIL: Key (b,c)=(foo,bar) is not present in table "x".
k=# insert into a values ('bar', 'foo');
INSERT 0 1
--
On Sat, 25 Feb 2006, Clark C. Evans wrote: > On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: > | > > * for foreign-key and check constraints, the default names > | > > are $1, $2, etc.; it would be great if they were "upgraded" > | > > to use the default names given by primary and unique key > | > > constraints: table_uk_1stcol, table_pk > | > > | > Err... what version are you using? I get constraint names like tt_a_fkey > | > from devel, and I thought at least 8.1 does the same. > > 7.4.8, so it's a bit old -- glad to hear this made it! > > | > > * when creating a foreign key constraint on two columns, say > | > > from A (x, y) to B (x, y), if the unique index on B is (x,y) > | > > you can make a foreign key from A->B using (y,x) > | > > | > I don't understand which particular case you're complaining about, but as > | > far as I can see, we have to allow that case by the rest of the spec. > > To be clear, I'm talking about... > > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); > CREATE TABLE a (b text, c text); > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); > > For this case, the information schema details: > > 1. the foreign key constraint as a reference to the > primary key constraint and lists the tuple (b,c) > > 2. the primary key constraint lists the keys (y,z) > > In particular, the column ordering (z, y) in the reference > clause is *lost*. Hence, if you were to blindly reconstruct > a join critiera from the information schema, you'd wrongly > assume that useful join critiera is: > > ON (a.b == x.y AND a.c == x.z) > > when the correct join critiera should be: > > ON (a.b == x.z AND a.c == x.y) > > I assert the problem here is that the FOREIGN KEY constraint > construction should have *failed* since the *tuple* (z,y) > does not infact match any unique key in table x. I disagree because the spec doesn't say that the columns must be equal or the list of columns must be the equal but that the set of columns must be equal. And in the definitions section, set is defined as an unordered collection of distinct objects. > | > If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) > | > and B(y), all of A(x,y)->B(x,y), A(y,x)->B(x,y), A(x,y)->B(y,x) and > | > A(y,x)->B(y,x) seem to be allowed by the definition in the constraint > | > section (as only the sets must be equal, with no mention of ordering). > > Ordering of tuples (fields within a row object) are significant > in SQL; and hence the two above are not comparable. You misunderstand what comparable means in the above. Comparable is the constraint on the column types (for example numeric types are comparable to other numeric types).
On Sat, 25 Feb 2006, Stephan Szabo wrote: > > On Sat, 25 Feb 2006, Clark C. Evans wrote: > > > On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: > > | > > * for foreign-key and check constraints, the default names > > | > > are $1, $2, etc.; it would be great if they were "upgraded" > > | > > to use the default names given by primary and unique key > > | > > constraints: table_uk_1stcol, table_pk > > | > > > | > Err... what version are you using? I get constraint names like tt_a_fkey > > | > from devel, and I thought at least 8.1 does the same. > > > > 7.4.8, so it's a bit old -- glad to hear this made it! > > > > | > > * when creating a foreign key constraint on two columns, say > > | > > from A (x, y) to B (x, y), if the unique index on B is (x,y) > > | > > you can make a foreign key from A->B using (y,x) > > | > > > | > I don't understand which particular case you're complaining about, but as > > | > far as I can see, we have to allow that case by the rest of the spec. > > > > To be clear, I'm talking about... > > > > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); > > CREATE TABLE a (b text, c text); > > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); > > > > For this case, the information schema details: > > > > 1. the foreign key constraint as a reference to the > > primary key constraint and lists the tuple (b,c) > > > > 2. the primary key constraint lists the keys (y,z) > > > > In particular, the column ordering (z, y) in the reference > > clause is *lost*. Hence, if you were to blindly reconstruct > > a join critiera from the information schema, you'd wrongly > > assume that useful join critiera is: > > > > ON (a.b == x.y AND a.c == x.z) > > > > when the correct join critiera should be: > > > > ON (a.b == x.z AND a.c == x.y) > > > > I assert the problem here is that the FOREIGN KEY constraint > > construction should have *failed* since the *tuple* (z,y) > > does not infact match any unique key in table x. > > I disagree because the spec doesn't say that the columns must be equal > or the list of columns must be the equal but that the set of columns must > be equal. And in the definitions section, set is defined as an unordered > collection of distinct objects. Okay, I'll take that back for SQL2003. They must have realized that this was broken with information schema and changed it. That's an interesting incompatibility with old versions, but it's easy to implement.
On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: > Bruno, > > > This has been discussed previously in a couple of threads. I believe the > > desire is to make it work as specified in SQL-2003, but I do not remember > > whether or not anyone volunteered to do the work to make it happen. > > I believe that the newsysviews follow the SQL03 permissions structure. Does SQL03 specify a different name for info schema? Should we morph newsysviews to that? Of course this still leaves the issue of how to deal with PostgreSQL-specific stuff that isn't in infoschema, since we probably don't want to be adding extra stuff there. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Guys, So, based on this discussion, I'd like to consider taking a second stab at newsysviews: 1) Implement SQL03 changes into the information_schema, using some of the code from newsysviews; 2) Modify the newsysviews to be extensions of the information_schema views:e.g. information_schema.tables would have theSQL03 information, andinformation_schema.tables_pg would have pg-specific stuff like table size and last analyzed date. I think that this should answer the objections expressed to the newsysviews and provide a path towards integrating them into the main code. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
| Can out point it out in the below psql output?
|
| k=# CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
| for table "x"
| CREATE TABLE
| k=# CREATE TABLE a (b text, c text);
| CREATE TABLE
| k=# ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
| ALTER TABLE
-> this alter _should_ fail, since there isn't a canidate key on the table x matching (z, y)
| "x_pkey" PRIMARY KEY, btree (y, z)
| "a_b_fkey" FOREIGN KEY (b, c) REFERENCES x(z, y)
The problem is that PostgreSQL is maintaining information that is/should
not be available to an SQL processor: the ordering of the _referenced_
columns. That a_b_fkey happens to reference (z, y) is not available in
the SQL INFORMATION_SCHEMA, and thus should not be used to interpret
standard SQL statements affected by the foreign key constraint.
| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table "a" violates foreign key constraint
| "a_b_fkey"
| DETAIL: Key (b,c)=(foo,bar) is not present in table "x".
Assuming that you _could_ create the FOREIGN KEY reference above, if you
are strictly using the meta-data available in the information_schema,
this insert should succeed
| k=# insert into a values ('bar', 'foo');
| INSERT 0 1
and this insert should fail. The opposite happens beacuse PostgreSQL
is storing _more_ information than what is specified and has over
interpreted the meaning of the reference clause.
On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
| > On Sat, 25 Feb 2006, Clark C. Evans wrote:
| > >
| > > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| > > CREATE TABLE a (b text, c text);
| > > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
| > >
| > > I assert the problem here is that the FOREIGN KEY constraint
| > > construction should have *failed* since the *tuple* (z,y)
| > > does not infact match any unique key in table x.
| >
| > I disagree because the spec doesn't say that the columns must be equal
| > or the list of columns must be the equal but that the set of columns must
| > be equal. And in the definitions section, set is defined as an unordered
| > collection of distinct objects.
Let's use the example Rod gave us above. If the comparison for foreign
key constraints should be done as an unorderd set, then why does the
following fail?
| k=# insert into x values ('foo', 'bar'); | INSERT 0 1 | k=# insert into a values ('foo', 'bar'); | ERROR: insert or
updateon table "a" violates foreign key constraint | "a_b_fkey" | DETAIL: Key (b,c)=(foo,bar) is not present in table
"x".
While the SQL1992 specification may be horribly incorrect; the current
behavior is not compliant with it... so this isn't a great defense. If
PostgreSQL didn't store the order of the columns referenced, it couldn't
provide the error above (which makes sense, given the extension).
| Okay, I'll take that back for SQL2003. They must have realized that this
| was broken with information schema and changed it.
Ok.
| That's an interesting incompatibility with old versions,
| but it's easy to implement.
This would be great; it would reduce the chances of an external program
generating SQL from making incorrect joins and causing a very strange
behavior and incorrect results.
Cheers,
Clark
Stephen, So, a quick re-cap of the questions/concerns I had: * Making the default constraint names include the table -> This was implemented in 8.x, thank you! * Forbidding the creation of a foreign key constraint where the column list for the referenced table doesn't *exactly* match a canidate key on that table. -> I think you've agreed to something like this, or am I mis-understanding? * Issue a warning when creating a constraint who's name is not unique within its (the constraint's) schema. -> This request seems to have gotten lost in the vigorous discussion ;) Kind Regards, Clark
On Sun, 26 Feb 2006, Clark C. Evans wrote: > Stephen, > > So, a quick re-cap of the questions/concerns I had: > > * Making the default constraint names include the table > > -> This was implemented in 8.x, thank you! > > * Forbidding the creation of a foreign key constraint where > the column list for the referenced table doesn't *exactly* > match a canidate key on that table. > > -> I think you've agreed to something like this, or am > I mis-understanding? Well, SQL03 requires it to match exactly (I haven't checked 99). SQL92 explicitly requires us to support not matching exactly and we can't really remove it for some amount of time due to compatibility. About the best we're likely to be able to do is change pg_dump to dump it in the 03 order and possibly give an optional way to turn on an exact check (default off) for the next version, probably changing the default 1 or 2 versions after that. Personally, I disagree with the 03 requirement and think that it's more an example of them misdesigning the information schema, but we should probably move in that direction for compatibility with more recent versions of spec. > * Issue a warning when creating a constraint who's name is > not unique within its (the constraint's) schema. > > -> This request seems to have gotten lost in the > vigorous discussion ;) I don't have a problem with it (once, I argued for following the spec constraint on this way back when), however I think this was proposed and rejected before as excess noise. You might want to look back through the archives.
On Sun, 26 Feb 2006, Clark C. Evans wrote:
> On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
> On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
> | > On Sat, 25 Feb 2006, Clark C. Evans wrote:
> | > >
> | > > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
> | > > CREATE TABLE a (b text, c text);
> | > > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
> | > >
> | > > I assert the problem here is that the FOREIGN KEY constraint
> | > > construction should have *failed* since the *tuple* (z,y)
> | > > does not infact match any unique key in table x.
> | >
> | > I disagree because the spec doesn't say that the columns must be equal
> | > or the list of columns must be the equal but that the set of columns must
> | > be equal. And in the definitions section, set is defined as an unordered
> | > collection of distinct objects.
>
> Let's use the example Rod gave us above. If the comparison for foreign
> key constraints should be done as an unorderd set, then why does the
> following fail?
>
> | k=# insert into x values ('foo', 'bar');
> | INSERT 0 1
> | k=# insert into a values ('foo', 'bar');
> | ERROR: insert or update on table "a" violates foreign key constraint
> | "a_b_fkey"
> | DETAIL: Key (b,c)=(foo,bar) is not present in table "x".
>
> While the SQL1992 specification may be horribly incorrect; the current
> behavior is not compliant with it... so this isn't a great defense. If
> PostgreSQL didn't store the order of the columns referenced, it couldn't
> provide the error above (which makes sense, given the extension).
No, because you're misunderstanding what the SQL92 spec says. The spec
says that the comparison between the (z,y) in the references and the key
definition (y,z) is unordered, not that the comparisons between (b,c) and
(z,y) are unordered.
Josh Berkus <josh@agliodbs.com> writes:
> 2) Modify the newsysviews to be extensions of the information_schema views:
> e.g. information_schema.tables would have the SQL03 information, and
> information_schema.tables_pg would have pg-specific stuff like table size
> and last analyzed date.
No way. The entire point of information_schema is that it is standard;
adding non-spec things to it renders it no better than direct access
to the PG catalogs.
This thread is fairly interesting since we appear to be watching the SQL
committee allowing a brain-dead choice in the initial information_schema
design to force a non-backwards-compatible dumbing-down of the main spec.
Which they would surely never have done if it weren't for their self-
imposed rules about never changing information_schema (rules that they
appear to follow only erratically anyway ;-))
I'm disinclined to risk being put in a similar bind ... so even if
we were at liberty to put PG-specific stuff into information_schema,
I wouldn't do it.
regards, tom lane
On Sat, Feb 25, 2006 at 10:52:48PM -0800, Stephan Szabo wrote: | > * Forbidding the creation of a foreign key constraint where | > the column list for the referenced table doesn't *exactly* | > match a canidate key on that table. | | About the best we're likely to be able to do is change pg_dump to dump | it in the 03 order and possibly give an optional way to turn on an exact | check (default off) for the next version, probably changing the default | 1 or 2 versions after that. Excellent. | > * Issue a warning when creating a constraint who's name is | > not unique within its (the constraint's) schema. | | I don't have a problem with it (once, I argued for following the spec | constraint on this way back when), however I think this was proposed and | rejected before as excess noise. You might want to look back through the | archives. I think the problem /w the noise was that default trigger names were not automatically prefixed with the table name. I'd like to see this warning; perhaps in the next release, the ``dump`` module can rename constraints like $1 and $2 to include the table name? Given that both of these issues consist of first changing the dumper and making an optional warning (at first) and then turning it into an error way down the line, could they be considered part of the same ticket? On Sat, Feb 25, 2006 at 10:52:48PM -0800, Stephan Szabo wrote: | Personally, I disagree with the 03 requirement and think that it's more an | example of them misdesigning the information schema, but we should | probably move in that direction for compatibility with more recent | versions of spec. Agreed; there is no reason why the information_schema could not have been improved to add an additional view to provide this information. On Sun, Feb 26, 2006 at 12:57:13PM -0500, Tom Lane wrote: | This thread is fairly interesting since we appear to be watching the SQL | committee allowing a brain-dead choice in the initial information_schema | design to force a non-backwards-compatible dumbing-down of the main spec. I'm glad that this thread has not been an undue burden; I'm greatful for your help (as I'm writing an application that is attempting to follow the specification as closely as possible). Best, Clark
Clark C. Evans wrote: > >| > * Issue a warning when creating a constraint who's name is >| > not unique within its (the constraint's) schema. >| >| I don't have a problem with it (once, I argued for following the spec >| constraint on this way back when), however I think this was proposed and >| rejected before as excess noise. You might want to look back through the >| archives. > >I think the problem /w the noise was that default trigger names were >not automatically prefixed with the table name. I'd like to see this >warning; perhaps in the next release, the ``dump`` module can rename >constraints like $1 and $2 to include the table name? > >Given that both of these issues consist of first changing the dumper and >making an optional warning (at first) and then turning it into an error >way down the line, could they be considered part of the same ticket? > > > > Ticket? :-) You might like to read up on how our development process works. See http://www.postgresql.org/docs/faqs.FAQ_DEV.html Among other things, note that we don't really have a ticket system. More substantively, I think making an option to have pg_dump prepend the table name to autogenned $n type constraint names is not a bad idea. cheers andrew
On Sun, 26 Feb 2006, Clark C. Evans wrote: > On Sat, Feb 25, 2006 at 10:52:48PM -0800, Stephan Szabo wrote: > | > * Issue a warning when creating a constraint who's name is > | > not unique within its (the constraint's) schema. > | > | I don't have a problem with it (once, I argued for following the spec > | constraint on this way back when), however I think this was proposed and > | rejected before as excess noise. You might want to look back through the > | archives. > > I think the problem /w the noise was that default trigger names were > not automatically prefixed with the table name. I'd like to see this > warning; perhaps in the next release, the ``dump`` module can rename > constraints like $1 and $2 to include the table name? > > Given that both of these issues consist of first changing the dumper and > making an optional warning (at first) and then turning it into an error > way down the line, could they be considered part of the same ticket? Well, I am worried about this change idea. That's potentially going to break applications, ALTER ... DROP CONSTRAINT and SET CONSTRAINTS both use the constraint names. I think maybe something like adddepend in contrib was for 7.2 or 7.3 might be better since that makes it something that's not automatically done but relatively easily done.
Tom, > No way. The entire point of information_schema is that it is standard; > adding non-spec things to it renders it no better than direct access > to the PG catalogs. Hmmm ... so, per you, we can't add extra views covering non-spec objects to the information_schema (like aggregates) because we can't modify it in any way. But per Peter we can't add new views to the pg_catalog because we want people to use information_schema. I sense a catch-22 here. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> No way. The entire point of information_schema is that it is standard;
>> adding non-spec things to it renders it no better than direct access
>> to the PG catalogs.
> Hmmm ... so, per you, we can't add extra views covering non-spec objects to
> the information_schema (like aggregates) because we can't modify it in any
> way. But per Peter we can't add new views to the pg_catalog because we
> want people to use information_schema. I sense a catch-22 here.
I doubt Peter really meant that we can't add any new views; in
particular, for information that is not available from the standard
information_schema it's certainly silly to claim that people should go
to information_schema for it. I do see his point that we shouldn't
unnecessarily duplicate functionality that's available in a standardized
view.
I do have doubts about adding any large number of add-on views to
pg_catalog, because of the privileged place of that schema in search
paths. It'd be better to put them in a separate schema ("pg_info"
maybe?) where they'd pose less risk of conflicts with user-defined names.
Does newsysviews already do this?
regards, tom lane
On Mon, Feb 27, 2006 at 11:39:30AM -0500, Tom Lane wrote:
| Josh Berkus <josh@agliodbs.com> writes:
| >> No way. The entire point of information_schema is that it is standard;
| >> adding non-spec things to it renders it no better than direct access
| >> to the PG catalogs.
| >
| > Hmmm ... so, per you, we can't add extra views covering non-spec
| > objects to the information_schema (like aggregates) because we
| > can't modify it in any way. But per Peter we can't add new
| > views to the pg_catalog because we want people to use
| > information_schema. I sense a catch-22 here.
| I doubt Peter really meant that we can't add any new views; in
| particular, for information that is not available from the standard
| information_schema it's certainly silly to claim that people should go
| to information_schema for it. I do see his point that we shouldn't
| unnecessarily duplicate functionality that's available in a standardized
| view.
If my opinion is worth anything here, nothing should go in the
information_schema unless is is specified in one of the SQL1992,
SQL1999, or SQL2003 specifications. According to my objectives,
if it isn't in the information_schema, I should not be using it.
I've been using information_schema reflectively, and would have
been confused to see anything in there that wasn't in the specs.
| I do have doubts about adding any large number of add-on views to
| pg_catalog, because of the privileged place of that schema in search
| paths. It'd be better to put them in a separate schema ("pg_info"
| maybe?) where they'd pose less risk of conflicts with user-defined names.
| Does newsysviews already do this?
A separate pg_info probably would not hurt, I suppose.
Best,
Clark
On 2006-02-27, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I do have doubts about adding any large number of add-on views to
> pg_catalog, because of the privileged place of that schema in search
> paths. It'd be better to put them in a separate schema ("pg_info"
> maybe?) where they'd pose less risk of conflicts with user-defined names.
> Does newsysviews already do this?
The current version in pgfoundry CVS uses "pg_sysviews" as the schema name.
If you have any better suggestions for the name, or any other aspect of the
project, then we're all ears.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
Tom,
>>Hmmm ... so, per you, we can't add extra views covering non-spec objects to
>>the information_schema (like aggregates) because we can't modify it in any
>>way. But per Peter we can't add new views to the pg_catalog because we
>>want people to use information_schema. I sense a catch-22 here.
>
>
> I doubt Peter really meant that we can't add any new views; in
> particular, for information that is not available from the standard
> information_schema it's certainly silly to claim that people should go
> to information_schema for it. I do see his point that we shouldn't
> unnecessarily duplicate functionality that's available in a standardized
> view.
Yes, I agree with him on that. However, there's a certain amount of
confusion inspired by the organization that: "If you want to look up the
table's columns go to information_schmea, if you want the table *size*
go to sysviews." But maybe that's unavoidable. Or maybe we could link
the information_schema views into pg_sysviews?
We'd earlier thought that the permissions stuff in information_schema
made is untenable for any real database catalog use. If 03 has fixed
that, though, maybe this can work. AndrewSN?
> I do have doubts about adding any large number of add-on views to
> pg_catalog, because of the privileged place of that schema in search
> paths. It'd be better to put them in a separate schema ("pg_info"
> maybe?) where they'd pose less risk of conflicts with user-defined names.
> Does newsysviews already do this?
Yes, in our original conception it was the schema pg_sysviews.
--Josh Berkus
Josh Berkus <josh@agliodbs.com> writes:
> Yes, I agree with him on that. However, there's a certain amount of
> confusion inspired by the organization that: "If you want to look up the
> table's columns go to information_schmea, if you want the table *size*
> go to sysviews." But maybe that's unavoidable. Or maybe we could link
> the information_schema views into pg_sysviews?
We could, but I'd argue that this makes sense only if the added
PG-specific stuff looks like a seamless extension of the standard
definitions. If there are obvious differences in naming style, table
layout, etc, I'd expect such a setup to look more like a hodgepodge
than a good idea.
regards, tom lane
Andrew - Supernews wrote:
>On 2006-02-27, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
>>I do have doubts about adding any large number of add-on views to
>>pg_catalog, because of the privileged place of that schema in search
>>paths. It'd be better to put them in a separate schema ("pg_info"
>>maybe?) where they'd pose less risk of conflicts with user-defined names.
>>Does newsysviews already do this?
>>
>>
>
>The current version in pgfoundry CVS uses "pg_sysviews" as the schema name.
>If you have any better suggestions for the name, or any other aspect of the
>project, then we're all ears.
>
>
>
How fine-grained do we want to get on namespaces? I'd be slightly more
inclined to have pg_info or maybe pg_utils as a place to stash not only
extra system views but other utility stuff that we want to ship but is
essentially droppable.
cheers
andrew
Andrew, > How fine-grained do we want to get on namespaces? I'd be slightly more > inclined to have pg_info or maybe pg_utils as a place to stash not only > extra system views but other utility stuff that we want to ship but is > essentially droppable. AFAIK, none of the contributors to newsysviews has any attachment to any particular name. I'd personally prefer to go with your suggestion of a more generic schema name. --Josh
On Mon, Feb 27, 2006 at 11:24:05AM -0800, Josh Berkus wrote: > Andrew, > > >How fine-grained do we want to get on namespaces? I'd be slightly more > >inclined to have pg_info or maybe pg_utils as a place to stash not only > >extra system views but other utility stuff that we want to ship but is > >essentially droppable. > > AFAIK, none of the contributors to newsysviews has any attachment to any > particular name. I'd personally prefer to go with your suggestion of a > more generic schema name. Agreed. pg_info or pg_util sound good. Since there's a fairly large number of views I don't know if it's worth having both pg_info and pg_util. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Mon, Feb 27, 2006 at 02:17:03PM -0500, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > > Yes, I agree with him on that. However, there's a certain amount of > > confusion inspired by the organization that: "If you want to look up the > > table's columns go to information_schmea, if you want the table *size* > > go to sysviews." But maybe that's unavoidable. Or maybe we could link > > the information_schema views into pg_sysviews? > > We could, but I'd argue that this makes sense only if the added > PG-specific stuff looks like a seamless extension of the standard > definitions. If there are obvious differences in naming style, table > layout, etc, I'd expect such a setup to look more like a hodgepodge > than a good idea. Agreed. Currently, newsysviews uses a completely different naming scheme, one intended to be as self-explanitory as possible and not using things like hard to remember abbreviations crammed together without underscores (no offense to the catalogs; I'm sure backend developers don't want to type out full names all the time, but it does make it substantially harder to grok for newer users). Personally, I find info_schema somewhat hard to use as well, largely because it's meant to be the same for any database, so there's terminology mapping you have to do. Plus, newsysviews has some things that make it easier on people who are doing ad-hoc queries, such as _all as well as _user versions of most things. So I'd rather keep the naming we use in newsysviews, but I can certainly see where a version of info_schema that's been extended could be of use. Perhaps there's reason to have both. Hopefully an extended info_schema would be able to rely mostly on what's already in info_schema, so there would be a minimal amount of work required on it. For that matter, if newsysviews became part of the standard install, it could well be easier to build info_schema on it instead of the base tables. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
[Resurrecting an old thread] On Sat, 25 Feb 2006, Clark C. Evans wrote: > On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: > | > > * for foreign-key and check constraints, the default names > | > > are $1, $2, etc.; it would be great if they were "upgraded" > | > > to use the default names given by primary and unique key > | > > constraints: table_uk_1stcol, table_pk > | > > | > Err... what version are you using? I get constraint names like tt_a_fkey > | > from devel, and I thought at least 8.1 does the same. > > 7.4.8, so it's a bit old -- glad to hear this made it! > > | > > * when creating a foreign key constraint on two columns, say > | > > from A (x, y) to B (x, y), if the unique index on B is (x,y) > | > > you can make a foreign key from A->B using (y,x) > | > > | > I don't understand which particular case you're complaining about, but as > | > far as I can see, we have to allow that case by the rest of the spec. > > To be clear, I'm talking about... > > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); > CREATE TABLE a (b text, c text); > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); > > For this case, the information schema details: > > 1. the foreign key constraint as a reference to the > primary key constraint and lists the tuple (b,c) > > 2. the primary key constraint lists the keys (y,z) > > In particular, the column ordering (z, y) in the reference > clause is *lost*. Hence, if you were to blindly reconstruct > a join critiera from the information schema, you'd wrongly > assume that useful join critiera is: > > ON (a.b == x.y AND a.c == x.z) > > when the correct join critiera should be: > > ON (a.b == x.z AND a.c == x.y) > > I assert the problem here is that the FOREIGN KEY constraint > construction should have *failed* since the *tuple* (z,y) > does not infact match any unique key in table x. Looking at this more, I'm not sure that making it match the unique key exactly helps information_schema.constraint_column_usage at least. Given the following: create table ta(a int, b int, primary key(a,b)); create table tb(a int, b int, foreign key (a,b) references ta); create table tc(a int, b int, foreign key (b,a) references ta); I don't see how you can differentiate the foreign keys in the last two without a position column, which doesn't seem to be in at least our current view (although I haven't checked 2003 to see if they changed it). Both of those should be valid, although the second is wierd.
On Tue, Mar 14, 2006 at 08:14:12PM -0800, Stephan Szabo wrote: | > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); | > CREATE TABLE a (b text, c text); | > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); ... | > I assert the problem here is that the FOREIGN KEY constraint | > construction should have *failed* since the *tuple* (z,y) | > does not infact match any unique key in table x. | | Looking at this more, I'm not sure that making it match the unique key | exactly helps information_schema.constraint_column_usage at least. My problem is that the column order can be provided in the reference clause in a way that does *not* match a canidate key: in the example above, there isn't a primary key nor a unique key index on (z,y). | create table ta(a int, b int, primary key(a,b)); | create table tb(a int, b int, foreign key (a,b) references ta); | create table tc(a int, b int, foreign key (b,a) references ta); When <reference column list> is omitted, it implies that the primary key of the referenced table is used; hence, these are equivalent to: create table tb(a int, b int, foreign key (a,b) references ta (a,b)); create table tc(a int, b int, foreign key (b,a) references ta (a,b)); | I don't see how you can differentiate the foreign keys in the last two | without a position column, which doesn't seem to be in at least our | current view (although I haven't checked 2003 to see if they changed it). | Both of those should be valid, although the second is wierd. There isn't a problem with the examples you provided, although the resulting join isn't what the user intended. I think the ability to omit the <reference column list> is a bad idea; but alias, it is quite different from the problem I'm reporting. Very Best, Clark
On Wed, 15 Mar 2006, Clark C. Evans wrote: > On Tue, Mar 14, 2006 at 08:14:12PM -0800, Stephan Szabo wrote: > | > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); > | > CREATE TABLE a (b text, c text); > | > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); > ... > | > I assert the problem here is that the FOREIGN KEY constraint > | > construction should have *failed* since the *tuple* (z,y) > | > does not infact match any unique key in table x. > | > | Looking at this more, I'm not sure that making it match the unique key > | exactly helps information_schema.constraint_column_usage at least. > > My problem is that the column order can be provided in the reference > clause in a way that does *not* match a canidate key: in the example > above, there isn't a primary key nor a unique key index on (z,y). I think that's only true if z1=z2 and y1=y2 could have a different answer than y1=y2 and z1=z2. Otherwise, you're effectively definting both uniquenesses in a single constraint. > | I don't see how you can differentiate the foreign keys in the last two > | without a position column, which doesn't seem to be in at least our > | current view (although I haven't checked 2003 to see if they changed it). > | Both of those should be valid, although the second is wierd. > > There isn't a problem with the examples you provided, although the > resulting join isn't what the user intended. I think the ability Actually, it's precisely what I intended. In the second case the labels happen to represent the other value. > to omit the <reference column list> is a bad idea; but alias, it > is quite different from the problem I'm reporting. The point is that because rows in a table don't have order (unless information_schema has special rules) the two constraints above seem to look the same to me in their representation in information_schema.constraint_column_usage. If that's true then forcing the referenced columns to match exactly doesn't actually fix the problem with the representation in infomration schema. The same ambiguity exists. We need to offer this for spec complience reasons, but I don't think it actually fixes the problem you would have with information_schema.
On Tue, 14 Mar 2006, Stephan Szabo wrote: > We need to offer this for spec complience reasons, but I don't think it > actually fixes the problem you would have with information_schema. Which of course is wrong, as i figured out when the discussion came up the first time and forgot when I came to it while trying to work something out while doing it. :( Not enough sleep aparently.
On Tue, Mar 14, 2006 at 10:01:16PM -0800, Stephan Szabo wrote:
| The point is that because rows in a table don't have order (unless
| information_schema has special rules) the two constraints above seem to
| look the same to me in their representation in
| information_schema.constraint_column_usage. If that's true then forcing
| the referenced columns to match exactly doesn't actually fix the problem
| with the representation in infomration schema. The same ambiguity exists.
Actually, there is no ambiguity; it's just that constraint_column_usage
view is absolutely useless. You want to be using key_column_usage.
--
-- Create the test tables, taking particular care to name the
-- constraints so that they are unique within the schema.
--
create table ta(a int, b int);
alter table ta add constraint ta_pk primary key (a,b);
create table tb(a int, b int);
alter table tb add constraint tb_ta_fk foreign key (a,b) references ta;
create table tc(a int, b int);
alter table tc add constraint tc_ta_fk foreign key (b,a) references ta;
--
-- Return the pairing between the foreign-key column, and
-- the canidate-key columns they refer to.
--
SELECT fk.table_name AS fk_table, fk.column_name AS fk_column, uk.table_name AS uk_table, uk.column_name AS
uk_columnFROM ( SELECT c.constraint_schema, c.constraint_name, c.table_schema, c.table_name,
u.column_name,u.ordinal_position FROM information_schema.table_constraints c JOIN
information_schema.key_column_usageu ON ( u.constraint_schema = c.constraint_schema AND
u.constraint_name= c.constraint_name AND u.table_schema = c.table_schema AND u.table_name =
c.table_name) WHERE c.constraint_type in ('UNIQUE', 'PRIMARY KEY') ) AS uk, ( SELECT c.unique_constraint_schema,
c.unique_constraint_name, u.table_schema, u.table_name, c.constraint_schema, c.constraint_name,
u.column_name, u.ordinal_position FROM information_schema.referential_constraints c JOIN
information_schema.key_column_usageu ON ( c.constraint_schema = u.constraint_schema AND
c.constraint_name= u.constraint_name ) ) AS fk WHERE uk.constraint_schema = fk.unique_constraint_schema AND
uk.constraint_name= fk.unique_constraint_name AND uk.ordinal_position = fk.ordinal_position ORDER BY fk.table_name,
fk.ordinal_position;
I hope this helps! (and that it's even remotely correct)
Best,
Clark
On Wed, 15 Mar 2006, Clark C. Evans wrote: > On Tue, Mar 14, 2006 at 10:01:16PM -0800, Stephan Szabo wrote: > | The point is that because rows in a table don't have order (unless > | information_schema has special rules) the two constraints above seem to > | look the same to me in their representation in > | information_schema.constraint_column_usage. If that's true then forcing > | the referenced columns to match exactly doesn't actually fix the problem > | with the representation in infomration schema. The same ambiguity exists. > > Actually, there is no ambiguity; it's just that constraint_column_usage > view is absolutely useless. You want to be using key_column_usage. Yeah, I remembered afterwards, so I had to send that later message. This came up because I was trying to figure out at what point (if ever) reordering should happen when the variable is set to allow references to the unique constraint in other orders (see below), and while looking at that, I poked at information_schema to see if I could come up with a good reason to do one way or another and queried the wrong one and then worried that I wasn't going to actually be solving the fundamental problem. --- The main options seem to be:When we're allowing other order access, immediately reorder the constraint information to match the primary key order. This helps out with IS since the loaded constraint should display properly, but theoretically could change the visual representation after load for people who don't care about this option.Change the representation unconditionally on dump. Basically reorder the constraint at dump time to always generate a dump in SQL03 order. This has the same downside as the above except only after another dump/restore.Change the representation on dump only if the flagis set (probably exporting this as an option to pg_dump as well). This could be a little more difficult to use, but pretty much causes the user to drive the choice.
On Tue, Mar 14, 2006 at 11:11:29PM -0800, Stephan Szabo wrote: | When we're allowing other order access, immediately reorder the | constraint information to match the primary key order. Let me try to parrot. In PostgreSQL, the pairing information between the foreign-key and unique-key constraint is available; even though it isn't represented in the information_schema. Hence, this option re-orders the foreign-key columns to match the corresponding canidate key constraint (unique _or_ foreign key). If so, I like it. | This helps out | with IS since the loaded constraint should display properly, but | theoretically could change the visual representation after load for people | who don't care about this option. I doubt that the actual ordering of the columns in the foreign key constraint matters to people; so I don't see a downside with this option other than the perhaps unexpected difference. | Change the representation unconditionally on dump. Basically reorder the | constraint at dump time to always generate a dump in SQL03 order. This has | the same downside as the above except only after another dump/restore. You could do both? | Change the representation on dump only if the flag is set (probably | exporting this as an option to pg_dump as well). This could be a little | more difficult to use, but pretty much causes the user to drive the | choice. Hmm. I just cannot think of a reason why someone would even care that the columns in their foreign-key constraint create get re-orderd as long as the behavior is the same. Best, Clark
On Wed, 15 Mar 2006, Clark C. Evans wrote: > On Tue, Mar 14, 2006 at 11:11:29PM -0800, Stephan Szabo wrote: > | When we're allowing other order access, immediately reorder the > | constraint information to match the primary key order. > > Let me try to parrot. In PostgreSQL, the pairing information between > the foreign-key and unique-key constraint is available; even though it > isn't represented in the information_schema. Hence, this option > re-orders the foreign-key columns to match the corresponding canidate > key constraint (unique _or_ foreign key). If so, I like it. Right, at create time (a,b) references t(d,c) where the key is actually t(c,d) would get treated as if the user had actually written (b,a) references t(c,d) if it's set up to accept that at all. > | This helps out > | with IS since the loaded constraint should display properly, but > | theoretically could change the visual representation after load for people > | who don't care about this option. > > I doubt that the actual ordering of the columns in the foreign > key constraint matters to people; so I don't see a downside with > this option other than the perhaps unexpected difference. The main case I could see is if an app thinks it knows what the key should look like (and looks at the catalogs or psql output or pg_dump output or the output of a function that gives back the key information in an api potentially) and now sees the key disappear and/or a new key appear after the upgrade. This option seems like the best apart from that one sticking point. > | Change the representation unconditionally on dump. Basically reorder the > | constraint at dump time to always generate a dump in SQL03 order. This has > | the same downside as the above except only after another dump/restore. > > You could do both? Well, if you do the first, you're effectively doing this one as well, since it'll always dump in SQL03 order from that point forward. It looks like we can't really meaningfully change the behavior against old versions, so this would only affect dumps of 8.2 servers or later in any case.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> The main options seem to be:
> When we're allowing other order access, immediately reorder the
> constraint information to match the primary key order. This helps out
> with IS since the loaded constraint should display properly, but
> theoretically could change the visual representation after load for people
> who don't care about this option.
> Change the representation unconditionally on dump. Basically reorder the
> constraint at dump time to always generate a dump in SQL03 order. This has
> the same downside as the above except only after another dump/restore.
> Change the representation on dump only if the flag is set (probably
> exporting this as an option to pg_dump as well). This could be a little
> more difficult to use, but pretty much causes the user to drive the
> choice.
I'm missing something. On what basis do you claim that there's a
"SQL03 order", ie some ordering mandated by the spec? What problem is
this really solving?
regards, tom lane
On Wed, 15 Mar 2006, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > The main options seem to be: > > When we're allowing other order access, immediately reorder the > > constraint information to match the primary key order. This helps out > > with IS since the loaded constraint should display properly, but > > theoretically could change the visual representation after load for people > > who don't care about this option. > > Change the representation unconditionally on dump. Basically reorder the > > constraint at dump time to always generate a dump in SQL03 order. This has > > the same downside as the above except only after another dump/restore. > > Change the representation on dump only if the flag is set (probably > > exporting this as an option to pg_dump as well). This could be a little > > more difficult to use, but pretty much causes the user to drive the > > choice. > > I'm missing something. On what basis do you claim that there's a > "SQL03 order", ie some ordering mandated by the spec? What problem is > this really solving? SQL2003 seems to change the relevant piece to: If the <referenced table and columns> specifies a <reference column list>, then there shall be a one-to-one correspondence between the set of <column name>s contained in that <reference column list> and the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table such that corresponding <column name>s are equivalent. Let referenced columns be the column or columns identified by that <reference column list> and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. I read the section on corresponding column names are equivalent to imply that (b,a) and (a,b) aren't equivalent for this purpose because the corresponding column names are different. That's a difference from SQL92's version which basically just says the sets are the same. Basically, it's a compliance point, and something that's necessary to make information_schema work for foreign key constraints because you can't seem to use information_schema to read how the columns line up without it because they didn't put an ordering column on the side storing the referenced keys.