Re: Changing foreign key referential actions in big databases

On Nov 7, 2016 3:29 PM, "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> 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?
>

Yes, they're all backed by indexes.

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