Thread: constraints and sql92 information_schema compliance

constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Josh Berkus
Date:
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


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Bruno Wolff III
Date:
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.


Re: constraints and sql92 information_schema compliance

From
Josh Berkus
Date:
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


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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).




Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Rod Taylor
Date:
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


-- 



Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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).


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.


Re: constraints and sql92 information_schema compliance

From
"Jim C. Nasby"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Josh Berkus
Date:
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


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.


Re: constraints and sql92 information_schema compliance

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


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Andrew Dunstan
Date:

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




Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.


Re: constraints and sql92 information_schema compliance

From
Josh Berkus
Date:
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


Re: constraints and sql92 information_schema compliance

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


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Andrew - Supernews
Date:
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


Re: constraints and sql92 information_schema compliance

From
Josh Berkus
Date:
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


Re: constraints and sql92 information_schema compliance

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


Re: constraints and sql92 information_schema compliance

From
Andrew Dunstan
Date:

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


Re: constraints and sql92 information_schema compliance

From
Josh Berkus
Date:
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


Re: constraints and sql92 information_schema compliance

From
"Jim C. Nasby"
Date:
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


Re: constraints and sql92 information_schema compliance

From
"Jim C. Nasby"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
[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.



Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.



Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.



Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.


Re: constraints and sql92 information_schema compliance

From
"Clark C. Evans"
Date:
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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.



Re: constraints and sql92 information_schema compliance

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


Re: constraints and sql92 information_schema compliance

From
Stephan Szabo
Date:
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.