[trafficcontrol] branch master updated: Update to DB migration server_id_primary_key.sql (#5509)

[oc...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

ocket8888 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/trafficcontrol.git


The following commit(s) were added to refs/heads/master by this push:
     new 46b4fd1  Update to DB migration server_id_primary_key.sql (#5509)
46b4fd1 is described below

commit 46b4fd19b449823a7c76dd4bf2fa7b743a2cbde2
Author: Hank Beatty <hb...@users.noreply.github.com>
AuthorDate: Thu Feb 11 18:40:28 2021 -0500

    Update to DB migration server_id_primary_key.sql (#5509)
    
    I was unable to run the dbadmin upgrade because this update was trying to add 2 primary keys (one for each schema).
    
    I encountered this while trying to upgrade from 4.1.1 to 5.0.0.
    
    Co-authored-by: Hank Beatty <hb...@dev4.atl-priv.kabletown.test>
---
 CHANGELOG.md                                                      | 1 +
 .../app/db/migrations/2020082700000000_server_id_primary_key.sql  | 8 ++++----
 2 files changed, 5 insertions(+), 4 deletions(-)

diff --git a/CHANGELOG.md b/CHANGELOG.md
index be19364..4904f23 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -53,6 +53,7 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
 - [#5461](https://github.com/apache/trafficcontrol/issues/5461) - Fixed steering endpoint to be ordered consistently
 - [#5395](https://github.com/apache/trafficcontrol/issues/5395) - Added validation to prevent changing the Type any Cache Group that is in use by a Topology
 - [#5384](https://github.com/apache/trafficcontrol/issues/5384) - New grids will now properly remember the current page number.
+- Fixed and issue with 2020082700000000_server_id_primary_key.sql trying to create multiple primary keys when there are multiple schemas.
 
 ### Changed
 - Refactored the Traffic Ops Go client internals so that all public methods have a consistent behavior/implementation
diff --git a/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql b/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql
index 3e4ed77..7581a11 100644
--- a/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql
+++ b/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql
@@ -18,9 +18,9 @@ DECLARE r record;
 BEGIN
   FOR r IN (SELECT indexname FROM pg_indexes WHERE tablename = 'server' AND indexname LIKE '%primary%')
   LOOP
-    EXECUTE 'ALTER TABLE server DROP CONSTRAINT '|| quote_ident(r.indexname) || ';';
-    EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id);';
+    EXECUTE 'ALTER TABLE server DROP CONSTRAINT IF EXISTS '|| quote_ident(r.indexname) || ';';
   END LOOP;
+  EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id);';
 END
 $$ LANGUAGE plpgsql;
 -- +goose StatementEnd
@@ -33,9 +33,9 @@ DECLARE r record;
 BEGIN
   FOR r IN (SELECT indexname FROM pg_indexes WHERE tablename = 'server' AND indexname LIKE '%primary%')
   LOOP
-    EXECUTE 'ALTER TABLE server DROP CONSTRAINT '|| quote_ident(r.indexname) || ';';
-    EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id, cachegroup, type, status, profile);';
+    EXECUTE 'ALTER TABLE server DROP CONSTRAINT IF EXISTS '|| quote_ident(r.indexname) || ';';
   END LOOP;
+  EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id, cachegroup, type, status, profile);';
 END
 $$ LANGUAGE plpgsql;
 -- +goose StatementEnd