Re: Changing foreign key referential actions in big databases

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Arthur Silva <arthurprs(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Changing foreign key referential actions in big databases
Date: 2016-11-07 14:29:56
Message-ID: 51e6a305-f76d-fec2-b7d7-b5e753c53250@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/07/2016 02:09 AM, Arthur Silva wrote:
> 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?

Are there indexes on the child columns?

> Is it safe(ish) to just update pg_constraint.confupdtype and
> pg_constraint.confdeltype for those?
>
> Regards
>
> --
> Arthur Silva
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-11-07 14:33:59 Re: Changing foreign key referential actions in big databases
Previous Message Merlin Moncure 2016-11-07 14:22:54 Re: How to hint 2 coulms IS NOT DISTINCT FROM each other