Ensure we don't time out background index creation

[erikjohnston]

We added a global timeout in #15853, which might break such (expected) long running queries.

[reivilibre]

As far as I know, this should be overridable at the transaction level with SET LOCAL statement_timeout. Shouldn't be a difficult fix but just needs someone to research that this actually works and add the exact incantation.

[H-Shay]

So looking into this SET LOCAL statement_timeout should work - the only caveat is that the SET LOCAL statement needs to be executed first rather than as part of the expected long-running statement, i.e.

        def test_set_local_tx(txn) -> None:
            sql = "SET LOCAL statement_timeout = 1"
            txn.execute(sql)
            more_sql = "SELECT pg_sleep(10)"
            txn.execute(more_sql)

        self.get_success(self.db_pool.runInteraction("", test_set_local_tx))

does the right thing and results in a psycopg2.errors.QueryCanceled: canceling statement due to statement timeout error but

        def test_set_local_tx(txn) -> None:
            sql = "SET LOCAL statement_timeout = 1; SELECT pg_sleep(10)"
            txn.execute(sql)

does not and merrily sleeps away. SET LOCAL statement_timeout = 0 is the incantation to disable the timeout for the next statement.
Should we put this in the documentation somewhere so people know to use this when creating a potentially long-running index?

[erikjohnston]

@H-Shay I'd be tempted to set it automatically for all background index updates, i.e. somewhere around

synapse/synapse/storage/background_updates.py

Line 748 in 84ae2e3

conn.set_session(autocommit=True) # type: ignore