Update user_filters table to have a unique index, and non-null columns

[pik]

Swaps the user_filter Table index to Unique, the current code does not use a single transaction or optimistic locking.

https://github.com/matrix-org/synapse/blob/master/synapse/storage/filtering.py#L43

Not sure on the procedure for PRing schema changes, so let me know if I'm missing something.

[matrixbot]

Can one of the admins verify this patch?

[matrixbot]

Can one of the admins verify this patch?

[matrixbot]

Can one of the admins verify this patch?

[matrixbot]

Can one of the admins verify this patch?

[matrixbot]

Can one of the admins verify this patch?

[erikjohnston]

@pik The way the schema upgrade stuff works is that we create a new "version" after every release, so creating a new 37 is the correct thing to do here since 36 is the latest in the release. You will need to bump the SCHEMA_VERSION in synapse/storage/prepare_database.py when bumping the version.

@matrixbot ok to test

[erikjohnston]

@matrixbot retest this please

[erikjohnston]

Hmm, this might require some more thought given its going to break starting synapse if it has duplicate filter ids.

[pik]

Yeah probably needs to come with a pre-migration 'clean-up-task'. I will update.

[pik]

Ah, apologies, it seems what I am asking about already exists (still navigating my way around the code-base). Updated.

[richvdh]

I have no idea why this has been ignored for 3 years.