Now working on a backport for the mod-inventory-storage hotfix.

@Julian Ladisch @Adam Dickmeiss

We have discussed that the the solution with performing two releases of mod-inventory-storage (can we any module really) is the way to go. It means:

1. we will make an RMB release that only fixes the index definitions (moves the functions to local schema and perform creation, we will have a seperate ticket for the CONCURENTLY creation)

2. we will make a hotfix mod-inventory-storage release off the Fameflower branch that includes only the index recreation fix (the deadlines for hotfixes is April 27) so that DevOps can choose to apply it for Fameflower

3. Goldenrod releases will also include the fix and if DevOps does not choose to apply the hotfix, Fameflower -> Goldenrod will be slower

As far as the new API for doing a pre-upgrade is concerned – we are delaying this for now.

During the module upgrade we create/recreate indexes without CONCURRENTLY. This is faster but prevents any write access to the underlying data. Quote from https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY :
"CONCURRENTLY: When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done."

Needs a comment from @Adam Dickmeiss or @Jakub Skoczen

My ideas how to do the index recreation:

Create a patch release for the old release that is in production.
This patch adds the non-public functions and adds an index creation endpoint that creates new indexes needed for the upcoming release. The sysOp can invoke it at a suitable time, for example at night, so that the additional load caused by index creation does not affect daily work.
These new indexes are not used but get updated when the underlying data changes.
When upgrading to the new release the upgrading code detects that the new indexes are already there and uses them without any recreation.

Implementation ideas:
The index is created using CREATE INDEX CONCURRENTLY: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY There is no downtime because write transactions are allowed during index creation.
The create endpoint immediately returns, the index is created asynchronously.
There is a GET endpoint to get the status of the new index: missing, in progress, successfully created.
Each index creation can be started separately.
Append _new to the name of the new index, for example instance_title_idx_new. Put index information into rmb_internal_index after successful index creation.
On module upgrade check if rmb_internal_index contains an instance_title_idx_new entry with the required expression. If yes, drop instance_title_idx and rename instance_title_idx_new to instance_title_idx, and update rmb_internal_index accordingly.