Hacker News Reader

Comment by btilly | Hacker News Reader

Oh. Locales. The remembered pain.

Save a file in Notepad. Open in vi. See that it is different. Find data in the database, no clue the weird characters were originally supposed to be. And so on and so forth.

I once wrote a reasonable program and sent it as a bug report to the maintainer of the Perl module DBD::File. He sent it as a bug report to BerkeleyDB. They said they never thought about it but yes, that would be silent data corruption with no way to recover. The program? Maintain an address book in a BTree sorted in the current locale. Enter names. Change locale and insert something else. Voila! Lost data with no way to recover!

View on HN

> Oh. Locales. The remembered pain.

More like the ongoing pain.

I had to write the following just this year because SQL Server still defaults to using CP 1252 for text. The culprit? One of those damned stylized quotes that Office loves to insert for you. The code:

    def _wrap_str(value: str):
        try:
            return SqlVarChar(
                value.encode("cp1252")
            )
        except UnicodeEncodeError:
            logging.getLogger("bulk copy").exception(f"value causing error: {value}")
            raise

I would call that Windows pain at this point, not locales.

In the Linux/Postgres world, everything is UTF-8. Which is the default for all internet protocols. Do that and the pain is gone.

Of course Windows doesn't do that...

SQL Server prior to 2019 stores unicode data in UCS-2 (UTF-16 analog, rougly). SQL Server 2019 supports UTF-8.

Linux is not entirely UTF-8, though plenty of people treate it as if is so. Even if on Linux, you might need to consume files from other OS's or other Linux systems with different locales. Once had issues with systems configured with "C" locale vs "en-US" should have been near identical, but enough slight differences to cause failures. Been +10 years, so I dont remember the details.

Windows, the OS, is UTF-16 (or UCS-2 - I forget the details between the two), SQL Server has, for historical reasons, defaulted to CP1252, probably for compatibility with Office components.

But, it's not really a Windows problem, per se, because you have to deal with this issue if you deal with data originated from numerous Windows apps, even if on Linux. Yeah, you can insert a byte order mark (BOM) to indicate UTF-8, but most tools expecting UTF-8 actually dont check for the BOM and blow up in interesting ways if present. Ive seen this far too many times. Enough that anytime I see an encoding error from the likes of Python or Ruby, its an instant recognition (I do a lot ETL work from a number of vendors, so I see a lot of different files "types").

Bugs in the C library string localisation have previously caused a problem in PostgreSQL as well:

https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issu...

Since Postgres 10 they use ICU instead of relying on the C library string routines to give more control.

https://www.2ndquadrant.com/en/blog/icu-support-postgresql-1...

Reply on news.ycombinator.com