Update steps to add a new foreign key to an existing table
What does this MR do and why?
In event of the severity4 incident (gitlab-com/gl-infra/production#7995 (closed)) that happened when we added a new NOT VALID
foreign key, we should ideate whether we need to change the steps mentioned in the documentation of "adding a foreign key" such that this problem does not happen again.
What happened?
- We added a new
NOT VALID
foreign key onproject.creator_id
, pointing tousers.id
, withON DELETE SET NULL
- There were ~60k projects where this FK relation wasn't valid. That is,
project.creator_id
was pointing to an already deleted user. - However, updates of such projects are not usually problematic, unless we are explicitly updating the
creator_id
of such projects again to an invaliduser_id
But, it turns out that there is a documented problem with even NOT VALID
foreign keys, such that if there are 2 updates to the record within the same transaction, the transaction will fail with
PG::ForeignKeyViolation: ERROR: insert or update on table "projects" violates foreign key constraint "fk_03ec10b0d3"
DETAIL: Key (creator_id)=(<invalid id>) is not present in table "users".
And this is what happened with us and lead to the incident. We had a place within the app code where we were updating project
twice, within a transaction, and even though we were not updating creator_id
of the record, it still failed.
What's next?
We should discuss plans on how to counter such scenarios so that we don't get into incidents in the future because of the same reasons
Some possible steps are:
- Should we audit the code base and avoid multiple updates of the same record within a transaction?
- Should we change the steps in the development guides on
how to add a foreign key to an existing table
?
Reproduction steps
From psql-bugs
create table source (id int, fk int, name character varying(10));
create table target (id int, name character varying(10));
alter table source add constraint pk_source primary key (id);
alter table target add constraint pk_target primary key (id);
insert into source values (1,99,'test');
alter table source add constraint fk_test foreign key (fk) references
target(id) not valid;
update source set name = 'twice' where id = 1; => works
update source set name = 'other' where id = 1; => works
begin;
update source set name = 'unknow2' where id = 4;
update source set name = 'unknow3' where id = 4;
commit; => does not work:
ERROR: insert or update on table "source" violates foreign key constraint
"fk_test"
DETAIL: Key (fk)=(99) is not present in table "target".
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.