6

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.

1
  • 5
    The CREATE INDEX CONCURRENTLY statement cannot be inside a trigger - or a transaction. Jul 28, 2018 at 0:41

1 Answer 1

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.

2
  • 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 .
    – Joag
    Jul 28, 2018 at 19:27
  • 2
    @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.
    – Luan Huynh
    Jul 30, 2018 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.