How to Create Gin Indices Concurrently? POSTGRES & Rails 4.2

Question

I am following the thoughtbot tutorial on multi-index full-text search using postgresql and rails 4. And I can't seem to get the indices migrations to work. I have tried

  disable_ddl_transaction!  
  def change
    add_index(:cards, :object, using: 'gin', algorithm: :concurrently)
    add_index(:cards, :content, using: 'gin', algorithm: :concurrently)
    add_index(:tags, :name, using: 'gin', algorithm: :concurrently)     
  end

and

  def up
    ActiveRecord::Base.connection.execute <<-SQL
    CREATE INDEX CONCURRENTLY index_cards_on_object ON cards USING gin(to_tsvector('english', object));
    CREATE INDEX CONCURRENTLY index_cards_on_content ON cards USING gin(to_tsvector('english', content));
    CREATE INDEX CONCURRENTLY index_tags_on_name ON tags USING gin(to_tsvector('english', name));
   SQL
 end
 def down
    ActiveRecord::Base.connection.execute <<-SQL
    DROP INDEX index_cards_on_object;
    DROP INDEX index_cards_on_content;
    DROP INDEX index_tags_on_name;
  SQL
end

I get the following errors (rescpectively)

PG::UndefinedObject: ERROR: data type character varying has no default operator class for access method "gin"

and

ActiveRecord::StatementInvalid: PG::UndefinedObject: ERROR: data type character varying has no default operator class for access method "gin" PG::SyntaxError: ERROR: syntax error at or near "CREATE"

All help and insight welcomed, thank you

I'd look at the `postgresql` server log file to see what command is getting transmitted to the database. — jjanes, Jun 12 '15 at 19:03

score 1 · Answer 1 · answered Sep 17 '19 at 14:34

It's been more than 4 years since you asked this question, but still - I ended up in this thread when looking for the answer for the same exact issue anyway. As I've managed to cope with it, I'm sharing the solution with you and whoever else that ends up here.

Try adding these extensions to you change or up method in a migration file. Note that NOT EVERY ONE is needed, but I've seen that sometimes one is just not enough. btree_gin worked in my case. Just give it a try!

enable_extension "plpgsql"
enable_extension "btree_gin"
enable_extension "pg_trgm"
enable_extension "fuzzystrmatch"
enable_extension "btree_gist"

How to Create Gin Indices Concurrently? POSTGRES & Rails 4.2

1 Answers1