Add unique index to room_aliases, remove duplicates on upgrade. Convert some columns back to TEXT from bytea

5 files changed, 8 insertions, 4 deletions

diff --git a/synapse/storage/schema/full_schemas/11/transactions.sql b/synapse/storage/schema/full_schemas/11/transactions.sql
index f01d9eba32..524a696920 100644
--- a/synapse/storage/schema/full_schemas/11/transactions.sql
+++ b/synapse/storage/schema/full_schemas/11/transactions.sql
@@ -33,7 +33,7 @@ CREATE TABLE IF NOT EXISTS sent_transactions(
     transaction_id VARCHAR(150),
     destination VARCHAR(150),
     response_code INTEGER DEFAULT 0,
-    response_json bytea,
+    response_json TEXT,
     ts BIGINT
 );
 
diff --git a/synapse/storage/schema/full_schemas/11/users.sql b/synapse/storage/schema/full_schemas/11/users.sql
index 2edc13c3b7..48a6aecfe8 100644
--- a/synapse/storage/schema/full_schemas/11/users.sql
+++ b/synapse/storage/schema/full_schemas/11/users.sql
@@ -35,7 +35,7 @@ CREATE TABLE IF NOT EXISTS user_ips (
     access_token VARCHAR(150) NOT NULL,
     device_id VARCHAR(150),
     ip VARCHAR(150) NOT NULL,
-    user_agent VARCHAR(150) NOT NULL,
+    user_agent TEXT NOT NULL,
     last_seen BIGINT NOT NULL,
     UNIQUE (user, access_token, ip, user_agent)
 );
diff --git a/synapse/storage/schema/full_schemas/16/room_aliases.sql b/synapse/storage/schema/full_schemas/16/room_aliases.sql
index 952cae35b7..2c0853a2a9 100644
--- a/synapse/storage/schema/full_schemas/16/room_aliases.sql
+++ b/synapse/storage/schema/full_schemas/16/room_aliases.sql
@@ -19,7 +19,11 @@ CREATE TABLE IF NOT EXISTS room_aliases(
     UNIQUE (room_alias)
 );
 
+CREATE INDEX room_aliases_id ON room_aliases(room_id);
+
 CREATE TABLE IF NOT EXISTS room_alias_servers(
     room_alias VARCHAR(150) NOT NULL,
     server VARCHAR(150) NOT NULL
 );
+
+CREATE INDEX room_alias_servers_alias ON room_alias_servers(room_alias);
diff --git a/synapse/storage/schema/full_schemas/16/transactions.sql b/synapse/storage/schema/full_schemas/16/transactions.sql
index bc64064936..ed431bd3af 100644
--- a/synapse/storage/schema/full_schemas/16/transactions.sql
+++ b/synapse/storage/schema/full_schemas/16/transactions.sql
@@ -33,7 +33,7 @@ CREATE TABLE IF NOT EXISTS sent_transactions(
     transaction_id VARCHAR(150),
     destination VARCHAR(150),
     response_code INTEGER DEFAULT 0,
-    response_json bytea,
+    response_json TEXT,
     ts BIGINT
 );
 
diff --git a/synapse/storage/schema/full_schemas/16/users.sql b/synapse/storage/schema/full_schemas/16/users.sql
index 006b249fc0..033e3244b5 100644
--- a/synapse/storage/schema/full_schemas/16/users.sql
+++ b/synapse/storage/schema/full_schemas/16/users.sql
@@ -34,7 +34,7 @@ CREATE TABLE IF NOT EXISTS user_ips (
     access_token VARCHAR(150) NOT NULL,
     device_id VARCHAR(150),
     ip VARCHAR(150) NOT NULL,
-    user_agent VARCHAR(150) NOT NULL,
+    user_agent TEXT NOT NULL,
     last_seen BIGINT NOT NULL
 );