Add PostgreSQL support

[ormsbee]

This repo currently has a set of collation-related helpers that it uses to help normalize model collation behavior between MySQL and SQLite:

• https://github.com/openedx/openedx-learning/blob/main/openedx_learning/lib/collations.py
• https://github.com/openedx/openedx-learning/blob/main/openedx_learning/lib/fields.py

They manifest in the migrations files like this:

openedx-learning/openedx_learning/apps/authoring/publishing/migrations/0001_initial.py

Lines 28 to 29 in 14284d2

	('key', openedx_learning.lib.fields.MultiCollationCharField(db_collations={'mysql': 'utf8mb4_bin', 'sqlite': 'BINARY'}, max_length=500)),
	('title', openedx_learning.lib.fields.MultiCollationCharField(db_collations={'mysql': 'utf8mb4_unicode_ci', 'sqlite': 'NOCASE'}, max_length=500)),

The MultiCollationCharField is also used in one place in edx-platform

Anyone looking to add PostgreSQL support would need to look for all instances of MultiCollationCharField and MultiCollationTextField and put the postgres-specific mappings there. That includes the convenience methods like case_insensitive_char_field in fields.py, as well as in the individual migration files that have the sqlite/mysql arguments serialized out.

[ormsbee]

@qasimgulzar: These are some of the specifics that have to happen for this repo to support PostgreSQL. Thank you for taking this work on.

[qasimgulzar]

Thank you @ormsbee for sharing this with me. I am going to take care of it.

[qasimgulzar]

@ormsbee could you please add some details of the background of this field, e.g: why this field is there any why is it required to be MultiCollationCharField. It will be very helpful while implementing and testing the change.

[qasimgulzar]

Update.-.1.1.-.min.mov

@ormsbee I have managed to run edx-platform with postgresql. I think it's going to bring some joy here.

[ormsbee]

There were two primary motivations for MultiCollationCharField:

1. To force utf8mb4 encoding, even in databases that were still configured for utf8mb3.

Up until Redwood, the Tutor default database connection was utf8, which in MySQL translated into their utf8mb3 encoding, which lacks support for emojis and certain other Unicode code points that require 4 bytes to encode in UTF8. Many people had already upgraded their instances through their own scripts and configuration, and some instances that didn't use Tutor were already connecting via utf8mb4.

In the middle of this weird, transitional period, we were creating new data models to store library (and eventually course) content, and we wanted to make sure that the generated tables could properly store 4-byte characters no matter what the database connection settings were for that particular instance at the time.

2. To normalize case sensitivity behavior for certain fields.

We have fields where case-sensitivity is important, like identifier keys. We've gotten into issues with this sort of mismatch in the past, where CourseOverviews store their course keys in a case insensitive manner, but Modulestore stores them in a case sensitive manner, making collision possible (e.g. two courses in Modulestore that map to the same CourseOverview). We also run many of our tests using in-memory sqlite, which is case sensitive by default–as opposed to MySQL's default case insensitivity.

Given that, it's convenient to have something like MultiCollationCharField in order to force consistent constraint-checking and ordering behavior between the two. That way, models can declare that, "this should be a case-insensitive char field" and get consistent behavior across databases, without the error-prone process of altering collations manually in the migration file.

[qasimgulzar]

Related Pull Requests

1. edx-when
2. edx-enterprise
3. xblock-lti-consumer
4. edx-milestones
5. openedx-learning
6. edx-platform

[Lord-KalEl]

Can we use Postgres now? Is this issue fixed?

[qasimgulzar]

There is an other PR waiting to merge jn edx-platfrom after that you will be able to provision with postgresql

[AntonOfTheWoods]

@ormsbee and @qasimgulzar I've been investigating this. Collation issues have already wasted in excess of 6 calendar months of my life... Sigh.

There appear to be various options, none of which are entirely satisfactory.

• using the CITEXT postgres extension for a new CI column type - requires using a separate django class instead of TextField or CharField, and installing the extension. citext is apparently now not the preferred solution - the django column type CICharField was removed in django v5.
• using the "built-in" en-x-icu (or similar) collations. They are built-in if the database was created with the icu locale provider which (afaict) is the default for pg14+ but isn't the default for Cloudnative Postgres (which is what I am using, for example), so it might not be the default apart from the vanilla distro.
• creating a collation with the provided django class CreateCollation in the migrations. This requires icu support to be available (regardless of how the db was created) but from what I can tell it is everywhere by default from pg14+.

The problem with both options 2 and 3 is that prior to pg18, like is not supported for the now-preferred non-deterministic collation types one creates.

I personally am fine with saying "openedx requires pg18+" but Qasim (and others) might not be. Are there potentially lots of areas where there will be likes on case-insensitive columns? I guess most of the actual "search" is done where it should be - in a proper search engine (elastic, meili...), so most of the pure db stuff would be =?

Any thoughts @ormsbee or @qasimgulzar ?

[AntonOfTheWoods]

If either of you (@ormsbee , @qasimgulzar ) have a moment for comment, #422

There were a couple of bad assumptions above ie, en-x-icu actually is available, but it's not case-insensitive, and there aren't any built-in case-insensitive (all the built-ins are "deterministic"), and the other that "non-deterministic" collations (so all the case-insensitive ones) can't be used at all in indexes. I put some discussion of my findings as a comment on the PR. Basically I found a single field that was trying to add an index to a case-insensitive field, and I couldn't find anywhere where that (non-unique) index was actually used. So given there is only one, the idea would be to move to something that doesn't accept indexes declared like that, and for the future require explicit indexes get declared.