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.

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.