I have a database with the following structure:
Create table bar…
Create function subset_of_bar … (which does a select on a subset of “bar”)
Create table foo…
Alter table foo add constraint mycheck check subset_of_bar(id);
I pg_dumped my database, and tried to pg_restore it on another server. When I do that, foo is being loaded before bar (and since there is no data in bar, the inserts to foo fail). I suspect that this is because the check constraint calls a function, which hides the dependency between the tables, so pg_dump does not get the required ordering of tables.
I thought (hoped?) that using –disable-triggers *might* help with that, so I tried to do the pg_restore in two steps (one with a –schema-only, and then another one with –data-only and –disable-triggers), but –disable-triggers does not seem to disable constraints (which is not surprising).
I’ve done some searching and am coming up empty. Is there a way to get pg_restore to apply constraints AFTER loading all the tables (I’d even be fine with a multi-step reload process of table structures, then data, then indexes, then constraints). I’d rather not go down the path of having to maintain an explicit ordered list of tables (but I might have to).
Every time you report an issue without creating a ticket, God kills a kitten. Please think of the kittens.
Disclaimer
THIS IS A CONFIDENTIAL COMMUNICATION. The information contained in this e-mail is attorney-client privileged and confidential, intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are here by notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail or by telephone (908) 233-8500.Thank you..
