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.
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 |