search

totara / totara-docker-dev

A docker setup to create a development environment for Totara Learn
MIT License
49 stars 32 forks source link

Adding postgres 14 support #201

Closed codyfinegan closed 2 years ago

codyfinegan commented 2 years ago

We need to disable memoize for postgres 14 in Totara

codyfinegan commented 2 years ago

@derschatta - Could you take a look at this, what we need to get pg14 behaving

derschatta commented 2 years ago

looks good to me. Can you give a bit of background in the description of this PR about this change? Why is it necessary?

codyfinegan commented 2 years ago

looks good to me. Can you give a bit of background in the description of this PR about this change? Why is it necessary?

Yep. 14 brought in memoization of subqueries (which is turned on by default). In theory it should speed queries up.

Unfortunately, it's a bit buggy and several of our complicated queries don't behave with it (the one I know for sure is the visibility lookup queries which are huge & complicated). By buggy I mean the exact same query against the exact same data returns two different results.

Rewriting those isn't really a feasible option right now, so we're requiring memoization be disabled for pg14.

You can see it in practice if you try running PHPUnit with it enabled against 14 (default state), a ton of tests will fail. But with disabled it's clean.

When I get some time I'm going to try and come up with a smaller sensible failing example & lodge a ticket with Postgres about it.