Re: Tweaking Foreign Keys for larger tables

On 5 November 2014 21:15, Peter Eisentraut <peter_e@gmx.net> wrote:
> On 10/31/14 6:19 AM, Simon Riggs wrote:
>> Various ways of tweaking Foreign Keys are suggested that are helpful
>> for larger databases.
>
>> *    INITIALLY NOT ENFORCED
>> FK created, but is not enforced during DML.
>> Will be/Must be marked NOT VALID when first created.
>> We can run a VALIDATE on the constraint at any time; if it passes the
>> check it is marked VALID and presumed to stay that way until the next
>> VALIDATE run.
>
> Does that mean the FK would become invalid after every DML operation,
> until you expicitly revalidate it?  Is that practical?

I think so.

We store the validity on the relcache entry.

Constraint would add a statement-level after trigger for insert,
update, delete and trigger, which issues a relcache invalidation if
the state was marked valid. Marked as deferrable initially deferred.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services