Thread: Changing foreign key referential actions in big databases
Ubuntu 14.04 x64
64GB to 256GB memory, depending on cluster
ALTER TABLE "partneracl"
DROP CONSTRAINT "partneracl_partner_fkey",
ADD CONSTRAINT "partneracl_partner_fkey"
FOREIGN KEY ("partner")
REFERENCES "partner"("name");
Is it safe(ish) to just update pg_constraint.confupdtype and pg_constraint.confdeltype for those?
Arthur Silva
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.klaver@aklaver.com
Arthur Silva <arthurprs@gmail.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
On Nov 7, 2016 3:29 PM, "Adrian Klaver" <adrian.klaver@aklaver.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.klaver@aklaver.com
On Nov 7, 2016 3:34 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>
> Arthur Silva <arthurprs@gmail.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
I see. Unfortunately I think all that would cross our "living too dangerously" line.