Re: dump/restore with a hidden dependency?

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.