Please run the following SQL statements one-at-a-time. They have been designed to minimize the impact on your Sourcegraph instance while they execute.

1. Declare a procedure which, when invoked, will incrementally update any repository that has a NULL entry in its stars column to 0 instead:

   
   

   CREATE OR REPLACE PROCEDURE set_repo_stars_null_to_zero() AS
   $BODY$
   DECLARE
     done boolean;
     total integer = 0;
     updated integer = 0;
   
   BEGIN
     SELECT COUNT(*) INTO total FROM repo WHERE stars IS NULL;
   
     RAISE NOTICE 'repo_stars_null_to_zero: updating % rows', total;
   
     done := total = 0;
   
     WHILE NOT done LOOP
       UPDATE repo SET stars = 0
       FROM (
         SELECT id FROM repo
         WHERE stars IS NULL
         LIMIT 10000
         FOR UPDATE SKIP LOCKED
       ) s
       WHERE repo.id = s.id;
   
       COMMIT;
   
       SELECT COUNT(*) = 0 INTO done FROM repo WHERE stars IS NULL LIMIT 1;
   
       updated := updated + 10000;
   
       RAISE NOTICE 'repo_stars_null_to_zero: updated % of % rows', updated, total;
     END LOOP;
   END
   $BODY$
   LANGUAGE plpgsql;




2. Call the procedure that was declared in the previous step:

   
   

   CALL set_repo_stars_null_to_zero();




3. Change the repo table to make the stars column not nullable:

   
   

   ALTER TABLE repo
     ALTER COLUMN stars SET NOT NULL,
     ALTER COLUMN stars SET DEFAULT 0;