Arthur Silva <arthurprs@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
