An Attempt At Postgres Support In Moodle Elis 2.6

ELIS doesn’t currently support Postgres, which is kind of annoying. As well as annoying its a little concerning. So I decided to use a little of my spare time to see what is going on.

A big part of the reason why applications often run into problems with Postgres is because its so much more strict than other databases. MySql essentially says “yeah, that’s probably fine” and lets your slightly malformed queries slide. Not Postgres though. If your query isn’t completely correct it throws an error and you’re done.

That means that errors in Postgres aren’t just a case of an unsupported database, they can indicate undetected problems with your queries. Something is wrong and you just haven’t noticed it yet. Its a big part of the reason why I do my Moodle development in Postgres these days.

Often these problems occur in large complex queries but here is a simple illustration of a common type of error that Postgres flushes out.

Select columnA, columnB
From tableName
Group By columnA

The problem here is that you are selecting two columns, one grouped, one coming from individual rows. Theoretically you could get several values for columnB squished into a single columnA grouped row so what exactly is the database meant to return you?

Typically there will be a join condition or where condition which, in practice, probably ensures that each grouped columnA only has a single value of columnB but you can’t be entirely sure. The simple solution is this…

Select columnA, columnB
From tableName
Group By columnA, columnB

Postgres forces you to go through your queries and think about whether this solution works for you or whether there is something fundamentally wrong with the query. Large queries, particularly those involving grouping, can easily contain subtle errors that can go undetected for quite some time.

So what did I do to make ELIS work with Postgres? All I’ve done is gone through ELIS attempting to flush out and fix the various errors I encountered by making the simplest fix possible.

Note that I don’t claim to have any real knowledge of the (often complex) queries involved. I have made naive fixes that resolve the error but someone with a deeper knowledge of ELIS needs to review and test these fixes.

Some of the fixes will likely be fine. Some of the queries potentially need to be substantially rewritten. I don’t currently possess sufficient knowledge of ELIS to determine which is which.

Standard caveats apply. Do not apply these fixes to a production site. I cannot offer any kind of guarantee that these changes won’t subtly alter the data retrieved by the various queries or even outright break your site.

Ok, all of that said, here are the changes for consideration by whoever considers such things.

https://github.com/andyjdavis/moodle-local_elisprogram/compare/MOODLE_26_STABLE…postgres

https://github.com/andyjdavis/moodle-local_elisreports/compare/MOODLE_26_STABLE…postgres

Leave a Reply