diff options
| author | Douglas Barbosa Alexandre <dbalexandre@gmail.com> | 2016-12-15 11:42:49 -0200 |
|---|---|---|
| committer | Douglas Barbosa Alexandre <dbalexandre@gmail.com> | 2016-12-15 23:50:16 -0200 |
| commit | d494c9a789799f3625c25273c3bbeb43a93a3b88 (patch) | |
| tree | 702a4bfd1184e709c75a743a6a52b839a8e51a98 | |
| parent | f140ae88c63d0d9976e33eb574702be5e182b851 (diff) | |
| download | gitlab-ce-d494c9a789799f3625c25273c3bbeb43a93a3b88.tar.gz | |
Use optimized query to fill the routes table when running PostgreSQL
| -rw-r--r-- | db/migrate/20161130101252_fill_projects_routes_table.rb | 20 | ||||
| -rw-r--r-- | db/migrate/20161202152031_remove_duplicates_from_routes.rb | 5 |
2 files changed, 19 insertions, 6 deletions
diff --git a/db/migrate/20161130101252_fill_projects_routes_table.rb b/db/migrate/20161130101252_fill_projects_routes_table.rb index 4f3fe7b03a9..56ba6fcdbe3 100644 --- a/db/migrate/20161130101252_fill_projects_routes_table.rb +++ b/db/migrate/20161130101252_fill_projects_routes_table.rb @@ -8,12 +8,20 @@ class FillProjectsRoutesTable < ActiveRecord::Migration DOWNTIME_REASON = 'No new projects should be created during data copy' def up - execute <<-EOF - INSERT INTO routes - (source_id, source_type, path) - (SELECT projects.id, 'Project', concat(namespaces.path, '/', projects.path) FROM projects - INNER JOIN namespaces ON projects.namespace_id = namespaces.id) - EOF + if Gitlab::Database.postgresql? + execute <<-EOF + INSERT INTO routes (source_id, source_type, path) + (SELECT DISTINCT ON (namespaces.path, projects.path) projects.id, 'Project', concat(namespaces.path, '/', projects.path) + FROM projects INNER JOIN namespaces ON projects.namespace_id = namespaces.id + ORDER BY namespaces.path, projects.path, projects.id DESC) + EOF + else + execute <<-EOF + INSERT INTO routes (source_id, source_type, path) + (SELECT projects.id, 'Project', concat(namespaces.path, '/', projects.path) + FROM projects INNER JOIN namespaces ON projects.namespace_id = namespaces.id) + EOF + end end def down diff --git a/db/migrate/20161202152031_remove_duplicates_from_routes.rb b/db/migrate/20161202152031_remove_duplicates_from_routes.rb index 510796e05f2..a21bde69995 100644 --- a/db/migrate/20161202152031_remove_duplicates_from_routes.rb +++ b/db/migrate/20161202152031_remove_duplicates_from_routes.rb @@ -7,6 +7,11 @@ class RemoveDuplicatesFromRoutes < ActiveRecord::Migration DOWNTIME = false def up + # We can skip this migration when running a PostgreSQL database because + # we use an optimized query in the "FillProjectsRoutesTable" migration + # to fill these values that avoid duplicate entries in the routes table. + return unless Gitlab::Database.mysql? + select_all("SELECT path FROM #{quote_table_name(:routes)} GROUP BY path HAVING COUNT(*) > 1").each do |row| path = connection.quote(row['path']) execute(%Q{ |