Adding indexes to the core database

As OpenLMIS is being used on multiple databases, one for each implementation, we do not have a full knowledge of how indexes look like in every country. They can differ not only in name, but also in columns the index is applied on.

For instance, some implementation can have an index on status_messages.requisitionid named idx_status_messages_requisitionid, so when we simply try to use CREATE INDEX IF NOT EXISTS..., it will create duplicated index, as the one with provided name does not exist yet.

That is why we came up with the solution to create a generic function for adding such indexes.
It is available here: openlmis-requisition/src/main/resources/db/migration/20240314174732705__add_requisition_indexes.sql at master · OpenLMIS/openlmis-requisition

The function takes three parameters:

schema name
table name
column the index is about to be applied on (separated by commas, without whitespaces, sorted alphabetically)

In lines 45-48 indexes are being defined. Index on two columns would look like this:
SELECT * FROM addindexesifnotexist('requisition', 'status_messages', 'requisitionid,statuschangeid');

At the end the freshly added function is being dropped, as it is not often used.

-- This script allows to add indexes to core database without possibility to add duplicated one, with two different names but on the same columns.
CREATE FUNCTION addindexesifnotexist(schema_parameter VARCHAR, table_parameter VARCHAR, indexed_parameter VARCHAR)
RETURNS void AS
$$
DECLARE index_exists BOOLEAN;
DECLARE new_index_name VARCHAR;
DECLARE comma_replaced_indexed_parameter VARCHAR;

BEGIN
	SELECT EXISTS (
        SELECT 1
        FROM (
            SELECT
                n.nspname AS schema_name,
                t.relname AS table_name,
                i.relname AS index_name,
                array_to_string(array_agg(a.attname ORDER BY a.attname), ',') AS indexed_columns
            FROM pg_class t
            JOIN pg_index ix ON t.oid = ix.indrelid
            JOIN pg_class i ON i.oid = ix.indexrelid
            JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
            JOIN pg_namespace n ON n.oid = t.relnamespace
            WHERE t.relkind = 'r'  -- regular tables (relations)
            GROUP BY
                n.nspname,
                t.relname,
                i.relname
        ) AS existing_indexes_info
        WHERE schema_name = schema_parameter
	    AND table_name = table_parameter
	    AND indexed_columns = indexed_parameter
    ) INTO index_exists;

   SELECT REPLACE(indexed_parameter, ',', '_') INTO comma_replaced_indexed_parameter;
   SELECT CONCAT(table_parameter, '_', comma_replaced_indexed_parameter, '_idx') INTO new_index_name;

   IF NOT index_exists THEN
    EXECUTE format('CREATE INDEX %I ON %I.%I (%s)', new_index_name, schema_parameter, table_parameter, indexed_parameter);
   END IF;

END; $$ LANGUAGE plpgsql;


-- do not insert whitespaces in parameters, variables in the third argument must be sorted alfabetically
SELECT * FROM addindexesifnotexist('requisition', 'status_messages', 'requisitionid');
SELECT * FROM addindexesifnotexist('requisition', 'requisitions_previous_requisitions', 'previousrequisitionid');
SELECT * FROM addindexesifnotexist('requisition', 'stock_adjustments', 'requisitionlineitemid');
SELECT * FROM addindexesifnotexist('requisition', 'stock_adjustment_reasons', 'requisitionid');


DROP FUNCTION addindexesifnotexist(varchar, varchar, varchar);