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