Thread: BUG #8465: major dump/reload problem
The following bug has been logged on the website: Bug reference: 8465 Logged by: Jan Mate Email address: jan.mate@inf-it.com PostgreSQL version: 9.3.0 Operating system: all Description: Hi PostgreSQL team, today I tried to upgrade from 9.2 to 9.3 (pg_upgradecluster 9.2 main) and the upgrade of one of my databases failed with the following error: "ERROR: new row for relation ... violates check constraint ...". I created an example to reproduce this bug: http://www.inf-it.com/fixes/postgres-bugreport2/schema.sql steps to reproduce it (tested with version 9.3): createdb test && psql test < schema.sql pg_dump test > dump.sql dropdb test && createdb test && psql test < dump.sql
On 21.09.2013 20:16, jan.mate@inf-it.com wrote: > today I tried to upgrade from 9.2 to 9.3 (pg_upgradecluster 9.2 main) and > the upgrade of one of my databases failed with the following error: "ERROR: > new row for relation ... violates check constraint ...". > > I created an example to reproduce this bug: > > http://www.inf-it.com/fixes/postgres-bugreport2/schema.sql The problem is that when the database is dumped with pg_dump and reloaded, the activity table is loaded first, and codebook table second. The check constraint checks that when a row is inserted into activity table, the corresponding row exists in codebook table, which clearly isn't true if the activity table is loaded first and the codebook table is still empty. The system doesn't know about that dependency since it's all implemented in the PL/pgSQL code. With a constraint like that, you would also get an unrestorable dump if you e.g deleted a row from codebook table after loading the activities. Usually you would implement a schema like that using foreign keys. That would be less code, and the system would automatically get the dump order correct. I would recommend that over a check constraint, if possible. As a work-around, you can drop the constraints from the database before upgrading, and restore them afterwards. The problem isn't really related to upgrade per se, BTW. Running pg_dump + restore even on the same version will give you the same error. - Heikki
Hi Keikki,
it is clear what causes this problem, but pg_upgradecluster is an operation which simply MUST work (if you have several hundred of user databases, it is not an option to check all of them for this kind of error before the cluster upgrade).
Database upgrade is performed by the server administrator and a simple check constraint (created by a random database user) CANNOT break it.
The real solution is to change the pg_dump result from:
CREATE TABLE activity (
_id bigint NOT NULL,
name text NOT NULL,
country bigint NOT NULL,
currency bigint NOT NULL,
CONSTRAINT activity_country_check CHECK (codebook_check((1)::bigint, country, false)),
CONSTRAINT activity_currency_check CHECK (codebook_check((2)::bigint, currency, false))
);
_id bigint NOT NULL,
name text NOT NULL,
country bigint NOT NULL,
currency bigint NOT NULL,
CONSTRAINT activity_country_check CHECK (codebook_check((1)::bigint, country, false)),
CONSTRAINT activity_currency_check CHECK (codebook_check((2)::bigint, currency, false))
);
<<import data here>>
to
CREATE TABLE activity (
_id bigint NOT NULL,
name text NOT NULL,
country bigint NOT NULL,
currency bigint NOT NULL
);
_id bigint NOT NULL,
name text NOT NULL,
country bigint NOT NULL,
currency bigint NOT NULL
);
<<import data here>>
ALTER TABLE activity ADD CHECK (codebook_check((1)::bigint, country, false));
ALTER TABLE activity ADD CHECK (codebook_check((2)::bigint, currency, false));
JM
On Sep 23, 2013, at 10:05 AM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
On 21.09.2013 20:16, jan.mate@inf-it.com wrote:today I tried to upgrade from 9.2 to 9.3 (pg_upgradecluster 9.2 main) and
the upgrade of one of my databases failed with the following error: "ERROR:
new row for relation ... violates check constraint ...".
I created an example to reproduce this bug:
http://www.inf-it.com/fixes/postgres-bugreport2/schema.sql
The problem is that when the database is dumped with pg_dump and reloaded, the activity table is loaded first, and codebook table second. The check constraint checks that when a row is inserted into activity table, the corresponding row exists in codebook table, which clearly isn't true if the activity table is loaded first and the codebook table is still empty. The system doesn't know about that dependency since it's all implemented in the PL/pgSQL code. With a constraint like that, you would also get an unrestorable dump if you e.g deleted a row from codebook table after loading the activities.
Usually you would implement a schema like that using foreign keys. That would be less code, and the system would automatically get the dump order correct. I would recommend that over a check constraint, if possible.
As a work-around, you can drop the constraints from the database before upgrading, and restore them afterwards. The problem isn't really related to upgrade per se, BTW. Running pg_dump + restore even on the same version will give you the same error.
- Heikki