Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jS0s6-0006de-T5 for pgsql-bugs@arkaria.postgresql.org; Fri, 24 Apr 2020 16:08:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jS0s5-00066b-Kk for pgsql-bugs@arkaria.postgresql.org; Fri, 24 Apr 2020 16:08:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jS0s5-00066U-F1 for pgsql-bugs@lists.postgresql.org; Fri, 24 Apr 2020 16:08:09 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jS0ry-0008JJ-T1 for pgsql-bugs@lists.postgresql.org; Fri, 24 Apr 2020 16:08:08 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 03OG80TZ004073; Fri, 24 Apr 2020 12:08:00 -0400 From: Tom Lane To: Alvaro Herrera cc: Euler Taveira , pgsql-bugs@lists.postgresql.org, szittya314@gmail.com Subject: Re: BUG #16386: drop contraint in inherited table is missing in pg_dump backup In-reply-to: <20200424152839.GA28038@alvherre.pgsql> References: <20200424152839.GA28038@alvherre.pgsql> Comments: In-reply-to Alvaro Herrera message dated "Fri, 24 Apr 2020 11:28:39 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4071.1587744480.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 24 Apr 2020 12:08:00 -0400 Message-ID: <4072.1587744480@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Alvaro Herrera writes: > On 2020-Apr-24, Euler Taveira wrote: >> On Fri, 24 Apr 2020 at 07:09, PG Bug reporting form >> wrote: >>> create table b (aaa int primary key,bb date ); >>> create table A (id int primary key) inherits (B); >>> alter table a alter column aaa drop not null; >> It does not make sense to exclude a not null constraint on an inherited >> table because column "aaa" can be null in table "a" but a SELECT in tab= le >> "b" will return a NULL for a primary key (ugh). It is just one of the w= ays >> to shoot yourself in the foot. If you check CREATE TABLE synopsis, ther= e >> isn't NO INHERIT for not null constraints. Maybe it is worth adding a n= ote >> in CREATE TABLE. > I agree with your analysis, but in that case we should make the DROP NOT > NULL throw an error instead of proceeding. Indeed, but we lack the catalog infrastructure to do that reasonably. If we ever get around to creating pg_constraint entries for NOT NULL constraints, it'd be simple to do, since those would carry info about whether or not the constraint is inherited. (Weren't you working on that awhile ago?) regards, tom lane