Chris Curvey-3 wrote
>> -----Original Message-----
>> From: Tom Lane [mailto:
> tgl@.pa
> ]
>> Sent: Thursday, August 07, 2014 2:50 PM
>> To: Chris Curvey
>> Cc:
> pgsql-general@
>> Subject: Re: [GENERAL] dump/restore with a hidden dependency?
>>
>> Chris Curvey <
> ccurvey@
> > 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?
In no particular order:
Triggers
A Compound FK that applies the check of the of the first field to the subset
defined by the second.
i.e., ... FOREIGN KEY (b_id, b_scope) REFERENCES bar (b_id, b_scope)
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/dump-restore-with-a-hidden-dependency-tp5814072p5814118.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
