Re: information_schema and not-null constraints

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter Eisentraut <peter(at)eisentraut(dot)org>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>
Subject: Re: information_schema and not-null constraints
Date: 2023-09-05 16:24:37
Message-ID: 202309051624.hrzj5uy3dmg3@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.)

On 2023-Sep-04, Tom Lane wrote:

> 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.

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)

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2023-09-05 16:25:28 Re: sandboxing untrusted code
Previous Message Aleksander Alekseev 2023-09-05 16:05:14 Re: A minor adjustment to get_cheapest_path_for_pathkeys