Kolibri: Migration using ALTER and CREATE/UPDATE in one transaction

[benjaoming]

Observed behavior

Upgrades are failing for Postgres in case data is updated after schema alterations in this migration:

https://github.com/learningequality/kolibri/blob/30dcfe13713c21c582cfbb8e91fb375f9f13737a/kolibri/core/device/migrations/0005_auto_20191203_0951.py

Failure is Cannot ALTER TABLE device_devicesettings because it has pending trigger events

More info: https://stackoverflow.com/a/12838113

Secondary issue

We have a test environment run on Postgres, but we are not testing our migrations with data, so we cannot know if they actually work. How do we solve this?

Gherkin?

Expected behavior

User can use 0.12, add something to device_devicesettings and upgrade Kolibri afterwards.

User-facing consequences

TBC

Errors and logs

TBC

Steps to reproduce

n/a

Context

Kolibri 0.12 upgrade to 0.13.0

[ralphiee22]

My PR fixes the issue, but still leaves open the question how to catch these types of errors in the future. A non-automated solution could be gherkins as suggested by @benjaoming, but we should flag them for "risky" migrations. Another one could be more stringent review of PRs that add migrations. As we learn about these types of migration issues and learn how to deal with them, we could create a template of things to check/test for in migrations.

[rtibbles]

I think the simplest automated solution would be to make a custom linting rule that ensures that RunPython operations never occur anywhere but as the last element of migrations (in the case where there are operations other than RunPython)? That should catch the majority of cases, I think.

[benjaoming]

Another one could be more stringent review of PRs that add migrations.

Yes, I have often fantasized about adding linting on migrations, as I don't like when they're called 0123_auto_some_timestamp - names should be verbose etc.

Data migrations and schema migrations should not be mixed, would be great to catch this in linting as well, really good idea @rtibbles ! :+1:

[indirectlylit]

created follow-up issues related to linting:

• https://github.com/learningequality/kolibri/issues/6514 (RunPython behavior)
• https://github.com/learningequality/kolibri/issues/6515 (migration naming)

[rtibbles]

Fixed in #6488

[cpauya]

I confirm that #6488 fixed the issue: specifically the exception during upgrade from v0.12.2 to v0.13.0 using PostgreSQL-9.5.

Here are the steps to reproduce it, I suggest to use a Virtual Machine so you can take snapshots:

Environment

• Ubuntu 16.04.6 Server 64-bit
• PostgreSQL-9.5 -- Install these via Apt:
  
  
  
  • postgresql-9.5 and;
  
  
  • postgresql-server-dev-9.5 -- needed by the Python library psycopg2 so we can use PostgreSQL in Django
  
  
• Pip3 -- Install python3-pip via Apt

Kolibri Installers Used

• v0.12.2
• v0.13.0
• kolibri-buildkite-build-6488-13326-kolibri_0.13.1a0.dev0 -- the assets were built from this PR #6488

Setup PostgreSQL-9.5

Reference: https://www.digitalocean.com/community/tutorials/how-to-use-postgresql-with-your-django-application-on-ubuntu-16-04

Create a Kolibri database and user to be used to access it:

1. Instal PostgreSQL-9.5
   
   
   
   1. sudo apt-get install postgresql-9.5 postgresql-server-dev-9.5
   
   
2. Login interactive Postgres session
   
   
   
   1. sudo -u postgres psql
   
   
3. Create a new database
   
   
   
   1. create database kolibri;
   
   
4. Create a new user
   
   
   
   1. CREATE USER kolibri WITH PASSWORD ‘password’;
   
   
5. Other important steps
   
   
   
   1. ALTER ROLE kolibri SET client_encoding TO 'utf8';
   
   
   2. ALTER ROLE kolibri SET default_transaction_isolation TO 'read committed';
   
   
   3. ALTER ROLE kolibri SET timezone TO 'UTC';
   
   
6. Grant user access rights
   
   
   
   1. GRANT ALL PRIVILEGES ON DATABASE kolibri TO kolibri;
   
   
7. Install psycopg2 by running these:
   
   
   
   1. sudo apt-get install python3-pip
   
   
   2. pip3 install psycopg2
   
   

Reproduce the Issue

1. Install Kolibri v0.12.2, but stop Kolibri after installation without running the setup wizard.
2. Use the Kolibri PostgreSQL database by creating a ~/.kolibri/options.ini file with this content

[Database]
DATABASE_ENGINE=postgres
DATABASE_NAME=kolibri
DATABASE_USER=kolibri 
DATABASE_PASSWORD=password
DATABASE_HOST=127.0.0.1
DATABASE_PORT=5432

1. Run kolibri start -- this will run the migrations and start Kolibri which should now use the Kolibri PostgreSQL database.
2. Stop Kolibri with kolibri stop.
3. Install Kolibri v0.13.0, then run kolibri status afterwards - notice that it shows Kolibri is stopped.
4. Run kolibri start to see the exception:

...
Running migrations:
  Applying device.0005_auto_20191203_0951...Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/kolibri/dist/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ObjectInUse: cannot ALTER TABLE "device_devicesettings" because it has pending trigger events
...

To Verify the Fix

• Follow the steps in the Reproduce the Issue section above but use the kolibri-buildkite-build-6488-13326-kolibri_0.13.1a0.dev0 installer instead.
• Notice that the installation was successful and that running kolibri status afterwards shows the Kolibri URLs where it's running.

Some Notes

1. Tried to use the Kolibri v0.12.5, v0.12.8, and v0.12.9 installers but they were throwing exceptions when I tried to use the PostgreSQL database after a fresh install. Also tried finishing the setup wizard then use PG but it still throws errors. So using PostgreSQL for those versions is broken.

Next Step/s

1. Create Gherkin stories with these steps.
2. Maybe publish a VM with the above setup and Kolibri v0.12.2 installed for quicker testing.

[benjaoming]

@radinamatic note the steps that @cpauya has found for Postgres above.

A little extra note for Postgres stuff: It's possible to assign access to a Postgres user without setting a password for the role, if we use the Peer authentication mechanism for local connections. This will also use a Unix socket which is faster. I mean, it's for when we want to simplify instructions in a tutorial or automated setup for using Postgres.