allow_unsafe_locale: true still enforces the check on postgres' encoding

[matrixbot]

This issue has been migrated from #13709.

---

Description

allow_unsafe_locale still fails to work when a non-C locale is supplied.

Steps to reproduce

• Run Synapse with YugabyteDB as the database
• Observe it fails to work because Yugabyte storage actually does not support C collation

Homeserver

local test setup

Synapse Version

1.66.0

Installation Method

Docker (matrixdotorg/synapse)

Platform

Yugabyte is also from the latest docker.

Relevant log output

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/synapse/app/homeserver.py", line 382, in setup
    hs.setup()
  File "/usr/local/lib/python3.9/site-packages/synapse/server.py", line 308, in setup
    self.datastores = Databases(self.DATASTORE_CLASS, self)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__
    prepare_database(
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 152, in prepare_database
    _setup_new_database(cur, database_engine, databases=databases)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 213, in _setup_new_database
    database_engine.check_new_database(cur)
  File "/usr/local/lib/python3.9/site-packages/synapse/storage/engines/postgres.py", line 137, in check_new_database
    raise IncorrectDatabaseSetup(
synapse.storage.engines._base.IncorrectDatabaseSetup: Database is incorrectly configured:

    - 'CTYPE' is set to 'en_US.UTF-8'. Should be 'C'

See docs/postgres.md for more information.
******************************************************
 Error during initialisation:
    Database is incorrectly configured:
 
     - 'CTYPE' is set to 'en_US.UTF-8'. Should be 'C'
 
 See docs/postgres.md for more information.
 There may be more information in the logs.
******************************************************

Anything else that would be useful to know?

No response

[ctr49]

Apparently unsafe_locale isn't the only limitation for Yugabyte. Even when skipping the CTYPE check, https://github.com/element-hq/synapse/blob/master/synapse/storage/schema/state/full_schemas/72/full.sql.postgres#L23 leads to

psycopg2.errors.FeatureNotSupported: ALTER TABLE ALTER column not supported yet
LINE 23: ALTER TABLE ONLY state_groups_state ALTER COLUMN state_group...
                                             ^
HINT:  See https://github.com/yugabyte/yugabyte-db/issues/1124. React with thumbs up to raise its priority

(see yugabyte/yugabyte-db#1124 (comment))