Re: pg_dump table ordering bug [8.0.1]

Andreas Lange <anlan(at)ida(dot)liu(dot)se> writes:
> Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
> got dumped in the wrong order (before their dependecies) and had to get
> their contents added manually after the restore. I've atleast isolated
> the part where things go wrong.

I don't think this is a pg_dump bug: the problem is you are abusing
check constraints to emulate foreign key constraints. pg_dump has no
way to know what those functions are doing and therefore no way to
realize that the check constraints impose a data load ordering
dependency. Furthermore, the check constraints are fundamentally wrong
anyway because they don't create a two-way relationship --- that is,
altering the referenced tables won't raise an error if the check is now
violated for something in the referencing table.

It would be best to find a way to express these relationships with
ordinary foreign keys. Maybe you could add a column to form_a_int
that is a foreign key reference to both of form_instance.fid and
form_q.fid, for example?

regards, tom lane