Migration wont recognize hstore extension created on schema_search_path

Question

I´m trying to add a column using postgresql HStore.

Since I´m running a multi tenant app (using apartment gem), I´ve created the hstore extension on a dedicated schema, called "shared_extensions", as seen here: [https://github.com/influitive/apartment#installing-extensions-into-persistent-schemas][1]

I also added the shared_extensions schema to database.yml as:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  schema_search_path: "public,shared_extensions"

However, when I try to run rake db:migrate to add hstore column, I´m still receiving the error:

ActiveRecord::StatementInvalid: PG::UndefinedObject: ERROR:  type "hstore" does not exist

This is the hstore migration code:

class AddAdditionalInformationsToParties < ActiveRecord::Migration
  def change
    add_column :parties, :additional_informations, :hstore
  end
end

I´m not sure, but it looks like migrations are not recognizing the schema_search_path on database.yml file.

Answer 1

You need to enable the hstore extension in postgres.

Try running rails g migration add_hstore_extension, then edit it like below:

class AddHstoreExtension < ActiveRecord::Migration def self.up enable_extension "hstore" end def self.down disable_extension "hstore" end end

Note that you'll need that to run before the migration which uses it.

Answer 2

I ended up adding the extension to pg_catalog, which is always implicitly on search_path, as described by Craig Ringer in this post:

Create HSTORE with multiple schemas

CREATE EXTENSION hstore WITH SCHEMA pg_catalog;