constraints and sql92 information_schema compliance

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
nicely unless 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 error is 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 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.

| > 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 null
z | 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 the SQL03 information, and
information_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 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).

| 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