1package migrations 2 3import migrate "github.com/rubenv/sql-migrate" 4 5func init() { 6 m := &migrate.Migration{ 7 Id: "20210525173505_valid_primaries_view", 8 Up: []string{` 9CREATE VIEW repository_generations AS 10 SELECT virtual_storage, relative_path, MAX(generation) AS generation 11 FROM storage_repositories 12 GROUP BY virtual_storage, relative_path 13 `, 14 ` 15CREATE VIEW valid_primaries AS 16 WITH candidates AS ( 17 SELECT virtual_storage, relative_path, storage, repository_assignments.storage IS NOT NULL AS assigned 18 FROM storage_repositories 19 JOIN repository_generations USING (virtual_storage, relative_path, generation) 20 JOIN healthy_storages USING (virtual_storage, storage) 21 LEFT JOIN repository_assignments USING (virtual_storage, relative_path, storage) 22 WHERE NOT EXISTS ( 23 -- This check exists to prevent us from electing a primary that is pending deletion. The primary 24 -- could accept a write and lose it when the deletion is carried out. 25 SELECT true 26 FROM replication_queue 27 WHERE state NOT IN ('completed', 'dead', 'cancelled') 28 AND job->>'change' = 'delete_replica' 29 AND job->>'virtual_storage' = virtual_storage 30 AND job->>'relative_path' = relative_path 31 AND job->>'target_node_storage' = storage 32 ) 33 ) 34 35 SELECT virtual_storage, relative_path, storage 36 FROM candidates 37 WHERE assigned OR ( 38 SELECT NOT EXISTS ( 39 SELECT FROM candidates AS assigned_candidates 40 WHERE assigned 41 AND assigned_candidates.virtual_storage = candidates.virtual_storage 42 AND assigned_candidates.relative_path = candidates.relative_path 43 ) 44 ) 45 `}, 46 Down: []string{ 47 "DROP VIEW valid_primaries", 48 "DROP VIEW repository_generations", 49 }, 50 } 51 52 allMigrations = append(allMigrations, m) 53} 54