From: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
---|---|
To: | jian he <jian(dot)universality(at)gmail(dot)com> |
Cc: | Tender Wang <tndrwang(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: not null constraints, again |
Date: | 2024-10-02 12:19:35 |
Message-ID: | 202410021219.bvjmxzdspif2@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2024-Oct-02, jian he wrote:
> On Tue, Oct 1, 2024 at 11:20 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> >
> > On 2024-Oct-01, jian he wrote:
> >
> > > create table t2 (a int primary key constraint foo not null no inherit);
> > > primary key cannot coexist with not-null no inherit?
> > > here t2, pg_dump/restore will fail.
> >
> > Yeah, this needs to throw an error. If you use a table constraint, it
> > does fail as expected:
> >
> > create table notnull_tbl_fail (a int primary key, not null a no inherit);
> > ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
> >
> > I missed adding the check in the column constraint case.
> >
> after v7, still not bullet-proof. as before, pg_dump/restore will fail
> for the following:
>
> drop table if exists t2, t2_0
> create table t2 (a int, b int, c int, constraint foo primary key(a),
> constraint foo1 not null a no inherit);
> create table t2_0 (a int constraint foo1 not null no inherit, b int, c
> int, constraint foo12 primary key(a));
Rats. Fixing :-)
> drop table if exists idxpart,idxpart0,idxpart1 cascade;
> create table idxpart (a int not null) partition by list (a);
> create table idxpart0 (a int constraint foo not null no inherit);
> alter table idxpart attach partition idxpart0 for values in (0,1);
>
> With V7, we basically cannot change the status of "NO INHERIT".
> now, we need to drop the not-null constraint foo,
> recreate a not-null constraint on idxpart0,
> then attach it to the partitioned table idxpart.
Yeah, that sucks. We'll need a new command
ALTER TABLE .. ALTER CONSTRAINT .. INHERIT
(exact syntax TBD) which allows you to turn a NO INHERIT constraint into
a normal one, to avoid this problem. I suggest we don't hold up this
patch for that.
> ---------------------------------------------------------------------------------
> drop table if exists inh_parent,inh_child1,inh_child2;
> create table inh_parent(f1 int);
> create table inh_child1(f1 int not null);
> alter table inh_child1 inherit inh_parent;
> alter table only inh_parent add constraint nn not null f1;
> alter table only inh_parent alter column f1 set not null;
>
> minor inconsistency, i guess.
> "alter table only inh_parent add constraint nn not null f1;"
> will fail.
> But
> "alter table only inh_parent alter column f1 set not null;"
> will not fail, but add a "NOT NULL f1 NO INHERIT" constraint.
> I thought they should behave the same.
>
>
> for partitioned table
> now both ALTER TABLE ONLY ADD CONSTRAINT NOT NULL,
> ALTER TABLE ONLY ALTER COLUMN SET NOT NULL
> will error out.
> I am fine with partitioned table behavior.
Yeah, this naughty relationship between ONLY and NO INHERIT is
bothersome and maybe we need to re-examine it.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
#error "Operator lives in the wrong universe"
("Use of cookies in real-time system development", M. Gleixner, M. Mc Guire)
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2024-10-02 14:04:15 | Re: On disable_cost |
Previous Message | Frédéric Yhuel | 2024-10-02 11:58:45 | Re: Allowing parallel-safe initplans |