My Rails 5.1 CI tests have started failing with the following error:
bundle exec rake db:create db:structure:load
Created database 'my_test'
psql: .../structure.sql:72: ERROR: operator family "btree_hstore_ops" for access method "btree" already exists
rake aborted!
I'm in the process of updating a Rails app to version 5.1.
Rails 5.1 appears to introduce some changes to how model indexes are defined, including adding the following to structure.sql.
CREATE OPERATOR FAMILY btree_hstore_ops USING btree;
CREATE OPERATOR FAMILY gin_hstore_ops USING gin;
CREATE OPERATOR FAMILY gist_hstore_ops USING gist;
CREATE OPERATOR FAMILY hash_hstore_ops USING hash;
These appear to be the cause.
Has anyone encountered this issue? Is there a way to make CREATE OPERATOR FAMILY conditional and to check whether btree_hstore_ops already exists? Or should I be looking elsewhere to solve this?
Adding trace:
-> rake db:structure:load
Running via Spring preloader in process 78735
psql:/Users/me/code/myapp/db/structure.sql:72: ERROR: operator family "btree_hstore_ops" for access method "btree" already exists
rake aborted!
failed to execute:
psql -v ON_ERROR_STOP=1 -q -f /Users/me/code/myapp/db/structure.sql mw_development
Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/postgresql_database_tasks.rb:108:in `run_cmd'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/postgresql_database_tasks.rb:80:in `structure_load'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:223:in `structure_load'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:236:in `load_schema'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:255:in `block in load_schema_current'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:305:in `block in each_current_configuration'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:302:in `each'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:302:in `each_current_configuration'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/tasks/database_tasks.rb:254:in `load_schema_current'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activerecord-5.1.5/lib/active_record/railties/databases.rake:290:in `block (3 levels) in <top (required)>'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activesupport-5.1.5/lib/active_support/dependencies.rb:286:in `load'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activesupport-5.1.5/lib/active_support/dependencies.rb:286:in `block in load'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activesupport-5.1.5/lib/active_support/dependencies.rb:258:in `load_dependency'
/Users/me/.rvm/gems/ruby-2.4.3@myapp/gems/activesupport-5.1.5/lib/active_support/dependencies.rb:286:in `load'
-e:1:in `<main>'
Tasks: TOP => db:structure:load
(See full trace by running task with --trace)
This issue is not Rails related, it is PostgreSQL related. Even though dumping the database state as a sequence of SQL commands is a great way to replicate your schema, and it is mentioned in the Rails core documentation, it is as "blind-shooting" a little, as it suposes a clear state of your DB, which is not the case with you. The problem you are facing is that you may have used your DB previously and, by doing so you may have created some of the operators your application requires. If you have choosen to dump your database state as SQL commands loading these schemas is simply a question of executing the statements they contain. By definition, this will create a perfect copy of the database’s structure, but will not check your previous DB state before execution. What you are trying to do was already done, as the error message is saying. To avoid that change the queries to use ALTER OPERATOR FAMILY ... ADD like this:
ALTER OPERATOR FAMILY btree_hstore_ops USING btree ADD
ALTER OPERATOR FAMILY gin_hstore_ops USING gin ADD
ALTER OPERATOR FAMILY gist_hstore_ops USING gist ADD
ALTER OPERATOR FAMILY hash_hstore_ops USING hash ADD
Documentation states that ALTER OPERATOR FAMILY does not presently check whether the operator family definition includes all the operators and functions required by the index method, nor whether the operators and functions form a self-consistent set. It is the user's responsibility to define a valid operator family.