On Thu, Nov 6, 2014 at 10:29 AM, Jim Nasby-5 [via PostgreSQL] <[hidden email]> wrote:
On 11/6/14, 2:58 AM, Simon Riggs wrote:
> On 5 November 2014 21:15, Peter Eisentraut <[hidden email]> 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.
I don't think you'd need to invalidate on insert,
Why? Since the FK is not enforced there is no guarantee that what you just inserted is valid
or on an update that didn't touch a referenced key.
OK - but you would still need the trigger on the FK columns
DELETE is OK as well since you cannot invalidate the constraint by simply removing the referencing row.