Fix concurrency handling in PostgreSQL WITH statements

[vxgmichel]

Follow up after #9428 and #9441

The code base incorrectly assumes that PostgreSQL runs WITH statements in order. This is not the case at all.

This is especially a problem when concurrency is handled by locking topics or other rows using FOR SHARE and FOR UPDATE.

In general, we cannot assume that the data selected in a query that does a FOR SHARE/FOR UPDATE is read after the lock is taken, unless the data comes from the very row AND table being locked. In practice, the opposite happens creating race conditions such as the ones explained in #9428.

For instance, consider a query like this one:

WITH locked_realm AS MATERIALIZED (
    SELECT realm._id, last_timestamp
    FROM realm_topic
    INNER JOIN realm ON realm._id = realm_topic.realm
    WHERE realm_id = $1
    FOR UPDATE OF realm_topic
)
SELECT realm._id, key_index, last_timestamp
FROM locked_realm
INNER JOIN realm ON locked_realm._id = realm._id

One could assume that postgre will first run the subquery locking the realm before reading the key index in the other subquery. In practice, the opposite happen if two of such queries run concurrently (i.e the key index returned will be outdated).

The obvious fix is to run the locking requests in their own queries, and only then use the locked id to perform a complementary read query. This has been done on #9441, but a full sweep of the postgre queries in the code base should be done to apply the same approach to other topics, invitations, etc.

Note that there might be other places where the locking is done implicitly by inserting or updating, but the problem still remains.