Trial tests against postgres 14 are failing since #11635

[DMRobertson]

Latest deps: https://github.com/matrix-org/synapse/actions/runs/3326877285/jobs/5501039194
Develop: https://github.com/matrix-org/synapse/actions/runs/3323214952/jobs/5493346783

[FAIL]
Traceback (most recent call last):
  File "/home/runner/work/synapse/synapse/tests/storage/test_room_search.py", line 372, in test_postgres_web_search_for_phrase
    self._check_test_cases(store, self.COMMON_CASES + self.POSTGRES_CASES, index=0)
  File "/home/runner/work/synapse/synapse/tests/storage/test_room_search.py", line 323, in _check_test_cases
    self.assertEquals(
  File "/home/runner/.cache/pypoetry/virtualenvs/matrix-synapse-pswDeSvb-py3.10/lib/python3.10/site-packages/twisted/trial/_synctest.py", line 424, in assertEqual
    super().assertEqual(first, second, msg)
  File "/opt/hostedtoolcache/Python/3.10.8/x64/lib/python3.10/unittest/case.py", line 845, in assertEqual
    assertion_func(first, second, msg=msg)
  File "/opt/hostedtoolcache/Python/3.10.8/x64/lib/python3.10/unittest/case.py", line 838, in _baseAssertEqual
    raise self.failureException(msg)
twisted.trial.unittest.FailTest: 0 != 1 : expected '"fox quick' to match 'the quick brown fox jumps over the lazy dog'

tests.storage.test_room_search.MessageSearchTest.test_postgres_web_search_for_phrase

From #11635.

[DMRobertson]

I'm assuming that test is to blame---but tests did pass before this was merged in. So not sure what's going on.

[DMRobertson]

This didn't happen on the twisted trial job. Do we have a new flakey test---some kind of race?

[DMRobertson]

Reproduce with

docker run --rm -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -e POSTGRES_DB=synapse -p 5432:5432 postgres:14-alpine &
SYNAPSE_POSTGRES=1 SYNAPSE_POSTGRES_HOST=127.0.0.1 SYNAPSE_POSTGRES_USER=postgres SYNAPSE_POSTGRES_PASSWORD=postgres poetry run trial tests.storage.test_room_search.MessageSearchTest.test_postgres_web_search_for_phrase

I tried postgres versions 10--13. On each of these, the test succeeds. I wonder if the FTS behaviour has changed in PG 14?

[clokep]

I'll take a look, thanks for filing! 🎉

[clokep]

Starting in Postgres 14 the behavior of a hanging double quote was changed; it used to ignore the double quote while now it treats it like a phrase.

In PostgreSQL 11 - 13:

postgres=# SELECT websearch_to_tsquery('english', '"fox quick');
 websearch_to_tsquery 
----------------------
 'fox' & 'quick'
(1 row)

In PostgreSQL 14:

postgres=# SELECT websearch_to_tsquery('english', '"fox quick');
 websearch_to_tsquery 
----------------------
 'fox' <-> 'quick'
(1 row)

---

Not sure what the perfect fix is for this...

[clokep]

See the release notes for psql 14 (and search for tsquery).