Why does add_index using 'gin' create a 'btree' index instead?

Question

I am on PostgreSQL 9.3.4 and Rails 4.0.4.

I add a "tags" column, and corresponding gin index (or, at least I ask for one).

class AddTagsToPhotos < ActiveRecord::Migration
  def change
    add_column :photos, :tags, :text, array: true, null: false, default: []
    add_index :photos, :tags, using: 'gin'
  end
end

Verify the results via psql:

psql=# \d photos
...
tags  | text[]  | not null default '{}'::text[]
Indexes:
    "index_photos_on_tags" btree (tags)

Notice that the "tags" index is of type btree - whereas I asked for gin.

Now manually create an index to show that gin is available:

psql=# create index index_photos_on_tags2 on photos using gin(tags) ;

psql=# \d photos
Indexes:
    "index_photos_on_tags" btree (tags)
    "index_photos_on_tags2" gin (tags)

Indeed, gin is available.

For the time being I am using this workaround with raw SQL, but would like to know why the typical approach above is failing:

class AddTagsToPhotos < ActiveRecord::Migration
  def up
    add_column :photos, :tags, :text, array: true, null: false, default: []
    ActiveRecord::Base.connection.execute('create index index_photos_on_tags on photos using gin(tags) ;')
  end
  def down
    ActiveRecord::Base.connection.execute('drop index index_photos_on_tags')
    remove_column :photos, :tags
  end
end

Note that there is another snag!

It turns out that db/schema.rb will not have gin set as the index type:

add_index "photos", ["tags"], :name => "index_photos_on_tags"

Potential interim workaround:

add_index "photos", ["tags"], :name => "index_photos_on_tags", using: :gin

Alert!

Until this bug is fixed, you must review changes to db/schema.rb whenever you run a migration, as all future migrations will strip using: :gin from the add_index line.

Confidant

as a workaround you might also want to specify this line in config/application.rb: config.active_record.schema_format = :sql otherwise this index, as you mentioned, will not be saved to db/schema.rb – Akene

This works correctly in Rails 4.2.0. – Placida

Note that execute is defined in ActiveRecord::Migration, so you don't need to specify ActiveRecord::Base.connection. – Apul

Answer 1

As paxer mentioned, you will need to set your schema format to :sql by adding (or changing) this line in your config/application.rb:

config.active_record.schema_format = :sql

The reason, as explained in the Rails Migrations guide, is that a GIN index is specific to Postgres. When you are using database-specific items, schema.rb will not be able to recreate them.

Here is a quote from the Rails guide:

There is however a trade-off: db/schema.rb cannot express database specific items such as triggers, or stored procedures. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database. If you are using features like this, then you should set the schema format to :sql.

Once you use the :sql format, your schema will now be saved to structure.sql instead of schema.rb. Both files can live side by side, but only structure.sql will be updated and used by the app when your format is set to :sql.

Taction