Database locale does not seem to be respected

[spapas]

Description

I tried converting a text column containing decimal numbers on a postgresql server with the Greek_Greece.1252 locale. This reported a generic error (as already described in #3111). However after debuggin a bit, I found out the full stacktrace:

Traceback (most recent call last):
  File "C:\progr\py3\mathesar\mathesar\db\columns\operations\alter.py", line 35, in alter_column
    db_conn.execute_msar_func_with_engine(
  File "C:\progr\py3\mathesar\mathesar\db\connection.py", line 19, in execute_msar_func_with_engine
    return conn.execute(
  File "C:\progr\py3\mathesar\venv\lib\site-packages\psycopg\connection.py", line 879, in execute
    raise ex.with_traceback(None)
psycopg.errors.InvalidTextRepresentation: invalid input syntax for type numeric: "10,6"
CONTEXT:  PL/pgSQL function mathesar_types.cast_to_numeric(text) line 26 at RETURN
SQL statement "ALTER TABLE books.birds ALTER COLUMN "Average mass kg" DROP DEFAULT, ALTER COLUMN "Average mass kg" TYPE numeric(6,3) USING mathesar_types.cast_to_numeric("Average mass kg")"
PL/pgSQL function __msar.exec_ddl(text) line 10 at EXECUTE
PL/pgSQL function __msar.exec_ddl(text,anyarray) line 14 at RETURN
SQL statement "SELECT __msar.exec_ddl(
      'ALTER TABLE %s %s',
      __msar.get_relation_name(tab_id),
      msar.process_col_alter_jsonb(tab_id, col_alters)
    )"
PL/pgSQL function msar.alter_columns(oid,jsonb) line 22 at PERFORM

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\progr\py3\mathesar\mathesar\mathesar\api\db\viewsets\columns.py", line 113, in partial_update
    table.alter_column(column_instance._sa_column.column_attnum, serializer.validated_data)
  File "C:\progr\py3\mathesar\mathesar\mathesar\models\base.py", line 424, in alter_column
    result = alter_column(
  File "C:\progr\py3\mathesar\mathesar\db\columns\operations\alter.py", line 47, in alter_column
    raise InvalidTypeError(column_db_name, requested_type)
db.columns.exceptions.InvalidTypeError: ('"Average mass kg"', 'numeric')

This seems a little noisy, however the problem is psycopg.errors.InvalidTextRepresentation: invalid input syntax for type numeric: "10,6"; the text column that I need to convert has the value 10.6. For some reason (probably because of the locale) this is converted to 10,6 and then it breaks.

Please notice that If I change the textual value to 10,6 then the alter table operation will finish without problem.

Also notice that on a different database that has a en_US.UTF-8 locale it works fine.

Expected behavior

I am not sure what should be the expected behavior here. This isn't an easy problem to resolve. However there should be a way for the end (front end) user to understand what is the problem i.e "the text 10.6 cannot be converted to number, make sure that it uses the correct locale" (or something similar).

To Reproduce

Try to set the locale of your database to a locale that uses comma (,) as a decimal separator (most EU countries do that).

Environment

• OS: Windows 10
• Browser: Chrome
• Browser Version: Latest
• Other info: Postgresql 13.4

Additional context