diff --git a/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.postgres b/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.postgres
index e1a41be9c9..e1cc3469a4 100644
--- a/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.postgres
+++ b/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.postgres
@@ -99,54 +99,3 @@ CREATE UNIQUE INDEX worker_read_write_locks_write ON worker_read_write_locks (lo
-- constraints.
ALTER TABLE worker_read_write_locks_mode ADD CONSTRAINT worker_read_write_locks_mode_foreign
FOREIGN KEY (lock_name, lock_key, token) REFERENCES worker_read_write_locks(lock_name, lock_key, token) DEFERRABLE INITIALLY DEFERRED;
-
-
--- Add a trigger to UPSERT into `worker_read_write_locks_mode` whenever we try
--- and acquire a lock, i.e. insert into `worker_read_write_locks`,
-CREATE OR REPLACE FUNCTION upsert_read_write_lock_parent() RETURNS trigger AS $$
-BEGIN
- INSERT INTO worker_read_write_locks_mode (lock_name, lock_key, write_lock, token)
- VALUES (NEW.lock_name, NEW.lock_key, NEW.write_lock, NEW.token)
- ON CONFLICT (lock_name, lock_key)
- DO NOTHING;
- RETURN NEW;
-END
-$$
-LANGUAGE plpgsql;
-
-CREATE TRIGGER upsert_read_write_lock_parent_trigger BEFORE INSERT ON worker_read_write_locks
- FOR EACH ROW
- EXECUTE PROCEDURE upsert_read_write_lock_parent();
-
-
--- Ensure that we keep `worker_read_write_locks_mode` up to date whenever a lock
--- is released (i.e. a row deleted from `worker_read_write_locks`). Either we
--- update the `worker_read_write_locks_mode.token` to match another instance
--- that has currently acquired the lock, or we delete the row if nobody has
--- currently acquired a lock.
-CREATE OR REPLACE FUNCTION delete_read_write_lock_parent() RETURNS trigger AS $$
-DECLARE
- new_token TEXT;
-BEGIN
- SELECT token INTO new_token FROM worker_read_write_locks
- WHERE
- lock_name = OLD.lock_name
- AND lock_key = OLD.lock_key;
-
- IF NOT FOUND THEN
- DELETE FROM worker_read_write_locks_mode
- WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key;
- ELSE
- UPDATE worker_read_write_locks_mode
- SET token = new_token
- WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key;
- END IF;
-
- RETURN NEW;
-END
-$$
-LANGUAGE plpgsql;
-
-CREATE TRIGGER delete_read_write_lock_parent_trigger AFTER DELETE ON worker_read_write_locks
- FOR EACH ROW
- EXECUTE PROCEDURE delete_read_write_lock_parent();
diff --git a/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.sqlite b/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.sqlite
index be2dfbbb8a..b15432f576 100644
--- a/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.sqlite
+++ b/synapse/storage/schema/main/delta/78/04_read_write_locks_triggers.sql.sqlite
@@ -70,50 +70,3 @@ CREATE TABLE worker_read_write_locks (
CREATE UNIQUE INDEX worker_read_write_locks_key ON worker_read_write_locks (lock_name, lock_key, token);
-- Ensures that only one instance can acquire a lock in write mode at a time.
CREATE UNIQUE INDEX worker_read_write_locks_write ON worker_read_write_locks (lock_name, lock_key) WHERE write_lock;
-
-
--- Add a trigger to UPSERT into `worker_read_write_locks_mode` whenever we try
--- and acquire a lock, i.e. insert into `worker_read_write_locks`,
-CREATE TRIGGER IF NOT EXISTS upsert_read_write_lock_parent_trigger
-BEFORE INSERT ON worker_read_write_locks
-FOR EACH ROW
-BEGIN
- -- First ensure that `worker_read_write_locks_mode` doesn't have stale
- -- entries in it, as on SQLite we don't have the foreign key constraint to
- -- enforce this.
- DELETE FROM worker_read_write_locks_mode
- WHERE lock_name = NEW.lock_name AND lock_key = NEW.lock_key
- AND NOT EXISTS (
- SELECT 1 FROM worker_read_write_locks
- WHERE lock_name = NEW.lock_name AND lock_key = NEW.lock_key
- );
-
- INSERT INTO worker_read_write_locks_mode (lock_name, lock_key, write_lock, token)
- VALUES (NEW.lock_name, NEW.lock_key, NEW.write_lock, NEW.token)
- ON CONFLICT (lock_name, lock_key)
- DO NOTHING;
-END;
-
--- Ensure that we keep `worker_read_write_locks_mode` up to date whenever a lock
--- is released (i.e. a row deleted from `worker_read_write_locks`). Either we
--- update the `worker_read_write_locks_mode.token` to match another instance
--- that has currently acquired the lock, or we delete the row if nobody has
--- currently acquired a lock.
-CREATE TRIGGER IF NOT EXISTS delete_read_write_lock_parent_trigger
-AFTER DELETE ON worker_read_write_locks
-FOR EACH ROW
-BEGIN
- DELETE FROM worker_read_write_locks_mode
- WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key
- AND NOT EXISTS (
- SELECT 1 FROM worker_read_write_locks
- WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key
- );
-
- UPDATE worker_read_write_locks_mode
- SET token = (
- SELECT token FROM worker_read_write_locks
- WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key
- )
- WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key;
-END;
diff --git a/synapse/storage/schema/main/delta/79/03_read_write_locks_triggers.sql.postgres b/synapse/storage/schema/main/delta/79/03_read_write_locks_triggers.sql.postgres
new file mode 100644
index 0000000000..ea3496ef2d
--- /dev/null
+++ b/synapse/storage/schema/main/delta/79/03_read_write_locks_triggers.sql.postgres
@@ -0,0 +1,69 @@
+/* Copyright 2023 The Matrix.org Foundation C.I.C
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+-- Fix up the triggers that were in `78/04_read_write_locks_triggers.sql`
+
+-- Add a trigger to UPSERT into `worker_read_write_locks_mode` whenever we try
+-- and acquire a lock, i.e. insert into `worker_read_write_locks`,
+CREATE OR REPLACE FUNCTION upsert_read_write_lock_parent() RETURNS trigger AS $$
+BEGIN
+ INSERT INTO worker_read_write_locks_mode (lock_name, lock_key, write_lock, token)
+ VALUES (NEW.lock_name, NEW.lock_key, NEW.write_lock, NEW.token)
+ ON CONFLICT (lock_name, lock_key)
+ DO UPDATE SET write_lock = NEW.write_lock, token = NEW.token;
+ RETURN NEW;
+END
+$$
+LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS upsert_read_write_lock_parent_trigger ON worker_read_write_locks;
+CREATE TRIGGER upsert_read_write_lock_parent_trigger BEFORE INSERT ON worker_read_write_locks
+ FOR EACH ROW
+ EXECUTE PROCEDURE upsert_read_write_lock_parent();
+
+
+-- Ensure that we keep `worker_read_write_locks_mode` up to date whenever a lock
+-- is released (i.e. a row deleted from `worker_read_write_locks`). Either we
+-- update the `worker_read_write_locks_mode.token` to match another instance
+-- that has currently acquired the lock, or we delete the row if nobody has
+-- currently acquired a lock.
+CREATE OR REPLACE FUNCTION delete_read_write_lock_parent() RETURNS trigger AS $$
+DECLARE
+ new_token TEXT;
+BEGIN
+ SELECT token INTO new_token FROM worker_read_write_locks
+ WHERE
+ lock_name = OLD.lock_name
+ AND lock_key = OLD.lock_key
+ LIMIT 1 FOR UPDATE;
+
+ IF NOT FOUND THEN
+ DELETE FROM worker_read_write_locks_mode
+ WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key AND token = OLD.token;
+ ELSE
+ UPDATE worker_read_write_locks_mode
+ SET token = new_token
+ WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key;
+ END IF;
+
+ RETURN NEW;
+END
+$$
+LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS delete_read_write_lock_parent_trigger ON worker_read_write_locks;
+CREATE TRIGGER delete_read_write_lock_parent_trigger AFTER DELETE ON worker_read_write_locks
+ FOR EACH ROW
+ EXECUTE PROCEDURE delete_read_write_lock_parent();
diff --git a/synapse/storage/schema/main/delta/79/03_read_write_locks_triggers.sql.sqlite b/synapse/storage/schema/main/delta/79/03_read_write_locks_triggers.sql.sqlite
new file mode 100644
index 0000000000..acb1a77c80
--- /dev/null
+++ b/synapse/storage/schema/main/delta/79/03_read_write_locks_triggers.sql.sqlite
@@ -0,0 +1,65 @@
+/* Copyright 2023 The Matrix.org Foundation C.I.C
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+-- Fix up the triggers that were in `78/04_read_write_locks_triggers.sql`
+
+-- Add a trigger to UPSERT into `worker_read_write_locks_mode` whenever we try
+-- and acquire a lock, i.e. insert into `worker_read_write_locks`,
+DROP TRIGGER IF EXISTS upsert_read_write_lock_parent_trigger;
+CREATE TRIGGER IF NOT EXISTS upsert_read_write_lock_parent_trigger
+BEFORE INSERT ON worker_read_write_locks
+FOR EACH ROW
+BEGIN
+ -- First ensure that `worker_read_write_locks_mode` doesn't have stale
+ -- entries in it, as on SQLite we don't have the foreign key constraint to
+ -- enforce this.
+ DELETE FROM worker_read_write_locks_mode
+ WHERE lock_name = NEW.lock_name AND lock_key = NEW.lock_key
+ AND NOT EXISTS (
+ SELECT 1 FROM worker_read_write_locks
+ WHERE lock_name = NEW.lock_name AND lock_key = NEW.lock_key
+ );
+
+ INSERT INTO worker_read_write_locks_mode (lock_name, lock_key, write_lock, token)
+ VALUES (NEW.lock_name, NEW.lock_key, NEW.write_lock, NEW.token)
+ ON CONFLICT (lock_name, lock_key)
+ DO UPDATE SET write_lock = NEW.write_lock, token = NEW.token;
+END;
+
+-- Ensure that we keep `worker_read_write_locks_mode` up to date whenever a lock
+-- is released (i.e. a row deleted from `worker_read_write_locks`). Either we
+-- update the `worker_read_write_locks_mode.token` to match another instance
+-- that has currently acquired the lock, or we delete the row if nobody has
+-- currently acquired a lock.
+DROP TRIGGER IF EXISTS delete_read_write_lock_parent_trigger;
+CREATE TRIGGER IF NOT EXISTS delete_read_write_lock_parent_trigger
+AFTER DELETE ON worker_read_write_locks
+FOR EACH ROW
+BEGIN
+ DELETE FROM worker_read_write_locks_mode
+ WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key
+ AND token = OLD.token
+ AND NOT EXISTS (
+ SELECT 1 FROM worker_read_write_locks
+ WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key
+ );
+
+ UPDATE worker_read_write_locks_mode
+ SET token = (
+ SELECT token FROM worker_read_write_locks
+ WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key
+ )
+ WHERE lock_name = OLD.lock_name AND lock_key = OLD.lock_key;
+END;
|
