Re: Changing foreign key referential actions in big databases

On Nov 7, 2016 3:34 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Arthur Silva <arthurprs(at)gmail(dot)com> writes:
> > We recently started looking into a long standing ticket to change some
> > foreign keys referential actions from CASCADE to RESTRICT for our own
> > safety. Everything else in the FK stays the same.
> > The problem is that running a query like the one bellow takes an
exclusive
> > lock for too long (order of minutes in some tables when testing against
a
> > backup db).
> > ...
> > Is it safe(ish) to just update pg_constraint.confupdtype and
> > pg_constraint.confdeltype for those?
>
> Well, it's probably safe, but it wouldn't have the results you want.
> What actually drives that behavior is the choice of trigger functions
> applied to the relations, so you'd have to also update the related
> pg_trigger rows appropriately.
>
> Also, I'm not too sure about the cacheing situation for pg_trigger,
> but it's moderately likely that a manual UPDATE on pg_trigger wouldn't
> force a cache flush, so that you'd have to do something extra to get
> running backends to notice the pg_trigger changes. Since you're living
> dangerously already, a dummy UPDATE on the pg_class row for the affected
> relation would be good enough.
>
> You could probably get away with all that as long as your application
> isn't doing anything that makes it matter critically which semantics
> get applied while the changeover is being made.
>
> But test on a scratch database ...
>
> regards, tom lane

I see. Unfortunately I think all that would cross our "living too
dangerously" line.