2

I logged into one of our production databases today and tried listing the databases using \l and got:

ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5

Since I am not touching any content, merely metadata on databases, I am not sure where to begin to attack this. Other than the superfluous similar error, I do not see how answers to similar questions are directly of help. If I were to guess, I would think that one of the names of the databases included chars not representable using utf8.

Clues?

Debugging info

Running Postgresql version 10.4 64bit on Windows Server 2016.

mydb=> SHOW client_encoding;
 client_encoding
-----------------
 WIN1252

mydb=> SHOW server_encoding;
 server_encoding
-----------------
 UTF8

mydb=> SELECT datname::bytea, encoding FROM pg_database;
       datname        | encoding
----------------------+----------
 \x706f737467726573   |        6
 \x74656d706c61746531 |        6
 \x74656d706c61746530 |        6
 \x6e696d626c656462   |        6
3
  • 1
    No... what do you get for SELECT datname::bytea, encoding FROM pg_database;? What for SHOW client_encoding; and SHOW server_encoding;? What is your PostgreSQL version? Feb 1 at 12:58
  • @LaurenzAlbe Added the info to the question.
    – oligofren
    Feb 1 at 13:58
  • This tip on querying the internal pg_database and using the raw byte encoding was the tip I needed to find the culprit!
    – oligofren
    Feb 2 at 13:02
3

Quick fix: change client encoding to match content

I got this working by changing the client encoding to match the server encoding. Changing the client encoding did the trick, as it prevented Postgres from encoding utf8 text when the encoding was cp1252. (I have no idea what the Collate stuff is.)

mydb=> \encoding
WIN1252

mydb=> \l
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5 0x6c 0x5f

mydb=> \encoding utf8;

mydb=> \l
                                                      List of databases
   Name    |    Owner    | Encoding |           Collate           |            Ctype            |      Access privileges
-----------+-------------+----------+-----------------------------+-----------------------------+-----------------------------
 mydb      |     myadmin | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =Tc/myadmin            +
           |             |          |                             |                             | myadmin=CTc/myadmin+
           |             |          |                             |                             | my=c/myadmin
 postgres  | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 |
 template0 | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =c/postgres                +
           |             |          |                             |                             | postgres=CTc/postgres
 template1 | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =c/postgres                +
           |             |          |                             |                             | postgres=CTc/postgres

Why (an excursion into raw byte territory)

This took a while to dig into, but I used the tip from the comments section that \l is actually equivalent to a SQL query on the pg_catalog.pg_database. By switching which columns to list I was able to find the column that was proving problematic and by showing it as raw bytes I could pinpoint exactly where:

psql session where I identify the issue

So it was the datcollate column that was proving problematic. According to the docs, this column is of type name and is LC_COLLATE for this database. Obviously there is something that escapes the eye, since there is nothing in the UTF8 encoded output that is "non-ascii", but the output is a bit weird, since the locale name has some chars which seems to have disappeared: the "å" character in "Norwegian Bokml_Norway.1252" is missing (should be "...Bokmål..."). Is it just a coincidence?

Let's compare outputs of standard ascii with the output above:

$ echo -n Bokml | hexdump -C
00000000  42 6f 6b 6d 6c                                    |Bokml|
00000005

If you compare this with the raw output above you see that the psql output has an additional byte: e5 between 6d and 6c. This is not showing.

So this is how å is encoded in my UTF8 terminal:

$ echo -n Bokmål | hexdump -C
00000000  42 6f 6b 6d c3 a5 6c                              |Bokm..l|
00000007

$ printf "%b" "\xc3\xa5"
å

So å uses two bytes (c3a5) in UTF8, while above it is just one byte. Can it be å in WIN1252?

$ printf "m%b" "\xe5" 
m�

$ printf "%b" "\xe5" |  iconv -f cp1252 -t utf8 
å

Indeed, it was! So the problem is that the locale name is encoded in the internal postgres database as WIN1252, which seems to not be handled correctly when the server encoding is set to UTF8, as the client seems to assume that the data it receives will be all valid utf8.

Bonus: VIEW query issue fixed

Coincidentally, this also fixed another, related issue I was fixing, which happened when trying to list contents from a VIEW that contained UTF8 Norwegian characters:

mydb=> select * from conversation_message limit 20;
ERROR:  character with byte sequence 0xf0 0x9f 0x91 0x8d in encoding "UTF8" has no equivalent in encoding "WIN1252"
5
  • Wait a minute. Your question was about \l, right? With a different table, I can easily see how you get that error. Feb 1 at 14:22
  • @LaurenzAlbe indeed it was! The problem was present in both cases and solved by the same fix, but you are absolutely right I should have posted the output from the listing of databases. I will do that as well, although I could not see any special chars in that output that could explain the behaviour.
    – oligofren
    Feb 2 at 11:42
  • But that is a different error message: you should also change the question title. Now that is easy to understand. Feb 2 at 11:46
  • @LaurenzAlbe I updated the question to remove ambiguity, but I will further edit the question and the answer to remove references to the table query. It is as you say, somewhat confusing to have two different issues described.
    – oligofren
    Feb 2 at 11:48
  • @LaurenzAlbe I debugged this to the end using your tip on the internal postgres table and found the real reason for why this fails. Added everything. Thanks for the tip!
    – oligofren
    Feb 2 at 13:01
2

Your collation Norwegian Bokmål_Norway.1252 comes from the operating system (Windows) and is taken as it is. Now Windows uses the Windows-1252 encoding, where å is code point 226, in hexadecimal E5.

This string is taken into pg_database.datcollate as it is, but PostgreSQL expects the name to be in UTF-8, which causes the error.

PostgreSQL has to sanitize that string , so this is a PostgreSQL bug (though, admittedly, Windows is to blame for changing its locale names from version to version).

This special case was actually fixed in 2019, so if you had used a recent bugfix level of PostgreSQL, you could have avoided the problem.

A patch has been proposed to deal with problems like that on a more general basis, but it never got committed because nobody on Windows tested it. You could push this on the -hackers list, ideally be testing the patch (but that requires building PostgreSQL on Windows, which requires some amount of setup).

1
  • That is great background info. Thank you! Ha, and I also see that you were not joking around when you said "this special case"; it was exactly this string that caused issues :-)
    – oligofren
    Feb 4 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.