Merge branch 'smh-optimize-dataloss-query-14-2' into '14-2-stable'

Materialize valid_primaries view (14.2)

See merge request gitlab-org/gitaly!4088

3 files changed, 20 insertions, 5 deletions

diff --git a/internal/praefect/datastore/collector.go b/internal/praefect/datastore/collector.go
index 15bc46394..71f145e67 100644
--- a/internal/praefect/datastore/collector.go
+++ b/internal/praefect/datastore/collector.go
@@ -72,6 +72,11 @@ func (c *RepositoryStoreCollector) Collect(ch chan<- prometheus.Metric) {
 // they are either unhealthy or out of date.
 func (c *RepositoryStoreCollector) queryMetrics(ctx context.Context) (map[string]int, error) {
 	rows, err := c.db.QueryContext(ctx, `
+WITH valid_primaries AS MATERIALIZED (
+	SELECT virtual_storage, relative_path
+	FROM valid_primaries
+)
+
 SELECT virtual_storage, COUNT(*)
 FROM repositories
 WHERE NOT EXISTS (
diff --git a/internal/praefect/datastore/repository_store.go b/internal/praefect/datastore/repository_store.go
index 0b53c3444..beffaa2af 100644
--- a/internal/praefect/datastore/repository_store.go
+++ b/internal/praefect/datastore/repository_store.go
@@ -615,6 +615,10 @@ func (rs *PostgresRepositoryStore) GetPartiallyAvailableRepositories(ctx context
 	//    than the assigned ones.
 	//
 	rows, err := rs.db.QueryContext(ctx, `
+WITH valid_primaries AS MATERIALIZED (
+	SELECT * FROM valid_primaries
+)
+
 SELECT
 	json_build_object (
 		'RelativePath', relative_path,
@@ -634,7 +638,7 @@ FROM (
 		relative_path,
 		repositories.primary,
 		storage,
-		repository_generations.generation - COALESCE(storage_repositories.generation, -1) AS behind_by,
+		repositories.generation - COALESCE(storage_repositories.generation, -1) AS behind_by,
 		repository_assignments.storage IS NOT NULL AS assigned,
 		healthy_storages.storage IS NOT NULL AS healthy,
 		valid_primaries.storage IS NOT NULL AS valid_primary
@@ -652,7 +656,6 @@ FROM (
 		)
 	) AS repository_assignments USING (virtual_storage, relative_path, storage)
 	JOIN repositories USING (virtual_storage, relative_path)
-	JOIN repository_generations USING (virtual_storage, relative_path)
 	LEFT JOIN healthy_storages USING (virtual_storage, storage)
 	LEFT JOIN valid_primaries USING (virtual_storage, relative_path, storage)
 	WHERE virtual_storage = $1
diff --git a/internal/praefect/datastore/repository_store_test.go b/internal/praefect/datastore/repository_store_test.go
index 1df2786e9..a8062fff0 100644
--- a/internal/praefect/datastore/repository_store_test.go
+++ b/internal/praefect/datastore/repository_store_test.go
@@ -1037,10 +1037,17 @@ func TestPostgresRepositoryStore_GetPartiallyAvailableRepositories(t *testing.T)
 			})
 
 			if !tc.nonExistentRepository {
+				var maxGeneration int
+				for _, generation := range tc.existingGenerations {
+					if generation > maxGeneration {
+						maxGeneration = generation
+					}
+				}
+
 				_, err := tx.ExecContext(ctx, `
-							INSERT INTO repositories (virtual_storage, relative_path, "primary")
-							VALUES ('virtual-storage', 'relative-path', 'repository-primary')
-						`)
+							INSERT INTO repositories (virtual_storage, relative_path, "primary", generation)
+							VALUES ('virtual-storage', 'relative-path', 'repository-primary', $1)
+						`, maxGeneration)
 				require.NoError(t, err)
 			}