Postgres GIN Index Not Creating Through Liquibase Scripts

Question

I am trying to create a GIN index through liquibase scripts.

CREATE INDEX IF NOT EXISTS index_name ON schema_name.table_name USING gin (column1, lower(column2) COLLATE pg_catalog."default" gin_trgm_ops)

Each time migration for the liquibae changeset fails with the following exception:

liquibase.exception.DatabaseException: ERROR: operator class "gin_trgm_ops" does not exist for access method "gin"

The problem is that this query works when run directly from pgAdmin query tool. There seems to be some issue when running this query from liquibase scripts.

I had to create the following extensions before creating the index from pgAdmin query tool. I am creating these extensions in the liquibase script too.

CREATE EXTENSION IF NOT EXISTS pg_trgm with schema public;

CREATE EXTENSION IF NOT EXISTS btree_gin with schema public;

Following is the part of my liquibase script that is creating the extensions and indexes. Btree index creating queries work well with the liquibase scripts. It fails with the above exception when trying to create GIN index.

CREATE EXTENSION IF NOT EXISTS pg_trgm with schema public;

CREATE EXTENSION IF NOT EXISTS btree_gin with schema public;

The following works:

CREATE INDEX IF NOT EXISTS index_name ON table_name USING btree (col1,col2 COLLATE pg_catalog."default") 

CREATE INDEX IF NOT EXISTS index_name ON table_name USING btree (col1, col2 COLLATE pg_catalog."default")

But this fails:

CREATE INDEX IF NOT EXISTS index_name ON table_name USING gin (col1, lower(col2) COLLATE pg_catalog."default" gin_trgm_ops)

score 3 · Answer 1 · edited Aug 21 '19 at 15:33

Wondering if liquibase is running the script, is not running with the public schema in the search path, resulting in an inability to find the gin_trgm_ops operator class you're using to create the index. The error message would certainly indicate this.

Either add it to the search path explicitly and try again (it should work), or create the extension in the pg_catalog schema so that its available implicitly. See here: https://www.postgresql.org/docs/9.1/ddl-schemas.html (section 5.7.5).

You can do this by running the following in place of your existing pg_trgm create extension command.

CREATE EXTENSION IF NOT EXISTS pg_trgm with schema pg_catalog;

This followed by your other commands should do the trick.

score 0 · Answer 2 · answered Feb 18 '22 at 22:34

0

Following worked for me instead of using createIndex statement in changeSet I used custom sql statement like

    <changeSet author="me" id="26">
        <sql>CREATE INDEX my_table_hours_idx ON my_table USING GIN(hours)</sql>
    </changeSet>

answered Feb 18 '22 at 22:34

Shrikant Prabhu

709
8
13

Postgres GIN Index Not Creating Through Liquibase Scripts

2 Answers2