Thread: 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>
Re: BUG #4709: dump/restore introduces wrong CHECK constraint for inherited table
From
Tom Lane
Date:
"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