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.
CREATE INDEX CONCURRENTLY
statement cannot be inside a trigger - or a transaction.