I have an existing foreign key that has ON DELETE NO ACTION
defined. I need to change this foreign key to ON DELETE CASCADE
. I can do this within a transaction:
begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade;
commit;
The problem is that the posts
table is large (4 million rows) which means that validating the foreign key can take a non-trivial amount of time (I've tested this with a copy of the database). Dropping/adding the foreign key acquires an ACCESS EXCLUSIVE
lock on posts
. So, adding the foreign key blocks all access to the posts
table for a decent amount of time because the lock is held while constraint validation occurs. I need to perform an online migration (I don't have a dedicated downtime window).
I know that I can perform 2 transactions to help with the check taking a long time:
begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade not valid;
commit;
begin;
alter table posts validate constraint posts;
commit;
The advantage of this approach is that the ACCESS EXCLUSIVE
lock is held for a very short time for dropping/adding the constraint and then for validating the constraint only a SHARE UPDATE EXCLUSIVE
on posts
and ROW SHARE
lock on blogs
since I'm on Postgres 9.5.
Are there downsides to this? I know that adding NOT VALID
to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT
will be checked. Because the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data?
Best Answer
The docs say this about
NOT VALID
Your concern,
They'll only be checked during validation AFTER you tell it to validate, so you can delay that until you have scheduled downtime.
No, because the second you add the
NOT VALID
it applies to all rows inserted AFTER the statement as if they were always there. VALIDATION is for the rejection of creating anFOREIGN KEY
when the referenced rows do not exist. It has nothing to do with cascading, observeAt this point you can see
bar
cascaded tobar
The constraint still can't be validated (as shown below), but for the purposes of cascaded deletion all things are good.
Btw you can write this
Like this
You don't have to wrap it in a txn. You also don't have to wrap any single statement in a txn -- PostgreSQL isn't MySQL. Everything is already transactional.