Re: dump/restore with a hidden dependency?

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, August 07, 2014 2:50 PM
> To: Chris Curvey
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] dump/restore with a hidden dependency?
>
> Chris Curvey <ccurvey(at)zuckergoldberg(dot)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

Thanks for the heads-up. Given that my requirement doesn't change (entries in foo must not only reference a row in bar, but must reference row in a subset of bar), what would be the recommended path forward? You can't reference a view. Using table inheritance feels like the wrong solution.

Perhaps a pair of triggers? An insert-or-update trigger on foo, and a delete-or-update trigger on bar?

Any other ideas?
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..