Re: information_schema.check_constraints Inconsistencies

Hristo Ivanov <hristo(dot)atanassov(at)gmail(dot)com> writes:
> 2018-09-18 22:59 GMT+03:00 Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>:
>> In the SQL spec, the name of a constraint is not local to the table; in
>> a given schema, the name must be unique. PostgreSQL does not enforce
>> this, and generally treats constraint names as local to a single
>> _table_; this is difficult to fix retroactively because it would make
>> old databases fail to restore if the spec's conditions were enforced.

> I didn't know that constraint names had to be unique. Even if that is true,
> I don't think returning wrong constraints in this case (belonging to a
> different table) is the right thing to do. This means that PostgreSQL is
> conforming to the standard in only places, while the dependencies are
> clearly not standard compliant. Since the likelihood of fixing dependencies
> is fairly small, I would suggest fixing the constraints selection behavior.

It was already explained to you that we're not changing this. The
information_schema outputs conform to the spec as long as the inputs
(i.e., the set of constraint names created by your application) do.
It's not very plausible to insist on spec compliance for what you see
in information_schema when the violation is your own fault. Moreover,
the only thing we could do to make the situation more compliant would
be to enforce constraint name uniqueness schema-wide, which is not really
very desirable (on any metric other than blind standards compliance)
and would create major backwards-compatibility issues. So no, it's not
going to change.

> Fair enough. Could I suggest having a column to discriminate non-null
> constraints from the rest?

Not in the information_schema you can't :-(. The set of columns in
those views is dictated by the standard. Adding more would just be
another way of not being compliant.

> FYI, the only solution I found to this problem, is: ...
> This completely disregards the information_schema objects.

Yup, if you want to deal with non-standard-compliant objects or
situations, you generally need to ignore information_schema and
look directly at the catalogs.

regards, tom lane