Repository navigation
Add PostgreSQL support #255
Description
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:
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.
Metadata
Metadata
Assignees
Labels
No labels
Activity
ormsbee commentedon Nov 5, 2024
@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 commentedon Nov 7, 2024
Thank you @ormsbee for sharing this with me. I am going to take care of it.
qasimgulzar commentedon Nov 13, 2024
@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 commentedon Nov 13, 2024
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 commentedon Nov 14, 2024
There were two primary motivations for
MultiCollationCharField:1. To force
utf8mb4encoding, even in databases that were still configured forutf8mb3.Up until Redwood, the Tutor default database connection was
utf8, which in MySQL translated into theirutf8mb3encoding, 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 viautf8mb4.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
MultiCollationCharFieldin 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 commentedon Nov 18, 2024
Related Pull Requests
FreeSynergy commentedon May 16, 2025
Can we use Postgres now? Is this issue fixed?
qasimgulzar commentedon May 16, 2025
There is an other PR waiting to merge jn edx-platfrom after that you will be able to provision with postgresql
AntonOfTheWoods commentedon Oct 26, 2025
@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.
TextFieldorCharField, and installing the extension.citextis apparently now not the preferred solution - the django column typeCICharFieldwas removed in django v5.en-x-icu(or similar) collations. They are built-in if the database was created with theiculocale 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.CreateCollationin 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,
likeis not supported for the now-preferrednon-deterministiccollation 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 commentedon Oct 27, 2025
If either of you (@ormsbee , @qasimgulzar ) have a moment for comment, #422
There were a couple of bad assumptions above ie,
en-x-icuactually 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.