Re: Changing foreign key referential actions in big databases

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