summary refs log tree commit diff
path: root/synapse/storage
diff options
context:
space:
mode:
authorDaniel Sonck <daniel@sonck.nl>2022-01-17 12:42:51 +0100
committerGitHub <noreply@github.com>2022-01-17 11:42:51 +0000
commit6b241f5286f05d4d8dc0569e90388713a956d038 (patch)
treeb79ff27fc7864456285183a11eb4fa3b654d555c /synapse/storage
parentMerge branch 'release-v1.50' into develop (diff)
downloadsynapse-6b241f5286f05d4d8dc0569e90388713a956d038.tar.xz
Make pagination of rooms in admin api stable (#11737)
Always add state.room_id after the configurable ORDER BY. Otherwise,
for any sort, certain pages can contain results from
other pages. (Especially when sorting by creator, since there may
be many rooms by the same creator)

* Document different order direction of numerical fields

"joined_members", "joined_local_members", "version" and "state_events"
are ordered in descending direction by default (dir=f). Added a note
in tests to explain the differences in ordering.

Signed-off-by: Daniƫl Sonck <daniel@sonck.nl>
Diffstat (limited to 'synapse/storage')
-rw-r--r--synapse/storage/databases/main/room.py18
1 files changed, 9 insertions, 9 deletions
diff --git a/synapse/storage/databases/main/room.py b/synapse/storage/databases/main/room.py
index c0e837854a..95167116c9 100644
--- a/synapse/storage/databases/main/room.py
+++ b/synapse/storage/databases/main/room.py
@@ -551,24 +551,24 @@ class RoomWorkerStore(CacheInvalidationWorkerStore):
             FROM room_stats_state state
             INNER JOIN room_stats_current curr USING (room_id)
             INNER JOIN rooms USING (room_id)
-            %s
-            ORDER BY %s %s
+            {where}
+            ORDER BY {order_by} {direction}, state.room_id {direction}
             LIMIT ?
             OFFSET ?
-        """ % (
-            where_statement,
-            order_by_column,
-            "ASC" if order_by_asc else "DESC",
+        """.format(
+            where=where_statement,
+            order_by=order_by_column,
+            direction="ASC" if order_by_asc else "DESC",
         )
 
         # Use a nested SELECT statement as SQL can't count(*) with an OFFSET
         count_sql = """
             SELECT count(*) FROM (
               SELECT room_id FROM room_stats_state state
-              %s
+              {where}
             ) AS get_room_ids
-        """ % (
-            where_statement,
+        """.format(
+            where=where_statement,
         )
 
         def _get_rooms_paginate_txn(