[keyserver] Set last_message correctly for threads
Summary:
ENG-4101
Previously when adding a new membership (e.g. adding someone to a thread) the memberships.last_message column was set to 1 (or 0 if it was supposed to be unread). But after landing D8492 we need the last_message to have a correct value so we now what latest message to fetch.
This really is only an issue for genesis because for other threads there is new message "<user> joined" that sets the last_message to the correct value.
This is an alternative to D8680 which I will abandon if this diff is landed.
Test Plan:
Performance analysis:
- I've run EXPLAIN and ANALYZE operations on this query
- The subqueries are of type LATERAL DERIVED which means db applies lateral derived optimization - it uses the constraints from the outer query to limit the number of searches in the subqueries. This means that each subquery only returns a few rows.
- The type of the queries is ref which according to the documentation is good when the number of matched rows is low (which we get from the previous point)
- We return early when just adding user normally (in this case there will be a "<user> joined a channel" message that will update the last_message). So the query will run very rarely - only when adding a new user to genesis
Tested creating a user:
- The query only runs for this one user and for genesis
- last_message was set to the latest text message and last_read_message remained 0
Changed setNewMembersToUnread in updateThread to false and tried creating a new user again. This time last_read_message was set to the latest text message. On the client the thread was set as "read"
To test the filtering of the private CREATE_SUB_THREAD messages I have changed the default silenceMessages in updateThread to true (so the "<user> joined" message wouldn't override the last_message). Then I added a user to a thread where the latest message is the private CREATE_SUB_THREAD message. User's last_message was set to the message before the private CREATE_SUB_THREAD message.
Reviewers: ashoat, tomek, inka
Reviewed By: ashoat
Subscribers: ashoat, tomek, wyilio
Differential Revision: https://phab.comm.dev/D9598