Re: BUG #8465: major dump/reload problem

On 21.09.2013 20:16, jan(dot)mate(at)inf-it(dot)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