On Nov 7, 2016 3:34 PM, "Tom Lane" <t...@sss.pgh.pa.us> wrote: > > Arthur Silva <arthur...@gmail.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.
