Re: Tweaking Foreign Keys for larger tables

On 5 November 2014 21:15, Peter Eisentraut <peter_e(at)gmx(dot)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