From 0af5f5efaf0b24187514cf78d7982ef9b85a208c Mon Sep 17 00:00:00 2001 From: Erik Johnston Date: Tue, 7 Apr 2015 12:08:35 +0100 Subject: Don't use multiple UNIQUE constraints; it will cause deadlocks --- synapse/storage/schema/full_schemas/11/presence.sql | 10 ++++++---- synapse/storage/schema/full_schemas/11/users.sql | 4 ++-- 2 files changed, 8 insertions(+), 6 deletions(-) (limited to 'synapse/storage/schema') diff --git a/synapse/storage/schema/full_schemas/11/presence.sql b/synapse/storage/schema/full_schemas/11/presence.sql index 273e61281a..00d803a5cd 100644 --- a/synapse/storage/schema/full_schemas/11/presence.sql +++ b/synapse/storage/schema/full_schemas/11/presence.sql @@ -17,20 +17,22 @@ CREATE TABLE IF NOT EXISTS presence( state VARCHAR(20), status_msg VARCHAR(150), mtime BIGINT, -- miliseconds since last state change - UNIQUE(user_id) + UNIQUE (user_id) ) ; -- For each of /my/ users which possibly-remote users are allowed to see their -- presence state CREATE TABLE IF NOT EXISTS presence_allow_inbound( observed_user_id VARCHAR(150) NOT NULL, - observer_user_id VARCHAR(150) -- a UserID, + observer_user_id VARCHAR(150) NOT NULL, -- a UserID, + UNIQUE (observed_user_id, observer_user_id) ) ; -- For each of /my/ users (watcher), which possibly-remote users are they -- watching? CREATE TABLE IF NOT EXISTS presence_list( user_id VARCHAR(150) NOT NULL, - observed_user_id VARCHAR(150), -- a UserID, - accepted BOOLEAN + observed_user_id VARCHAR(150) NOT NULL, -- a UserID, + accepted BOOLEAN NOT NULL, + UNIQUE (user_id, observed_user_id) ) ; diff --git a/synapse/storage/schema/full_schemas/11/users.sql b/synapse/storage/schema/full_schemas/11/users.sql index 0271de3526..ba0f42d455 100644 --- a/synapse/storage/schema/full_schemas/11/users.sql +++ b/synapse/storage/schema/full_schemas/11/users.sql @@ -35,8 +35,8 @@ CREATE TABLE IF NOT EXISTS user_ips ( device_id VARCHAR(150), ip VARCHAR(150) NOT NULL, user_agent VARCHAR(150) NOT NULL, - last_seen BIGINT NOT NULL, - UNIQUE (user, access_token, ip, user_agent) + last_seen BIGINT NOT NULL ) ; CREATE INDEX IF NOT EXISTS user_ips_user ON user_ips(user); +CREATE INDEX IF NOT EXISTS user_ips_user_ip ON user_ips(user, access_token, ip); -- cgit 1.4.1