Re: Tweaking Foreign Keys for larger tables

On Thu, Nov 6, 2014 at 10:29 AM, Jim Nasby-5 [via PostgreSQL] <
ml-node+s1045698n5825967h1(at)n5(dot)nabble(dot)com> wrote:

> On 11/6/14, 2:58 AM, Simon Riggs wrote:
>
> > On 5 November 2014 21:15, Peter Eisentraut <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5825967&i=0>> 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.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Tweaking-Foreign-Keys-for-larger-tables-tp5825162p5825970.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.