BUG #14325: backup restore inherited constraint

From: vbv256(at)yandex(dot)ru
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14325: backup restore inherited constraint
Date: 2016-09-16 06:27:35
Message-ID: 20160916062735.1421.7431@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14325
Logged by: Boris Vuks
Email address: vbv256(at)yandex(dot)ru
PostgreSQL version: 9.5.4
Operating system: Slackware64. Build from source.
Description:

Incorrect dumping inherited table constraint.

If child table has field with removed inherit constraint.
In dump file this constraint not removed and data not restored correctly.
Problem also exist in PostgreSQL 9.1. Before this version appropriate
construction not used by me.

-- --- to reproduce problem ----------
bash$ psql template1
template1=# create database test;
template1=# \c test

test=# create table test_parent(
field1 int,
field2 int not null
);

test=# create table test(
)inherits(test_parent);

test=# alter table test alter COLUMN field2 drop not null;

test=# insert into test(field1, field2)
values(1,1),
(1,null);

test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
field1 | integer |
field2 | integer |
Inherits: test_parent

-- ..... COMMENT: modifiers of field2 is empty.......
test=# \q

bash$ pg_dump -C test > test.sql
bash$ psql template1
template1=# drop database test;
template1=# \q

bash$ psql template1 < test.sql
......
ERROR: null value in column "field2" violates not-null constraint
DETAIL: Failing row contains (1, null).
CONTEXT: COPY test, line 2: "1 \N"
COPY 0
.......

bash$ psql test
test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
field1 | integer |
field2 | integer | not null
Inherits: test_parent

-- ..... COMMENT: modifiers of field2 is not empty .......

\q

-- ----------end ----------------
in result table test has field2 with constraint NOT NULL :(

Sorry for my bad english.

PS: System Detail:
# uname -a
Linux v 4.7.0 #1 SMP Mon Aug 1 03:59:35 EEST 2016 x86_64 Intel(R) Core(TM)2
Quad CPU Q8300 @ 2.50GHz GenuineIntel GNU/Linux

PostgreSQL configure line:
./configure \
--prefix=/usr \
--libdir=/usr/lib64 \
--with-perl \
--with-python \
--with-openssl \
--with-libxml \
--with-libxslt

PS1: Currently I solve this problem use modify dump with 'sed' by adding
ALTER for remove constraint from field in this case for table 'test'.

PS2: I'am undestand logical incorrection this construction.
If parent requre field value, then child should not remove this constraint.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message marco.nenciarini 2016-09-16 09:54:48 BUG #14326: Unexpected status after crash during exclusive backup
Previous Message Keith Fiske 2016-09-15 22:17:07 9.6rc1 Background worker starting multiple times