Re: not null constraints, again - Mailing list pgsql-hackers

On 2024-Oct-02, jian he wrote:

> On Tue, Oct 1, 2024 at 11:20 PM Alvaro Herrera <alvherre@alvh.no-ip.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)