Re: dump/restore with a hidden dependency?

From: David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: dump/restore with a hidden dependency?
Date: 2014-08-07 21:12:12
Message-ID: 1407445932379-5814118.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Chris Curvey-3 wrote
>> -----Original Message-----
>> From: Tom Lane [mailto:

> tgl(at)(dot)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 &lt;

> ccurvey@

> &gt; 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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message AI Rumman 2014-08-07 21:14:41 Re: not finding rows using ctid
Previous Message Adrian Klaver 2014-08-07 20:51:43 Re: not finding rows using ctid