Re: Changing foreign key referential actions in big databases

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