Re: Database design confusing pg_restore, and misc pg_restore issues

Rick Yorgason <rick(at)longbowgames(dot)com> writes:
> In other words, (order_id, product_id) of order_item is a foreign key to
> either reginfo1, reginfo2, or nothing, depending on which product it is.

I think you'll find that few people regard that as good database design.

> The works really well, until I try to use pg_dump/pg_restore, because it
> attempts to restore order_items before the reginfo tables. To get it to
> work properly, I need to load the schema, disable the check, load the
> data, then re-enable the check.

Well, you can hardly expect pg_dump to intuit that there's a dependency
there; it understands nothing about the behavior of that SQL function.
Since you say that --disable-triggers doesn't help, I guess that you're
applying that function not in a trigger but in a CHECK constraint?
That's pretty horrid in itself: CHECK is *not* meant to enforce anything
except local properties of the newly inserted/updated row itself.
Aside from the ordering problems that you've already run into some of,
consider what happens when the referenced row gets deleted. (Hint:
nothing.)

Sure you can't find a way to unify reginfo1/reginfo2 into one table?
If you have some side information that doesn't fit conveniently into
that table, maybe making an auxiliary table that's foreign-keyed to
the master reginfo table would help. But you really need a structure
that allows you to declare the order_item table with a regular foreign
key for reginfo. Foreign keys are not something you can cobble together
from spare parts --- a correct, robust implementation requires magic
that is just not available at the user level in SQL.

regards, tom lane