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>
