sql-migrate: Update storage_repositories tablesh-fix-link-repository-id-migration-for-14-4

The batch update query introduced to mitigate limitation
of the PostgreSQL on amount of payload size that can be
send by the NOTIFY function was missing a condition in
the update statements. Because of that the payload contained
changes not for the N storage-repository entries, but for
(N * num_of_storages) storage-repository entries. So initial
size of 150 becomes 450 if 3 storages used.

The change also includes significantly reduced batch size.
The calculation was done on the test data similar to the
production used data. The approximate payload size for
single row is about 470 bytes. As max payload size is 8k bytes
we are allowed to use no more than 16~17 entries. To be more
realistic we reduce it to 14.

Closes: https://gitlab.com/gitlab-org/gitaly/-/issues/3806

Changelog: fixed

1 files changed, 7 insertions, 6 deletions

diff --git a/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go b/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go
index 15deb0fe2..8ee84d968 100644
--- a/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go
+++ b/internal/praefect/datastore/migrations/20210906145021_link_repository_id.go
@@ -16,21 +16,22 @@ DO $BODY$
                 UPDATE storage_repositories
                 SET repository_id = sub.repository_id
                 FROM (
-                    SELECT storage_repositories.virtual_storage, storage_repositories.relative_path, repositories.repository_id
+                    SELECT storage_repositories.virtual_storage, storage_repositories.storage, storage_repositories.relative_path, repositories.repository_id
                     FROM storage_repositories JOIN repositories USING (virtual_storage, relative_path)
                     WHERE storage_repositories.repository_id IS NULL
-                    LIMIT 150
+                    LIMIT 14
                 ) AS sub
-                WHERE   storage_repositories.virtual_storage = sub.virtual_storage
-                    AND storage_repositories.relative_path   = sub.relative_path
-                RETURNING storage_repositories.repository_id
+                    WHERE   storage_repositories.virtual_storage = sub.virtual_storage
+                        AND storage_repositories.storage         = sub.storage
+                        AND storage_repositories.relative_path   = sub.relative_path
+                    RETURNING storage_repositories.repository_id
             )
             SELECT COUNT(*) INTO count_val FROM updated_rows;
             EXIT WHEN count_val = 0;
         END LOOP;
     END
 $BODY$
-LANGUAGE plpgsql;
+LANGUAGE plpgsql
 -- +migrate StatementEnd`,
 			`
 UPDATE repository_assignments