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

7 files changed, 19 insertions, 4 deletions

diff --git a/synapse/storage/schema/delta/16/room_alias_index.sql b/synapse/storage/schema/delta/16/room_alias_index.sql
new file mode 100644
index 0000000000..f82486132b
--- /dev/null
+++ b/synapse/storage/schema/delta/16/room_alias_index.sql
@@ -0,0 +1,3 @@
+
+CREATE INDEX IF NOT EXISTS room_aliases_id ON room_aliases(room_id);
+CREATE INDEX IF NOT EXISTS room_alias_servers_alias ON room_alias_servers(room_alias);
diff --git a/synapse/storage/schema/delta/16/unique_constraints.sql b/synapse/storage/schema/delta/16/unique_constraints.sql
index 3604ea8427..fecf11118c 100644
--- a/synapse/storage/schema/delta/16/unique_constraints.sql
+++ b/synapse/storage/schema/delta/16/unique_constraints.sql
@@ -70,3 +70,11 @@ DROP INDEX IF EXISTS presence_list_observers;
 CREATE UNIQUE INDEX presence_list_observers ON presence_list(
     user_id, observed_user_id
 );
+
+--
+DELETE FROM room_aliases WHERE rowid not in (
+    SELECT MIN(rowid) FROM room_aliases GROUP BY room_alias
+);
+
+DROP INDEX IF EXISTS room_aliases_id;
+CREATE INDEX room_aliases_id ON room_aliases(room_id);
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
 );