Chris Curvey <ccurvey@zuckergoldberg.com> writes:
> 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);
Basically, that's broken in any number of ways, not only the one you
tripped across. CHECK constraint conditions should never, ever, depend
on anything except the contents of the specific row being checked.
When you try to fake a foreign-key-like constraint with a CHECK, Postgres
will check it at inappropriate times (as per your pg_dump problem) and
fail to check it at other times when it really needs to be checked
(in this case, when you modify table bar).
You need to restructure so that you can describe the table relationship
as a regular foreign key. Anything else *will* bite you on the rear.
regards, tom lane