Thread: BUG #13656: table inheritance, pg_dump emits same constraint for all inheritors causing errors

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

swingi@gmail.com writes:
> 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.

Seems to be a simple oversight in the code for handling not-valid
constraints.  Thanks for the report!

            regards, tom lane

On Fri, Oct 2, 2015 at 3:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> swingi@gmail.com writes:
>> 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.
>
> Seems to be a simple oversight in the code for handling not-valid
> constraints.  Thanks for the report!

Tom, are you working on a fix? I began looking at it a bit when this
has been posted FWIW so I could post a patch except that I run out of
fuel yesterday :)
--
Michael

On Fri, Oct 2, 2015 at 8:21 AM, Michael Paquier wrote:
> Tom, are you working on a fix? I began looking at it a bit when this
> has been posted FWIW so I could post a patch except that I run out of
> fuel yesterday :)

Please ignore, you already fixed this issue. Sorry for the noise.
--
Michael