Postgresql create index concurrently

indexpostgresql

I want to create a concurrently index in postgresql inside a trigger like following code

    CREATE OR REPLACE FUNCTION hour_production_index_partition(partition_name text) RETURNS VOID AS
$BODY$
BEGIN
    -- Ensure we have all the necessary indices in this partition;
    EXECUTE 'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || partition_name || '_domain_write_date_idx ON ' || partition_name || ' (fk_domain, write_date)';
    END;
$BODY$
LANGUAGE plpgsql;

The problem is that when I execute that statement, postgresql complains sending an error like

WARN SqlExceptionHelper – SQL Error: 0, SQLState: 25001 ERROR
SqlExceptionHelper – ERROR: CREATE INDEX CONCURRENTLY cannot run
inside a transaction block Where: SQL statement "CREATE INDEX
CONCURRENTLY IF NOT EXISTS
hour_production_1_2018_07_01_domain_write_date_idx ON
hour_production_1_2018_07_01 (fk_domain, write_date)"

I need to create this index in this way because hour_production table is dinamically break into small parts.

Best Answer

CREATE INDEX CONCURRENTLY cannot run inside a transaction, and all functions are transactions, (but ordinary create index can).

Perhaps something like PG_AGENT can be leveraged to create the index upon command from your trigger.

https://www.pgadmin.org/docs/pgadmin4/3.x/pgagent.html

Alternatively you could do a regular create index when you create the table, an empty table will not be locked for long during a create index.

Related Question