Changing foreign key referential actions in big databases

From: Arthur Silva <arthurprs(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Changing foreign key referential actions in big databases
Date: 2016-11-07 10:09:23
Message-ID: CAO_YK0WRqSq-=-Esmb=8X2Nw6uRRTZSS0DXqPJzJ=SJJKNDvmA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all, we're running a few Pg databases in production.

Ubuntu 14.04 x64
32 x64 cores
64GB to 256GB memory, depending on cluster
PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
FusionIO storage

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).

ALTER TABLE "partneracl"
DROP CONSTRAINT "partneracl_partner_fkey",
ADD CONSTRAINT "partneracl_partner_fkey"
FOREIGN KEY ("partner")
REFERENCES "partner"("name");

Is there any way to change the foreign key referential actions quickly
and/or without an exclusive lock?
Is it safe(ish) to just update pg_constraint.confupdtype and
pg_constraint.confdeltype for those?

Regards

--
Arthur Silva

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom DalPozzo 2016-11-07 10:50:27 checkpoint_timout with no WAL activity
Previous Message Emrul 2016-11-07 10:04:19 Re: Dynamic execution returning large result sets