BUG: Incorrect working with POSIX locale if database in UTF-8 encoding

From: Олег Самойлов <splarv(at)ya(dot)ru>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: BUG: Incorrect working with POSIX locale if database in UTF-8 encoding
Date: 2018-10-17 09:29:52
Message-ID: 13FE0F7C-5140-499C-8C2E-0BE64BC3A48B@ya.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I think this is a bug, because database encoding logging must work even in this case. The main problem is with pacemaker module pgsqlms, which launch pg_ctl in empty environment and thus with broken logging.
Let me explain on examples.
Empty databases:

-bash-4.2$ psql -p 5433 -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 |
template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

In empty environment:

-bash-4.2$ locale
LANG=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

-bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/krogan0a start
waiting for server to start....2018-10-17 12:04:20.762 MSK [788] ?????????: ??? ?????? ??????????? ?? ?????? IPv4 "0.0.0.0" ?????? ???? 5433
2018-10-17 12:04:20.763 MSK [788] ?????????: ??? ?????? ??????????? ?? ?????? IPv6 "::" ?????? ???? 5433
2018-10-17 12:04:20.770 MSK [788] ?????????: ??? ?????? ??????????? ?????? Unix-????? "/var/run/postgresql/.s.PGSQL.5433"
2018-10-17 12:04:20.784 MSK [788] ?????????: ??? ?????? ??????????? ?????? Unix-????? "/tmp/.s.PGSQL.5433"
2018-10-17 12:04:20.811 MSK [788] ?????????: ???????? ?????? ? ???????? ???????? ????? ??????????
2018-10-17 12:04:20.811 MSK [788] ?????????: ? ?????????? ????????? ????? ?????????? ? ??????? "log".
done

And log file also unreadable:

2018-10-17 11:56:57.328 MSK [579] ?????????: ??????? ?? ???? ?????????: 2018-10-17 11:55:41 MSK
2018-10-17 11:56:57.339 MSK [577] ?????????: ??????? ?? ?????? ????????? ???????????
2018-10-17 12:04:14.754 MSK [577] ?????????: ??????? ?????? ?? ??????? ??????????
2018-10-17 12:04:14.808 MSK [577] ?????????: ?????????? ???? ???????? ??????????
2018-10-17 12:04:14.817 MSK [577] ?????????: ??????? ???????: logical replication launcher (PID 586) ?????????? ? ????? ?????? 1
2018-10-17 12:04:14.817 MSK [580] ?????????: ??????????
2018-10-17 12:04:16.930 MSK [577] ?????????: ??????? ?? ?????????
2018-10-17 12:04:20.821 MSK [790] ?????????: ??????? ?? ???? ?????????: 2018-10-17 12:04:15 MSK
2018-10-17 12:04:20.858 MSK [788] ?????????: ??????? ?? ?????? ????????? ???????????

But in a connection to database all fine:

-bash-4.2$ psql -p 5433
psql (10.5)
Type "help" for help.

postgres=# selectt;
ОШИБКА: ошибка синтаксиса (примерное положение: "selectt")
LINE 1: selectt;
^

And in log file:

2018-10-17 12:05:08.048 MSK [801] ОШИБКА: ошибка синтаксиса (примерное положение: "selectt") (символ 1)
2018-10-17 12:05:08.048 MSK [801] ОПЕРАТОР: selectt;

And on stop:

-bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/krogan0a stop
waiting for server to shut down..... done
server stopped

But log file:

2018-10-17 12:09:10.376 MSK [788] ?????????: ??????? ?????? ?? ??????? ??????????
2018-10-17 12:09:10.384 MSK [788] ?????????: ?????????? ???? ???????? ??????????
2018-10-17 12:09:10.390 MSK [788] ?????????: ??????? ???????: logical replication launcher (PID 797) ?????????? ? ????? ?????? 1
2018-10-17 12:09:10.390 MSK [791] ?????????: ??????????
2018-10-17 12:09:12.365 MSK [788] ?????????: ??????? ?? ?????????

Work around is to set UTF-8 locale or LANGUAGE:

-bash-4.2$ LANG=ru_RU.UTF-8 /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/krogan0a start
ожидание запуска сервера....2018-10-17 12:11:05.508 MSK [951] СООБЩЕНИЕ: для приёма подключений по адресу IPv4 "0.0.0.0" открыт порт 5433
2018-10-17 12:11:05.508 MSK [951] СООБЩЕНИЕ: для приёма подключений по адресу IPv6 "::" открыт порт 5433
2018-10-17 12:11:05.515 MSK [951] СООБЩЕНИЕ: для приёма подключений открыт Unix-сокет "/var/run/postgresql/.s.PGSQL.5433"
2018-10-17 12:11:05.529 MSK [951] СООБЩЕНИЕ: для приёма подключений открыт Unix-сокет "/tmp/.s.PGSQL.5433"
2018-10-17 12:11:05.553 MSK [951] СООБЩЕНИЕ: передача вывода в протокол процессу сбора протоколов
2018-10-17 12:11:05.553 MSK [951] ПОДСКАЗКА: В дальнейшем протоколы будут выводиться в каталог "log".
готово
сервер запущен

log file:

2018-10-17 12:11:05.562 MSK [953] СООБЩЕНИЕ: система БД была выключена: 2018-10-17 12:09:10 MSK
2018-10-17 12:11:05.576 MSK [951] СООБЩЕНИЕ: система БД готова принимать подключения

Or:

-bash-4.2$ LANG=en_US.UTF-8 /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/krogan0a start
waiting for server to start....2018-10-17 12:12:35.182 MSK [1030] СООБЩЕНИЕ: для приёма подключений по адресу IPv4 "0.0.0.0" открыт порт 5433
2018-10-17 12:12:35.182 MSK [1030] СООБЩЕНИЕ: для приёма подключений по адресу IPv6 "::" открыт порт 5433
2018-10-17 12:12:35.189 MSK [1030] СООБЩЕНИЕ: для приёма подключений открыт Unix-сокет "/var/run/postgresql/.s.PGSQL.5433"
2018-10-17 12:12:35.203 MSK [1030] СООБЩЕНИЕ: для приёма подключений открыт Unix-сокет "/tmp/.s.PGSQL.5433"
2018-10-17 12:12:35.229 MSK [1030] СООБЩЕНИЕ: передача вывода в протокол процессу сбора протоколов
2018-10-17 12:12:35.229 MSK [1030] ПОДСКАЗКА: В дальнейшем протоколы будут выводиться в каталог "log".
done
server started

Log file:

2018-10-17 12:12:35.243 MSK [1032] СООБЩЕНИЕ: система БД была выключена: 2018-10-17 12:12:30 MSK
2018-10-17 12:12:35.257 MSK [1030] СООБЩЕНИЕ: система БД готова принимать подключения

What is strange set US locale help to write russian messages. %) Or:

-bash-4.2$ LANGUAGE=english /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/krogan0a start
waiting for server to start....2018-10-17 12:14:51.252 MSK [1106] LOG: listening on IPv4 address "0.0.0.0", port 5433
2018-10-17 12:14:51.252 MSK [1106] LOG: listening on IPv6 address "::", port 5433
2018-10-17 12:14:51.275 MSK [1106] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2018-10-17 12:14:51.358 MSK [1106] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2018-10-17 12:14:51.435 MSK [1106] LOG: redirecting log output to logging collector process
2018-10-17 12:14:51.435 MSK [1106] HINT: Future log output will appear in directory "log".
done
server started

Log file:

2018-10-17 12:14:51.469 MSK [1108] LOG: database system was shut down at 2018-10-17 12:14:33 MSK
2018-10-17 12:14:51.522 MSK [1106] LOG: database system is ready to accept connections

There is not problem with systemctl, if system locale setted by localectl is the same as database locale. But, as I said, there is problem with pacemaker pgsqlms module. And I think this is incorrect behavior. Database may write in log messages in database locale, if this messages arises inside connection to this database. But common messages, such as messages on start cluster or errors on start cluster must written according to locale settings, and if locale empty (POSIX) such messages must be in english. And if messages not in english they must be visible as is, not as ‘???’. :) It works inside connection, why this does not work on start/stop?

-bash-4.2$ LANGUAGE=english /usr/pgsql-10/bin/pg_ctl --version
pg_ctl (PostgreSQL) 10.5

-bash-4.2$ rpm -q postgresql10
postgresql10-10.5-1PGDG.rhel7.x86_64

-bash-4.2$ cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Krishna 2018-10-17 10:46:36 GIN Index for low cardinality
Previous Message Andreas Kretschmer 2018-10-17 06:47:19 Re: Which index is used in the index scan.