pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16386: drop contraint in inherited table is missing in pg_dump backup
4+ messages / 4 participants
[nested] [flat]

* BUG #16386: drop contraint in inherited table is missing in pg_dump backup
@ 2020-04-24 10:02  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 4+ messages in thread

From: PG Bug reporting form @ 2020-04-24 10:02 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: szittya314@gmail.com

The following bug has been logged on the website:

Bug reference:      16386
Logged by:          klement szabolcs
Email address:      szittya314@gmail.com
PostgreSQL version: 10.12
Operating system:   centos 7
Description:        

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;

after i made pg_dump and pg_restore the not null contraint isexist on table
a;



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16386: drop contraint in inherited table is missing in pg_dump backup
@ 2020-04-24 15:13  Euler Taveira <euler.taveira@2ndquadrant.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 4+ messages in thread

From: Euler Taveira @ 2020-04-24 15:13 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: szittya314@gmail.com

On Fri, 24 Apr 2020 at 07:09, PG Bug reporting form <noreply@postgresql.org>
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;
>
> after i made pg_dump and pg_restore the not null contraint isexist on table
> a;
>
> 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 table
"b" will return a NULL for a primary key (ugh). It is just one of the ways
to shoot yourself in the foot. If you check CREATE TABLE synopsis, there
isn't NO INHERIT for not null constraints. Maybe it is worth adding a note
in CREATE TABLE.


-- 
Euler Taveira                 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16386: drop contraint in inherited table is missing in pg_dump backup
@ 2020-04-24 15:28  Alvaro Herrera <alvherre@2ndquadrant.com>
  parent: Euler Taveira <euler.taveira@2ndquadrant.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Alvaro Herrera @ 2020-04-24 15:28 UTC (permalink / raw)
  To: Euler Taveira <euler.taveira@2ndquadrant.com>; +Cc: pgsql-bugs@lists.postgresql.org; szittya314@gmail.com

On 2020-Apr-24, Euler Taveira wrote:

> On Fri, 24 Apr 2020 at 07:09, PG Bug reporting form <noreply@postgresql.org>
> 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;
> >
> > after i made pg_dump and pg_restore the not null contraint isexist on table
> > a;
> >
> 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 table
> "b" will return a NULL for a primary key (ugh). It is just one of the ways
> to shoot yourself in the foot. If you check CREATE TABLE synopsis, there
> isn't NO INHERIT for not null constraints. Maybe it is worth adding a note
> 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.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16386: drop contraint in inherited table is missing in pg_dump backup
@ 2020-04-24 16:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alvaro Herrera <alvherre@2ndquadrant.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2020-04-24 16:08 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Euler Taveira <euler.taveira@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org; szittya314@gmail.com

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Apr-24, Euler Taveira wrote:
>> On Fri, 24 Apr 2020 at 07:09, PG Bug reporting form <noreply@postgresql.org>
>> 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 table
>> "b" will return a NULL for a primary key (ugh). It is just one of the ways
>> to shoot yourself in the foot. If you check CREATE TABLE synopsis, there
>> isn't NO INHERIT for not null constraints. Maybe it is worth adding a note
>> 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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2020-04-24 16:08 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-24 10:02 BUG #16386: drop contraint in inherited table is missing in pg_dump backup PG Bug reporting form <noreply@postgresql.org>
2020-04-24 15:13 ` Euler Taveira <euler.taveira@2ndquadrant.com>
2020-04-24 15:28   ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-04-24 16:08     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox