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 on project.creator_id, pointing to users.id, with ON 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 invalid user_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.