aboutsummaryrefslogtreecommitdiff
path: root/syncapi/storage/postgres
AgeCommit message (Collapse)Author
2022-10-25Filter `/members`, return members at given point (#2827)Till
Makes the tests ``` Can get rooms/{roomId}/members at a given point Can filter rooms/{roomId}/members ``` pass, by moving `/members` and `/joined_members` to the SyncAPI.
2022-10-19Get state deltas without filters (#2810)Till
This makes the following changes: - get state deltas without the user supplied filter, so we can actually "calculate" state transitions - closes `stmt` when using SQLite - Adds presence for users who newly joined a room, even if the syncing user already knows about the presence status (should fix https://github.com/matrix-org/complement/pull/516)
2022-10-13Event relations (#2790)Neil Alexander
This adds support for tracking `m.relates_to`, as well as adding support for the various `/room/{roomID}/relations/...` endpoints to the CS API.
2022-10-04Add indexes that optimise `selectStateInRangeSQL` (#2764)Neil Alexander
This gets rid of some expensive scans on `add_state_ids` and `remove_state_ids`, turning them into much cheaper and faster index scans instead.
2022-10-03Fix fragility of selectEventsWithEventIDsSQL queries (#2757)Ashley Nelson
This fixes a temporary workaround with the `selectEventsWithEventIDsSQL` queries where fields need to be artificially added to the queries so the row results match the format of the `syncapi_output_room_events` table. I made similar functions that accept row results from the `syncapi_current_room_state` table and convert them into StreamEvents without the fields that are specific to output room events. There is also a unit test in the first commit to ensure the resulting behavior doesn't change from the modified queries and functions. Fixes #601. ### Pull Request Checklist <!-- Please read docs/CONTRIBUTING.md before submitting your pull request --> * [x] I have added tests for PR _or_ I have justified why this PR doesn't need tests. * [x] Pull request includes a [sign off](https://github.com/matrix-org/dendrite/blob/main/docs/CONTRIBUTING.md#sign-off) Signed-off-by: `Ashley Nelson <fant@shley.email>` Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-09-30Transactional isolation for `/sync` (#2745)Neil Alexander
This should transactional snapshot isolation for `/sync` etc requests. For now we don't use repeatable read due to some odd test failures with invites.
2022-09-28Consistent `*sql.Tx` usage across sync API (#2744)Neil Alexander
This tidies up the `storage` package so that everything takes a transaction parameter instead of something things that do and some that don't.
2022-09-27Fulltext implementation using Bleve (#2675)Till
Based on #2480 This actually indexes events based on their event type. They are removed from the index if we receive a `m.room.redaction` event on the `OutputRoomEvent` stream. An admin endpoint is added to reindex all existing events. Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-09-27Refactor notifications (#2688)Till
This PR changes the handling of notifications - removes the `StreamEvent` and `ReadUpdate` stream - listens on the `OutputRoomEvent` stream in the UserAPI to inform the SyncAPI about unread notifications - listens on the `OutputReceiptEvent` stream in the UserAPI to set receipts/update notifications - sets the `read_markers` directly from within the internal UserAPI Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-09-27Improve selectRoomIDsWithAnyMembershipSQL performance (#2738)PiotrKozimor
Recently I have observed that dendrite spends a lot of time (~390s) in `selectRoomIDsWithAnyMembershipSQL` query ``` dendrite_syncapi=# select total_exec_time, left(query,100) from pg_stat_statements order by total_exec_time desc limit 5 ; total_exec_time | left --------------------+------------------------------------------------------------------------------------------------------ 747826.5800519128 | SELECT event_id, id, headered_event_json, session_id, exclude_from_sync, transaction_id, history_vis 389130.5490339942 | SELECT DISTINCT room_id, membership FROM syncapi_current_room_state WHERE type = $2 AND state_key = 376104.17514700035 | SELECT psd.datname, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_ 363644.164092031 | SELECT event_type_nid, event_state_key_nid, event_nid FROM roomserver_events WHERE event_nid = ANY($ 58570.48104699995 | SELECT event_id, headered_event_json FROM syncapi_current_room_state WHERE room_id = $1 AND ( $2::te (5 rows) ``` Explain analyze showed correct usage of `syncapi_room_state_unique` index: ``` dendrite_syncapi=# explain analyze SELECT distinct room_id, membership FROM syncapi_current_room_state WHERE type = 'm.room.member' AND state_key = '@qjfl:dendrite.stg.globekeeper.com'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=2749.38..2749.56 rows=24 width=52) (actual time=2.933..2.956 rows=65 loops=1) -> Sort (cost=2749.38..2749.44 rows=24 width=52) (actual time=2.932..2.937 rows=65 loops=1) Sort Key: room_id, membership Sort Method: quicksort Memory: 34kB -> Index Scan using syncapi_room_state_unique on syncapi_current_room_state (cost=0.41..2748.83 rows=24 width=52) (actual time=0.030..2.890 rows=65 loops=1) Index Cond: ((type = 'm.room.member'::text) AND (state_key = '@qjfl:dendrite.stg.globekeeper.com'::text)) Planning Time: 0.140 ms Execution Time: 2.990 ms (8 rows) ``` Multi-column indexes in Postgres shall perform well for leftmost columns, but I gave it a try and created `syncapi_current_room_state_type_state_key_idx` index. I could observe significant performance improvement. Execution time dropped from 2.9 ms to 0.24 ms: ``` explain analyze SELECT distinct room_id, membership FROM syncapi_current_room_state WHERE type = 'm.room.member' AND state_key = '@qjfl:dendrite.stg.globekeeper.com'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=96.46..96.64 rows=24 width=52) (actual time=0.199..0.218 rows=65 loops=1) -> Sort (cost=96.46..96.52 rows=24 width=52) (actual time=0.199..0.202 rows=65 loops=1) Sort Key: room_id, membership Sort Method: quicksort Memory: 34kB -> Bitmap Heap Scan on syncapi_current_room_state (cost=4.53..95.91 rows=24 width=52) (actual time=0.048..0.139 rows=65 loops=1) Recheck Cond: ((type = 'm.room.member'::text) AND (state_key = '@qjfl:dendrite.stg.globekeeper.com'::text)) Heap Blocks: exact=59 -> Bitmap Index Scan on syncapi_current_room_state_type_state_key_idx (cost=0.00..4.53 rows=24 width=0) (actual time=0.037..0.037 rows=65 loops=1) Index Cond: ((type = 'm.room.member'::text) AND (state_key = '@qjfl:dendrite.stg.globekeeper.com'::text)) Planning Time: 0.236 ms Execution Time: 0.242 ms (11 rows) ``` Next improvement is skipping DISTINCT and rely on map assignment in `SelectRoomIDsWithAnyMembership`. Execution time drops by almost half: ``` explain analyze SELECT room_id, membership FROM syncapi_current_room_state WHERE type = 'm.room.member' AND state_key = '@qjfl:dendrite.stg.globekeeper.com'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on syncapi_current_room_state (cost=4.53..95.91 rows=24 width=52) (actual time=0.032..0.113 rows=65 loops=1) Recheck Cond: ((type = 'm.room.member'::text) AND (state_key = '@qjfl:dendrite.stg.globekeeper.com'::text)) Heap Blocks: exact=59 -> Bitmap Index Scan on syncapi_current_room_state_type_state_key_idx (cost=0.00..4.53 rows=24 width=0) (actual time=0.021..0.021 rows=65 loops=1) Index Cond: ((type = 'm.room.member'::text) AND (state_key = '@qjfl:dendrite.stg.globekeeper.com'::text)) Planning Time: 0.087 ms Execution Time: 0.136 ms (7 rows) ``` In our env we spend only 1s on inserting to table, so the write penalty of creating an index should be small. ``` dendrite_syncapi=# select total_exec_time, left(query,100) from pg_stat_statements where query like '%INSERT%syncapi_current_room_state%' order by total_exec_time desc; total_exec_time | left --------------------+------------------------------------------------------------------------------------------------------ 1139.9057619999971 | INSERT INTO syncapi_current_room_state (room_id, event_id, type, sender, contains_url, state_key, he (1 row) ``` This PR does not require test modifications. ### Pull Request Checklist <!-- Please read docs/CONTRIBUTING.md before submitting your pull request --> * [x] I have added added tests for PR _or_ I have justified why this PR doesn't need tests. * [x] Pull request includes a [sign off](https://github.com/matrix-org/dendrite/blob/main/docs/CONTRIBUTING.md#sign-off) Signed-off-by: `Piotr Kozimor <p1996k@gmail.com>`
2022-09-20Remove deleted state logging lines from sync API (they are pointless)Neil Alexander
2022-09-09Optimise `SharedUsers` again by using complete composite indexNeil Alexander
2022-09-09Optimise shared users query in Synx API slightly by removing a potential sortNeil Alexander
2022-09-09More writer fixes in the Sync APINeil Alexander
2022-08-30Index on `syncapi_send_to_device` table (#2684)PiotrKozimor
Introduced index improves select query performance. Example execution time of `selectSendToDeviceMessagesSQL` query dropped from 80 ms to 15 ms. No sytest modifications are required. ### Pull Request Checklist * [x] I have added added tests for PR _or_ I have justified why this PR doesn't need tests. * [x] Pull request includes a [sign off](https://github.com/matrix-org/dendrite/blob/main/docs/CONTRIBUTING.md#sign-off) Signed-off-by: `Piotr Kozimor <p1996k@gmail.com>`
2022-08-16Stop spamming the logs with `StateBetween: ignoring deleted state` event IDsNeil Alexander
2022-08-11Implement history visibility on `/messages`, `/context`, `/sync` (#2511)Till
* Add possibility to set history_visibility and user AccountType * Add new DB queries * Add actual history_visibility changes for /messages * Add passing tests * Extract check function * Cleanup * Cleanup * Fix build on 386 * Move ApplyHistoryVisibilityFilter to internal * Move queries to topology table * Add filtering to /sync and /context Some cleanup * Add passing tests; Remove failing tests :( * Re-add passing tests * Move filtering to own function to avoid duplication * Re-add passing test * Use newly added GMSL HistoryVisibility * Update gomatrixserverlib * Set the visibility when creating events * Default to shared history visibility * Remove unused query * Update history visibility checks to use gmsl Update tests * Remove unused statement * Update migrations to set "correct" history visibility * Add method to fetch the membership at a given event * Tweaks and logging * Use actual internal rsAPI, default to shared visibility in tests * Revert "Move queries to topology table" This reverts commit 4f0d41be9c194a46379796435ce73e79203edbd6. * Remove noise/unneeded code * More cleanup * Try to optimize database requests * Fix imports * PR peview fixes/changes * Move setting history visibility to own migration, be more restrictive * Fix unit tests * Lint * Fix missing entries * Tweaks for incremental syncs * Adapt generic changes Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com> Co-authored-by: kegsay <kegan@matrix.org>
2022-08-05Partly fix notification counts (#2621)Till
* Fix notification query * Also for SQLite * Move tests to whitelist * Revert "Move tests to whitelist" This reverts commit a7d0120019a111ce45a447ba40233d9c101e6e9b.
2022-08-03Fix syncapi shared users query & device lists (#2614)Till
* Fix query issue, only add "changed" users if we actually share a room * Avoid log spam if context is done * Undo changes to filterSharedUsers * Add logging again.. * Fix SQLite shared users query * Change query to include invited users
2022-08-02Delete correct Send-to-Device messages (#2608)Till
* Add send-to-device tests * Update tests, fix message deletion * PR comments
2022-07-25Update database migrations, remove goose (#2264)Till
* Add new db migration * Update migrations Remove goose * Add possibility to test direct upgrades * Try to fix WASM test * Add checks for specific migrations * Remove AddMigration Use WithTransaction Add Dendrite version to table * Fix linter issues * Update tests * Update comments, outdent if * Namespace migrations * Add direct upgrade tests, skipping over one version * Split migrations * Update go version in CI * Fix copy&paste mistake * Use contexts in migrations Co-authored-by: kegsay <kegan@matrix.org> Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-07-18Set historyVisibility in rowsToStreamEventsTill Faelligen
2022-07-18History visibility database changes (#2533)Till
* Add new history_visibility column * Update SQL queries to include history_visibility * Store the history visibilty calculated by the roomserver * Update GMSL * Update migrations * Fix migration * Update GMSL * Fix `go.sum` * Update GMSL to use sql.Scanner & sql.Valuer * Re-order migration/table creation * Update gomatrixserverlib * Add history_visibility column to current_room_state * Fix migrations * Return error instead of Fatal log Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-07-15Use sync API database in `filterSharedUsers` (#2572)Neil Alexander
* Add function to the sync API storage package for filtering shared users * Use the database instead of asking the RS API * Fix unit tests * Fix map handling in `filterSharedUsers`
2022-07-12Minor SendToDevice fix (#2565)Till
* Avoid unnecessary marshalling if sending to the local server * Fix ordering of ToDevice messages * Revive SendToDevice test
2022-05-10Add indexes to `syncapi_output_room_events` table that satisfy the filters ↵Neil Alexander
(#2446)
2022-05-03Global database connection pool (for monolith mode) (#2411)Neil Alexander
* Allow monolith components to share a single database pool * Don't yell about missing connection strings * Rename field * Setup tweaks * Fix panic * Improve configuration checks * Update config * Fix lint errors * Update comments
2022-04-28Only load members of newly joined rooms (#2389)Till
* Only load members of newly joined rooms * Comment that the query is prepared at runtime Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-04-28Limit presence in `/sync` responses (#2394)Till
* Use filter and limit presence count * More limiting * More limiting * Fix unit test * Also limit presence by last_active_ts * Update query, use "from" as the initial lastPos * Get 1000 presence events, they are filtered later Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-04-27Fix #2390 (#2392)Till
Fix duplicate heroes in `/sync` response.
2022-04-27Try that againNeil Alexander
2022-04-27Correct account data position mappingNeil Alexander
2022-04-27Don't create fictitious presence entries (#2381)Neil Alexander
* Don't create fictitious presence entries for users that don't have any * Update whitelist, since that test probably shouldn't be passing * Fix panics
2022-04-26Send all account data on complete sync by defaultNeil Alexander
Squashed commit of the following: commit 0ec8de57261d573a5f88577aa9d7a1174d3999b9 Author: Neil Alexander <neilalexander@users.noreply.github.com> Date: Tue Apr 26 16:56:30 2022 +0100 Select filter onto provided target filter commit da40b6fffbf5737864b223f49900048f557941f9 Author: Neil Alexander <neilalexander@users.noreply.github.com> Date: Tue Apr 26 16:48:00 2022 +0100 Specify other field too commit ffc0b0801f63bb4d3061b6813e3ce5f3b4c8fbcb Author: Neil Alexander <neilalexander@users.noreply.github.com> Date: Tue Apr 26 16:45:44 2022 +0100 Send as much account data as possible during complete sync
2022-04-26Fix account data positionNeil Alexander
2022-04-26Start account data ID from `from`Neil Alexander
2022-04-26Fix account_data not correctly send in a complete sync (#2379)Till
* Return the StreamPosition from the database and not the latest * Fix linter issue
2022-04-26Add heroes to the room summary (#2373)Till
* Implement room summary heroes * Add passing tests * Move MembershipCount to addRoomSummary * Add comments, close Statement
2022-04-13Make `/messages` filterable (#2347)Till
* Make /messages filterable Fix bug when determining if an event contains an URL * Add newly passing test * Fix test
2022-04-11Correctly use provided filters (#2339)Till
* Apply filters correctly * Fix issues; Use prepareWithFilters * Update gmsl & tests * go.mod.. * PR comments
2022-04-09Remove dead code in the sync api (#2341)kegsay
2022-04-08syncapi: add more tests; fix more bugs (#2338)kegsay
* syncapi: add more tests; fix more bugs bugfixes: - The postgres impl of TopologyTable.SelectEventIDsInRange did not use the provided txn - The postgres impl of EventsTable.SelectEvents did not preserve the ordering of the input event IDs in the output events slice - The sqlite impl of EventsTable.SelectEvents did not use a bulk `IN ($1)` query. Added tests: - `TestGetEventsInRangeWithTopologyToken` - `TestOutputRoomEventsTable` - `TestTopologyTable` * -p 1 for now
2022-04-07Add possibility to ignore users (#2329)Till
* Add ignore users * Ignore users in pushrules Add passing tests * Update sytest lists * Store ignore knowledge in the sync API * Fix copyrights Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-04-06Add presence module V2 (#2312)Till
* Syncapi presence * Clientapi http presence handler * Why is this here? * Missing files * FederationAPI presence implementation * Add new presence stream * Pinecone update * Pinecone update * Add passing tests * Make linter happy * Add presence producer * Add presence config option * Set user to unavailable after x minutes * Only set currently_active if online Avoid unneeded presence updates when syncing * Tweaks * Query devices for last_active_ts Fixes & tweaks * Export SharedUsers/SharedUsers * Presence stream in MemoryStorage * Remove status_msg_nil * Fix sytest crashes * Make presence types const and use stringer for it * Change options to allow inbound/outbound presence * Fix option & typo * Update configs Co-authored-by: Neil Alexander <neilalexander@users.noreply.github.com>
2022-04-01Include joined and invite member counts in room summary (#2315)Neil Alexander
* Include joined and invite member counts in room summary This should fix #2314 and also fix the problem where some clients like Element Android, Fluffychat etc would display the wrong member count for a given room. * Improve SQLite query precision * Check existence of state key for membership events
2022-03-29Remove eduserver (#2306)S7evinK
* Move receipt sending to own JetStream producer * Move SendToDevice to producer * Remove most parts of the EDU server * Fix SendToDevice & copyrights * Move structs, cleanup EDU Server traces * Use HeadersOnly subscription * Missing file * Fix linter issues * Move consumers to own files * Rename durable consumer; Consumer cleanup * Docs/config cleanup
2022-03-24Fix lint errorsNeil Alexander
2022-03-21Remove unused partition_offset_table (#2288)S7evinK
2022-03-18Better mapping of stream positions to topological positions in `/messages` ↵Neil Alexander
(#2263) * Convert stream positions into topological positions for both `from` and `to` in `/messages` * Hopefully it works now * Remove unnecessary logging * Return sane values if `StreamToTopologicalPosition` can't work out the right thing to do * Revert logging change * tweaks * Fix `selectEventIDsInRangeASCSQL` * Test `Getting messages going forward is limited for a departed room (SPEC-216)` was passing incorrectly so un-whitelist it
2022-03-17Various bug fixes and tweaks around invites and membershipNeil Alexander
generated by cgit v1.2.3 (git 2.26.2) at 2025-03-23 12:11:33 +0000