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