Обсуждение: BUG #4709: dump/restore introduces wrong CHECK constraint for inherited table

The following bug has been logged online:

Bug reference:      4709
Logged by:          Andrey
Email address:      andrey@ulab.ru
PostgreSQL version: 8.3.6
Operating system:   rhel-4-i386
Description:        dump/restore introduces wrong CHECK constraint for
inherited table
Details:

Assume we have 2 tables with additionally added CHECK constraints:
CREATE TABLE t
(
id serial NOT NULL,
"type" integer NOT NULL,
CONSTRAINT pkey_t PRIMARY KEY (id)
) WITH (OIDS=FALSE);

CREATE TABLE t1
(
CONSTRAINT pkey_t1 PRIMARY KEY (id)
) INHERITS (t) WITH (OIDS=FALSE);

ALTER TABLE ONLY t ADD CONSTRAINT type_eq_zero CHECK (type = 0);
ALTER TABLE t1 ADD CONSTRAINT type_eq_one CHECK (type = 1);

As one can see, "type_eq_zero" constraint is applied to table "t" only and
it's not applied to "t1".

Then I do dump for entire database. After that I try to restore it and see
that after schema part of dump file is restored, table "t1" has both
"type_eq_zero" and "type_eq_one" constraints, which is completely different
situation that leads to failures when restoration process goes further and
tries to load data to the table.

Postgres version 8.3.6 under RHEL4 (32 bit)

To run dump I use: pg_dump -f <filename> -F p <databasename>
To restore: psql -f <filename> <databasename>

"Andrey" <andrey@ulab.ru> writes:
> Assume we have 2 tables with additionally added CHECK constraints:
> ...
> ALTER TABLE ONLY t ADD CONSTRAINT type_eq_zero CHECK (type = 0);

The way this is addressed in CVS HEAD is that such commands are
forbidden:

regression=# ALTER TABLE ONLY t ADD CONSTRAINT type_eq_zero CHECK (type = 0);
ERROR:  constraint must be added to child tables too

An "ONLY" check constraint isn't very reasonable because then the table
would appear to contain rows that violate the constraint.  What I'd
suggest for what you seem to be trying to accomplish is to have an
empty parent table and two child tables, each with a constraint
restricting the "type" column.  The information on partitioning in the
manual might help you, since this is fundamentally a partitioning setup.

The behavior of 8.3 and before in this regard won't be changed.

            regards, tom lane