Summary

Due to unique constraint violation in Postgres, duplicate records could exists in Bitbucket database tables such as sta_normal_user, project, & plugin_setting.

This will result in a user who no longer exists been assigned as a default reviewer when creating a pull-request.

Environment

Bitbucket 7.17.4
PostgreSQL

Diagnosis

1. The sta_normal_user table  has duplicate entry of the user. The entry has different user_id & slug , but the same name. 

2. Duplicates are found in other tables such as project and plugin_setting table.

Cause

This seems to have occurred due to the bug causing unique constraint violation in Postgres due to OS upgrade.

Solution

Step 1:

1. Set up a test instance.

2. Get all the users who are duplicated, in ascending order (older users first) of their user_id using the query below:
   
   

   SELECT * from sta_normal_user where name in (SELECT (sta_normal_user.name)::text
   from sta_normal_user
   GROUP BY sta_normal_user.name
   HAVING count(*) > 1) ORDER by user_id;

3. Locate the duplicated users which you no longer require. For example, if the  first 17 users  seem to be the old users, rename the username which are duplicate entries with the query below:

   UPDATE sta_normal_user
   SET
           name = slug || '.dis',
           slug = slug || '.dis'
   where user_id in 
   (SELECT user_id from sta_normal_user where name in (SELECT (sta_normal_user.name)::text
   from sta_normal_user
   GROUP BY sta_normal_user.name
   HAVING count(*) > 1) ORDER by user_id limit 17)

The above query would fix the duplicates for the sta_normal_user table .

Step 2:

We still need to fix the duplicate entries for the table project  and plugin_setting tables.

1. To find the duplicates of project  table run the below query:

   select *
   from project pr
   where (select count(*)
          from project inr
          where inr.name = pr.name) > 1
   ORDER by name;

2. Select the id of the duplicate project and delete it using the query below:

   DELETE FROM public.project WHERE id = project_id

   The project_id  is gotten from the query in step 1.

3. To find the duplicates on the plugin_setting table run the query below:

   select *
   from plugin_setting pr
   where (select count(*)
          from plugin_setting inr
          where inr.key_name = pr.key_name) > 1
   ORDER by key_name;

   
   

   
   

4. To delete the duplicate entries execute the command below for all the entries: 

   DELETE FROM plugin_setting WHERE id = plugin_setting_id

   The plugin_setting_id  is gotten from the query in step 3.

Step 3:

1. Reindex all the tables using the command below: 

   REINDEX TABLE sta_normal_user;
   
   REINDEX TABLE project;
   
   REINDEX TABLE plugin_setting;

2. Confirm if all duplicates are removed and your instance is fully functional.
3. Perform the same steps on your production instance.