Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On 2025-Mar-31, Robert Haas wrote:

> It seems like a bad idea to make conislocal and coninhcount have
> anything to do with whether the constraint is valid. We need those to
> mean what they have traditionally meant just to make the correct
> things happen when the constraint is dropped, either directly from the
> child or at some ancestor of that child. If something is dropped at an
> ancestor table, we cascade down the tree and decrement coninhcount. If
> something is dropped at a child table, we can set conislocal=false.
> The constraint goes away, IIUC, when coninhcount=0 and
> conislocal=false, which directly corresponds to "this constraint no
> longer has a remaining definition either locally or by inheritance". I
> don't see how you can change much of anything here without breaking
> the existing structure. Validity could have separate tracking of some
> sort, possibly as elaborate as convalidlocal and convalidinhcount, but
> I don't think it can get away with redefining the tracking that we
> already have for existence.

Yeah, I think you're (at least partly) right. I tried a quick
experiment with check constraints on Postgres 10 and I think it behaves
as you say -- namely this script:

create table parted (a int) partition by list (a);
create table part1 partition of parted for values in (1, -1);
alter table part1 add constraint parted_a_check check (a > 0);
alter table parted add constraint parted_a_check check (a > 0) not valid;

results in the constraint in the child having conislocal=false and
pg_dump producing this:

CREATE TABLE public.parted (
a integer
)
PARTITION BY LIST (a);

ALTER TABLE public.parted OWNER TO alvherre;

--
-- Name: part1; Type: TABLE; Schema: public; Owner: alvherre
--

CREATE TABLE public.part1 (
a integer,
CONSTRAINT parted_a_check CHECK ((a > 0))
);
ALTER TABLE ONLY public.parted ATTACH PARTITION public.part1 FOR VALUES IN (1, '-1');

ALTER TABLE public.part1 OWNER TO alvherre;

--
-- Name: parted parted_a_check; Type: CHECK CONSTRAINT; Schema: public; Owner: alvherre
--

ALTER TABLE public.parted
ADD CONSTRAINT parted_a_check CHECK ((a > 0)) NOT VALID;

I don't quite love this behavior, but since there have been no
complaints, I suppose it's okay and we should just do the same for
not-nulls.

FWIW the part that I think you're not right on, is that constraints on
partitioned tables never have local definitions. Even if you start with
a constraint defined locally in the partition, the ATTACH operation will
change its conislocal flag to false. So you can never "drop" it from
the partition. For regular inheritance, we don't flip the conislocal
flag to false, but you're still prevented from "dropping" the constraint
from the child while the inheritance relationship exists (i.e. you can
never set conislocal=false in such a case).

-- first case
create table parent (a int);
create table child () inherits (parent);
alter table child add constraint parent_a_check check (a > 0);
alter table parent add constraint parent_a_check check (a > 0) not valid;

-- second case
drop table parent, child;
create table parent (a int);
create table child (a int);
alter table child add constraint parent_a_check check (a > 0);
alter table parent add constraint parent_a_check check (a > 0) not valid;
alter table child inherit parent;

In both these cases, the constraint ends up with conislocal=true, but
ALTER TABLE child DROP CONSTRAINT parent_a_check will fail with
ERROR: cannot drop inherited constraint "parent_a_check" of relation "child"

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