The following bug has been logged on the website:
Bug reference: 13656
Logged by: Ingmar Brouns
Email address: swingi@gmail.com
PostgreSQL version: 9.4.4
Operating system: fedora 21
Description:
Hi,
I just noticed that when you add a not valid constraint to a table that is
inherited, this will result in pg_dump emitting an add constraint operation
for every table in the inheritance hierarchy. Therefore restoring a dump
will cause errors. Below is an example, first with a valid constraint (goes
well), and then the not valid constraint.
$ psql
psql (9.4.4)
Type "help" for help.
testdb=# create table foo ( a integer );
CREATE TABLE
testdb=# create table bar ( b integer) inherits (foo);
CREATE TABLE
testdb=# alter table foo add constraint foo_constraint check (true);
ALTER TABLE
testdb=# \q
$ pg_dump testdb > db.sql
$ dropdb testdb
$ createdb testdb
$ psql -d testdb -f db.sql
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 0
COPY 0
REVOKE
REVOKE
GRANT
GRANT
$ psql
psql (9.4.4)
Type "help" for help.
testdb=# alter table foo drop constraint foo_constraint;
ALTER TABLE
testdb=# alter table foo add constraint foo_constraint check (true) not
valid;
ALTER TABLE
testdb=# \q
$ pg_dump testdb > db.sql
$ dropdb testdb
$ createdb testdb
$ psql -d testdb -f db.sql
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 0
COPY 0
ALTER TABLE
psql:db.sql:84: ERROR: constraint "foo_constraint" for relation "bar"
already exists
REVOKE
REVOKE
GRANT
GRANT
When looking in the dump file, you see that in the case that goes well the
constraint is part of the table definition
CREATE TABLE foo (
a integer,
CONSTRAINT foo_constraint CHECK (true)
);
However, for the not valid constraint, a separate ddl operation is there for
every table:
ALTER TABLE foo
ADD CONSTRAINT foo_constraint CHECK (true) NOT VALID;
ALTER TABLE bar
ADD CONSTRAINT foo_constraint CHECK (true) NOT VALID;
Regards,
Ingmar Brouns
