Log the state group and also check state_group_edges.state_group

This should ensure that if we delete a state group, we delete it from
all other tables.

changelog.d/18626.misc

@@ -0,0 +1 @@
+Add constraint on `state_groups_state` when deleting.

synapse/storage/schema/state/delta/92/08_no_empty_state_groups.sql.postgres

@@ -0,0 +1,65 @@
+--
+-- This file is licensed under the Affero General Public License (AGPL) version 3.
+--
+-- Copyright (C) 2025 New Vector, Ltd
+--
+-- This program is free software: you can redistribute it and/or modify
+-- it under the terms of the GNU Affero General Public License as
+-- published by the Free Software Foundation, either version 3 of the
+-- License, or (at your option) any later version.
+--
+-- See the GNU Affero General Public License for more details:
+-- <https://www.gnu.org/licenses/agpl-3.0.html>.
+
+-- Add a "constraint" on deleting rows from state_groups_state. This is to
+-- ensure that we don't delete a state group that is still referenced by
+-- state_group_edges or state_groups.
+CREATE FUNCTION check_state_groups_state_deletion() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+            BEGIN
+                IF EXISTS (
+                    SELECT 1 FROM state_group_edges
+                    WHERE
+                        OLD.state_group = state_group_edges.prev_state_group
+                        -- Check that the state group is fully deleted, rather
+                        -- than deleted/reinserted
+                        AND NOT EXISTS (
+                            SELECT state_group FROM state_groups_state
+                            WHERE state_group = OLD.state_group
+                        )
+                ) THEN
+                    RAISE EXCEPTION 'Deleting state_groups_state row when it still exists in state_groups_edges: prev_state_group = %%', OLD.state_group;
+                END IF;
+                IF EXISTS (
+                    SELECT 1 FROM state_group_edges
+                    WHERE
+                        OLD.state_group = state_group_edges.state_group
+                        -- Check that the state group is fully deleted, rather
+                        -- than deleted/reinserted
+                        AND NOT EXISTS (
+                            SELECT state_group FROM state_groups_state
+                            WHERE state_group = OLD.state_group
+                        )
+                ) THEN
+                    RAISE EXCEPTION 'Deleting state_groups_state row when it still exists in state_groups_edges: state_group = %%', OLD.state_group;
+                END IF;
+                IF EXISTS (
+                    SELECT 1 FROM state_groups
+                    WHERE
+                        OLD.state_group = state_groups.id
+                        AND NOT EXISTS (
+                            SELECT state_group FROM state_groups_state
+                            WHERE state_group = OLD.state_group
+                        )
+                ) THEN
+                    RAISE EXCEPTION 'Deleting state_groups_state row when it still exists in state_groups: id = %%', OLD.state_group;
+                END IF;
+                RETURN NULL;
+            END;
+            $$;
+
+CREATE CONSTRAINT TRIGGER check_state_groups_state_deletion_trigger AFTER DELETE ON state_groups_state
+    DEFERRABLE INITIALLY DEFERRED
+    FOR EACH ROW
+    EXECUTE PROCEDURE check_state_groups_state_deletion()