Postgresql create index concurrently

Question

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.

• ypercubeᵀᴹ: The CREATE INDEX CONCURRENTLY statement cannot be inside a trigger - or a transaction.

Answer 1

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.

• Joag: PG_AGENT, I will try if this approach is able to solve my problem, Thanks. The problem with create a regular index is the number of insert that this table has, maybe a hundred of thousands of rows in a day .
• Luan Huynh: @Joag depending on size of inserted data and business (how often to query new partition table), I think you should consider create index when you create the table. Please note that create index concurrently would take a long time if your table has a lot activities.