Fixes https://github.com/matrix-org/synapse/issues/14465

As part of the database migration to support threaded receipts, there is
a possible window in between
`73/08thread_receipts_non_null.sql.postgres` removing the original
unique constraints on `receipts_linearized` and `receipts_graph` and the
`reeipts_linearized_unique_index` and `receipts_graph_unique_index`
background updates from `72/08thread_receipts.sql` completing where
the unique constraints on `receipts_linearized` and `receipts_graph` are
missing. Any emulated upserts on these tables must therefore be
performed with a lock held, otherwise duplicate rows can end up in the
tables when there are concurrent emulated upserts. Fix the missing lock.

Note that emulated upserts no longer happen by default on sqlite, since
the minimum supported version of sqlite supports native upserts by
default now.

Finally, clean up any duplicate receipts that may have crept in before
trying to create the `receipts_graph_unique_index` and
`receipts_linearized_unique_index` unique indexes.

Signed-off-by: Sean Quah <seanq@matrix.org>

This reverts commit 36097e88.

We don't filter state usually, so doing so here is a waste of time. This is not much of an issue for clients that enable lazy loading of members, since there will be fewer state events.

* Pull out hero selection logic

* Include heroes in partial join response's state

* Changelog

* Fixup trial test

* Remove TODO

* Fix typechecking errors introduced in #14128

* Changelog

* Correct annotations

so that context_factory works if you don't use TLS

* Fix missing SSL support in worker endpoints.

* Add changelog

* SSL for Replication endpoint

* Remove unit test change

* Refactor listener creation to reduce duplicated code

* Fix the logger message

* Update synapse/app/_base.py

Co-authored-by: Patrick Cloke <clokep@users.noreply.github.com>

* Update synapse/app/_base.py

Co-authored-by: Patrick Cloke <clokep@users.noreply.github.com>

* Update synapse/app/_base.py

Co-authored-by: Patrick Cloke <clokep@users.noreply.github.com>

* Add config documentation for new TLS option

Co-authored-by: Tuomas Ojamies <tojamies@palantir.com>
Co-authored-by: Patrick Cloke <clokep@users.noreply.github.com>
Co-authored-by: Olivier Wilkinson (reivilibre) <oliverw@matrix.org>

Update docstring to clarify that `get_partial_state_events_batch` does not just give you completely arbitrary partial-state events. (#14417)

* Include eturnal TURN server configuration example

and moving specific configuration examples into sub folders.

* Update docs/turn-howto.md

Co-authored-by: Dirk Klimpel <5740567+dklimpel@users.noreply.github.com>

* Update docs/setup/turn/coturn.md

Co-authored-by: Dirk Klimpel <5740567+dklimpel@users.noreply.github.com>

* Update docs/setup/turn/eturnal.md

Co-authored-by: Dirk Klimpel <5740567+dklimpel@users.noreply.github.com>

* Fix TURN relaying public IP address hint

* lint eturnal installation commands

* Adjust synapse setup to link to existing documentation

..avoid redundant information.

* remove redundant text

* include alpine linux package link

* Create 14293.doc

* Update 14293.doc

add missing dot

* Update docs/setup/turn/eturnal.md

Co-authored-by: reivilibre <olivier@librepush.net>

* Update docs/setup/turn/eturnal.md

Co-authored-by: reivilibre <olivier@librepush.net>

* Update docs/setup/turn/coturn.md

Co-authored-by: Moritz Dietz <moritzdietz@users.noreply.github.com>

* Update docs/setup/turn/coturn.md

Co-authored-by: Moritz Dietz <moritzdietz@users.noreply.github.com>

* Update docs/setup/turn/coturn.md

Co-authored-by: Moritz Dietz <moritzdietz@users.noreply.github.com>

* Update docs/setup/turn/eturnal.md

Co-authored-by: reivilibre <olivier@librepush.net>

* Update docs/setup/turn/coturn.md

Co-authored-by: Moritz Dietz <moritzdietz@users.noreply.github.com>

* Update docs/setup/turn/coturn.md

Co-authored-by: Moritz Dietz <moritzdietz@users.noreply.github.com>

* Update eturnal.md to link to official documentation

... and to simplify some aspects

* Adjust coturn to link to default prefix

* Mention eturnalctl location

* Update docs/turn-howto.md

Co-authored-by: Saarko <sandomir@tutanotal.com>
Co-authored-by: Dirk Klimpel <5740567+dklimpel@users.noreply.github.com>
Co-authored-by: reivilibre <olivier@librepush.net>
Co-authored-by: Moritz Dietz <moritzdietz@users.noreply.github.com>

This matches the multi instance writer ID generator class which can
both handle advancing the current token over replication and by calling
the database.

* Bump jsonschema from 4.16.0 to 4.17.0

Bumps [jsonschema](https://github.com/python-jsonschema/jsonschema) from 4.16.0 to 4.17.0.
- [Release notes](https://github.com/python-jsonschema/jsonschema/releases)
- [Changelog](https://github.com/python-jsonschema/jsonschema/blob/main/CHANGELOG.rst)
- [Commits](https://github.com/python-jsonschema/jsonschema/compare/v4.16.0...v4.17.0

)

---
updated-dependencies:
- dependency-name: jsonschema
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump flake8-comprehensions from 3.8.0 to 3.10.1

Bumps [flake8-comprehensions](https://github.com/adamchainz/flake8-comprehensions) from 3.8.0 to 3.10.1.
- [Release notes](https://github.com/adamchainz/flake8-comprehensions/releases)
- [Changelog](https://github.com/adamchainz/flake8-comprehensions/blob/main/HISTORY.rst)
- [Commits](https://github.com/adamchainz/flake8-comprehensions/compare/3.8.0...3.10.1

)

---
updated-dependencies:
- dependency-name: flake8-comprehensions
  dependency-type: direct:development
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump types-pyopenssl from 22.0.10 to 22.1.0.2

Bumps [types-pyopenssl](https://github.com/python/typeshed) from 22.0.10 to 22.1.0.2.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-pyopenssl
  dependency-type: direct:development
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump canonicaljson from 1.6.3 to 1.6.4

Bumps [canonicaljson](https://github.com/matrix-org/python-canonicaljson) from 1.6.3 to 1.6.4.
- [Release notes](https://github.com/matrix-org/python-canonicaljson/releases)
- [Changelog](https://github.com/matrix-org/python-canonicaljson/blob/main/CHANGES.md)
- [Commits](https://github.com/matrix-org/python-canonicaljson/compare/v1.6.3...v1.6.4

)

---
updated-dependencies:
- dependency-name: canonicaljson
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump types-setuptools from 65.5.0.2 to 65.5.0.3

Bumps [types-setuptools](https://github.com/python/typeshed) from 65.5.0.2 to 65.5.0.3.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-setuptools
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

This is to help downstream packagers.

* Remove unused type-ignores

Oversights in #14427 and #14429.

* Changelog

By removing unused variables and making some arguments
required which are always provided.

* Bump gitpython from 3.1.27 to 3.1.29

Bumps [gitpython](https://github.com/gitpython-developers/GitPython) from 3.1.27 to 3.1.29.
- [Release notes](https://github.com/gitpython-developers/GitPython/releases)
- [Changelog](https://github.com/gitpython-developers/GitPython/blob/main/CHANGES)
- [Commits](https://github.com/gitpython-developers/GitPython/compare/3.1.27...3.1.29

)

---
updated-dependencies:
- dependency-name: gitpython
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump types-pyyaml from 6.0.12.1 to 6.0.12.2

Bumps [types-pyyaml](https://github.com/python/typeshed) from 6.0.12.1 to 6.0.12.2.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-pyyaml
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump attrs from 21.4.0 to 22.1.0

Bumps [attrs](https://github.com/python-attrs/attrs) from 21.4.0 to 22.1.0.
- [Release notes](https://github.com/python-attrs/attrs/releases)
- [Changelog](https://github.com/python-attrs/attrs/blob/main/CHANGELOG.rst)
- [Commits](https://github.com/python-attrs/attrs/compare/21.4.0...22.1.0

)

---
updated-dependencies:
- dependency-name: attrs
  dependency-type: direct:production
  update-type: version-update:semver-major
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump flake8 from 4.0.1 to 5.0.4

Bumps [flake8](https://github.com/pycqa/flake8) from 4.0.1 to 5.0.4.
- [Release notes](https://github.com/pycqa/flake8/releases)
- [Commits](https://github.com/pycqa/flake8/compare/4.0.1...5.0.4

)

---
updated-dependencies:
- dependency-name: flake8
  dependency-type: direct:development
  update-type: version-update:semver-major
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump types-jsonschema from 4.17.0.0 to 4.17.0.1

Bumps [types-jsonschema](https://github.com/python/typeshed) from 4.17.0.0 to 4.17.0.1.
- [Release notes](https://github.com/python/typeshed/releases)
- [Commits](https://github.com/python/typeshed/commits

)

---
updated-dependencies:
- dependency-name: types-jsonschema
  dependency-type: direct:development
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump blake2 from 0.10.4 to 0.10.5

Bumps [blake2](https://github.com/RustCrypto/hashes) from 0.10.4 to 0.10.5.
- [Release notes](https://github.com/RustCrypto/hashes/releases)
- [Commits](https://github.com/RustCrypto/hashes/compare/blake2-v0.10.4...blake2-v0.10.5

)

---
updated-dependencies:
- dependency-name: blake2
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump actions/upload-artifact from 2 to 3

Bumps [actions/upload-artifact](https://github.com/actions/upload-artifact) from 2 to 3.
- [Release notes](https://github.com/actions/upload-artifact/releases)
- [Commits](https://github.com/actions/upload-artifact/compare/v2...v3

)

---
updated-dependencies:
- dependency-name: actions/upload-artifact
  dependency-type: direct:production
  update-type: version-update:semver-major
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

* Bump dawidd6/action-download-artifact from 2.24.1 to 2.24.2

Bumps [dawidd6/action-download-artifact](https://github.com/dawidd6/action-download-artifact) from 2.24.1 to 2.24.2.
- [Release notes](https://github.com/dawidd6/action-download-artifact/releases)
- [Commits](https://github.com/dawidd6/action-download-artifact/compare/b12b127cf24433d14b4f93cee62f5465076ba82a...e6e25ac3a2b93187502a8be1ef9e9603afc34925

)

---
updated-dependencies:
- dependency-name: dawidd6/action-download-artifact
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Changelog

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Co-authored-by: GitHub Actions <github-actions[bot]@users.noreply.github.com>

Signed-off-by: Brad Jones <brad@kinksters.dating>

This code was factored out to a method, but also left in-place.

Calling this twice in a row makes no sense: the first call will reduce
the size appropriately, but the loop will immediately exit since the
cache size was already reduced.

PostgreSQL may underestimate the number of distinct `room_id`s in
`event_search`, which can cause it to use table scans for queries for
multiple rooms.

Fix this by setting `n_distinct` on the column.

Resolves #14402.

Signed-off-by: Sean Quah <seanq@matrix.org>