sql: support set client_encoding='LATIN1'

[genofire]

maintainer's note from @jordanlewis
We're interested in feedback on this. If you need support for other client encodings like LATIN1 please upvote this issue or #35882.

BUG REPORT

• What did you do?
  Try to use it in postfix.

postmap -q "user@domain.de" pgsql:/etc/postfix/virtual_alias_maps.cf

• What did you expect to see?
  No output, but exitcode 1.

• What did you see instead?

postmap: warning: dict_pgsql: cannot set the encoding to LATIN1, skipping node1:26257
postmap: fatal: table pgsql:/etc/postfix/virtual_alias_maps.cf: query error: Success

Jira issue: CRDB-5933

[mberhault]

CC: @dianasaur323

[dianasaur323]

hmmm... @awoods187 probably has a better view on this

[awoods187]

CC @vivekmenezes

[vivekmenezes]

@genofire how important is this to you? Thank you for you interest in cockroachDB.

[genofire]

I would like to run cockroachdb on a test system with just some domains with following three software.

• postfix/dovecot (this bug)
• gitea (not working yet)
• grafana (not working yet too)
• i own developed software with gorm

So i thought the implementation of LATIN1 support is easyier done then the support of gitea and grafana (with xorm).

I really like cockroach and his for clustering (with multi master) - thank you for developing 👍
It look nice.

So i am maybe the only which use cockroach with postfix and dovecot (and need LATIN1 support).
You are free to decide if a old ASCII encoding is needed.

[knz]

@genofire I had a look into this. Would be acceptable to configure postfix and dovecot to use UTF-8 instead? It sounds to me that UTF-8 is a more desirable setting anyway given the international nature of email.

[MEschenbacher]

I'm running into the same issue with the debian buster postfix version 3.4.8.

[knz]

Did you try utf-8?

[MEschenbacher]

Currently, there is no stable postfix release, which supports connections to postgres with utf-8 encoding; postfix depends on LATIN1. I tried to force postfix by using a postgres://[...]?client_encoding=utf-8 with no success but the same error

postmap: warning: dict_pgsql: cannot set the encoding to LATIN1

The change for using utf-8 is already committet to the postfix repo but within a #ifdef SNAPSHOT block which suggests it is not yet considered stable for production use.

[GildedHonour]

What does

postmap: fatal: table pgsql:/etc/postfix/virtual_alias_maps.cf: query error: Success

mean? Is it success or failure?

[MEschenbacher]

It means failure. The real error indication is showing one line above where postfix is unable to set the encoding to LATIN1 on the connection to a cockroachdb server.

postmap -q "user@domain.de" pgsql:/etc/postfix/virtual_alias_maps.cf
postmap: warning: dict_pgsql: cannot set the encoding to LATIN1, skipping node1:26257

[GildedHonour]

What's the logic behind of query error: Success to mean failure?

[knz]

The word "Success" is produced by the client trying to find the cause of the error in the libc variable "errno". This variable would contain something meaningful if a system error was encountered, for example a network error. However, this is a logical SQL error, there was no system error. So errno is 0, and the word that describes the system status for this value is "Success".

This is confusing IMHO, the client code should not print out the system error status if errno is observed to be zero. But that's not specific to cockroachdb in any case.

[solongordon]

CockroachDB only supports the UTF-8 character set for the time being. Unfortunately this is unlikely to change in the near future.

[MEschenbacher]

Thank you for the feedback.

[GildedHonour]

Thank you for saying thank you. Thank you for typing the text. Thank you for waking up in the morning. Thank you looking in the monitor and hurting your eyes while typing the text that says "thank you". Thank you for spending time to read what's written in the topic.

[GildedHonour]

Ahhh, I forgot the say - "Thank you"!

[linsomniac]

Would be acceptable to configure postfix and dovecot to use UTF-8 instead? It sounds to me that UTF-8 is a more desirable setting anyway given the international nature of email.

Postfix isn't configurable except by modifying the code.

I believe the reason for choosing LATIN1 is that, realizing it's been a long time since I've poured over the RFCs, mail headers and envelopes tend to be encoded as 7-bit ASCII. The "international nature" is handled by encoding other charsets in 7-bit ASCII. So for the postfix use case, I believe they are saying "rather than asking for UTF-8 and then internally complaining about it if it isn't LATIN1, we are telling the database to use LATIN1".

It's a real pity that there isn't some solution to this, I've been setting up a proof of concept for migrating from MySQL with galera to CockroachDB on our mail cluster, and this looks like it's going to prevent that. Cockroach looks fantastic!

[jordanlewis]

I investigated this a little bit today. It's not clear to me how many people out there need this on CockroachDB - if you do need, please chime in!

If we did want to support this, I think what we'd want to do is add a library like https://github.com/paulrosania/go-charset and use it to convert byte strings during lexing/parsing into UTF8, assuming that they're encoded according to the set client_encoding, and then vice versa convert the UTF8 back into the currently-set client_encoding when sending strings back to the client.

I think it's doable, but not sure if it's worth the effort for us at this time.

[linsomniac]

Thank you for checking into this. I, personally, do not further need it because I've set up our mail cluster to use mariadb+galera cluster, and that's working great. We had previously been using mysql+galera, and it worked well, but was a bit of a chore to set up, that latter was part of why I wanted to try Cockroach. Cockroach was indeed easier to set up when I tried it, until I ran into the encoding issue with Postgres.

[MEschenbacher]

Thank you for checking up on this. I do not need LATIN1 any longer since postfix starting with version 3.8 does no longer hardcode the encoding at LATIN1 but makes is configurable, defaulting to utf8.