Mandate everything runs in some fixed locale en_US.UTF8

[saihaj]

We should mandate that everything runs in some fixed locale (maybe en_US.UTF8, though we'd need to look into if there are other locales that are more palatable to the non-English world) graph-node could then check on startup that its locale and the locale of the database are the expected one.

Originally posted by @lutter in #1837 (comment)

[saihaj]

I search the codebase and I see this comment

graph-node/store/postgres/src/connection_pool.rs

Lines 589 to 594 in 85ac00c

	// This check will break if users run Postgres (or even graph-node)
	// in a locale other than English. In that case, their database will
	// be marked as unavailable even though it is perfectly fine.
	if msg.contains("canceling statement")
	|| msg.contains("no connection to the server")
	|| msg.contains("terminating connection due to conflict with recovery")

which means non english DBs shouldn't work right cause they get marked as unavailable. That means we don't need to panic or anything? cc @lutter

[azf20]

@saihaj I wonder if we can add a specific check for en_US.UTF8? I think for determinism it's not enough for it to be in English, I think it needs to be specific

[leoyvens]

The POSIX locale is better for performance, so it seems worth discussing if we should choose that.

[lutter]

This discussion is a good start. One thing to note is that switching collation would require rebuilding all indexes with text columns in them.

[azf20]

The majority of indexers responding to the straw poll are running en_US.UTF8. Proposing that we adopt that as the standard on the network, but @lutter am wondering what the migration path is for any indexers currently running a different locale? I don't know what the implications are - ideally it would be as simple as a reconfiguration, but presumably indices would need to be regenerated?

[lutter]

The 'right' choice would be to use the C locale because it performs better in queries etc. than en_US.UTF8 (I am told that it can speed up comparisons by 10x)

Either way, the safest way to change locale is to recreate the database - the default locale is baked in when the database is initialized. That means the indexers would have to dump their database, recreate it, and restore from backup. There might be ways for us to change that when generating queries, but it'll require some more work within graph-node

[azf20]

OK - so they wouldn't need to re-index, just restore from backup? Interested in @fordN's opinion from an indexer perspective here?

[saihaj]

#4163