Subject: Re: information_schema and not-null constraints

From: Tom Lane <[email-masked]>
Date: Mon, 04 Sep 2023 16:43:07 -0400

Alvaro Herrera <[email-masked]> writes:
> In 0002, I took the tests added by Peter's proposed patch and put them
> in a separate test file that runs at the end.  There are some issues,
> however.  One is that the ORDER BY clause in the check_constraints view
> is not fully deterministic, because the table name is not part of the
> view definition, so we cannot sort by table name.

I object very very strongly to this proposed test method.  It
completely undoes the work I did in v15 (cc50080a8 and related)
to make the core regression test scripts mostly independent of each
other.  Even without considering the use-case of running a subset of
the tests, the new test's expected output will constantly be needing
updates as side effects of unrelated changes.

			regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 5 Sep 2023 18:24:37 +0200

On 2023-Sep-05, Peter Eisentraut wrote:

> The following information schema views are affected by the not-null
> constraint catalog entries:
> 
> 1. CHECK_CONSTRAINTS
> 2. CONSTRAINT_COLUMN_USAGE
> 3. DOMAIN_CONSTRAINTS
> 4. TABLE_CONSTRAINTS
> 
> Note that 1 and 3 also contain domain constraints.

After looking at what happens for domain constraints in older versions
(I tested 15, but I suppose this applies everywhere), I notice that we
don't seem to handle them anywhere that I can see.  My quick exercise is
just

create domain nnint as int not null;
create table foo (a nnint);

and then verify that this constraint shows nowhere -- it's not in
DOMAIN_CONSTRAINTS for starters, which is I think the most obvious place.
And nothing is shown in CHECK_CONSTRAINTS nor TABLE_CONSTRAINTS either.

This did ever work in the past?  I tested with 9.3 and didn't see
anything there either.

I am hesitant to try to add domain not-null constraint support to
information_schema in the same commit as these changes.  I think this
should be fixed separately.

(Note that if, in older versions, you change the table to be
 create table foo (a nnint NOT NULL);
 then you do get a row in table_constraints, but nothing in
 check_constraints.  With my proposed definition this constraint appears
 in check_constraints, table_constraints and constraint_column_usage.)

> updates as side effects of unrelated changes.

You're absolutely right, this would be disastrous.  A better alternative
is that the new test file creates a few objects for itself, either by
using a separate role or by using a separate schema, and we examine the
information_schema display for those objects only.  Then it'll be better
isolated.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Subversion to GIT: the shortest path to happiness I've ever heard of
                                                (Alexey Klyukin)

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 5 Sep 2023 19:15:43 +0200

> And nothing is shown in CHECK_CONSTRAINTS nor TABLE_CONSTRAINTS either.

Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
I admit I'm completely confused about what this view is supposed to
show.  Currently, we show the constraint name and a definition like
"CHECK (column IS NOT NULL)".  But since the table name is not given, it
is not possible to know to what table the column name refers to.  For
domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
indication of what domain it applies to, or anything at all that would
make this useful in any way whatsoever.

So this whole thing seems pretty futile and I'm disinclined to waste
much time on it.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 6 Sep 2023 13:02:50 +0200

> This did ever work in the past?  I tested with 9.3 and didn't see

No, this was never implemented.  (As I wrote in my other message on the 
other thread, arguably a bit buggy.)  We could fix this separately, 
unless we are going to implement catalogued domain not-null constraints 
soon.