Re: constraints and sql92 information_schema compliance

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