2

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?

Edit:

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)
Community
  • 1
  • 1
Andy Harvey
  • 12,333
  • 17
  • 93
  • 185
  • Are you trying to CREATE OPERATOR or OPERATOR FAMILY? – smn_onrocks Feb 20 '18 at 05:44
  • Thanks @smn_onrocks, I’m using `CREATE OPERATOR FAMILY` – Andy Harvey Feb 20 '18 at 08:07
  • but I must admit I don't know what this function means. It seems to have appears during a recent upgrade to Rails 5.1 – Andy Harvey Feb 20 '18 at 08:58
  • please go through the link https://www.postgresql.org/docs/9.1/static/sql-createopfamily.html – smn_onrocks Feb 20 '18 at 11:54
  • Thanks @smn_onrocks. Sorry, i wasn’t very clear. I don’t know what the `btree_hstore_ops` is doing, and why this has suddenly appeared where it wasn’t before. This is making it difficult to work out how to resolve the error I’m seeing above – Andy Harvey Feb 20 '18 at 12:07
  • Any chance you were upgrading PostgreSQL recently as well? https://www.postgresql.org/message-id/CAN5pzZj3%3DEApRHWhpsW-C2-gtSbgw5pn6kE4rT2rYS7R-8hb9Q%40mail.gmail.com – Greg Feb 22 '18 at 11:59
  • @meta thanks for the suggestion. I had come across this but eventually disregarded it because: 1. the link refers to a "does not exist" error rather than "already exists", and 2. my error is occurring across several machines and fresh databases. Debugging at this level is not my strength, but willing to dig back into this if you can suggest steps. On my dev machine, yes, recently upgraded from PSQL 9.6 > 10.2 – Andy Harvey Feb 22 '18 at 12:21

1 Answers1

2

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.

ErvalhouS
  • 4,178
  • 1
  • 22
  • 38
  • thanks @ervalhouS. This looks promising, but can I check the syntax. This is raising `syntax error at or near "NOT"` – Andy Harvey Feb 22 '18 at 17:33
  • 1
    I see your point about this being a Postgres issue, however these lines appear to have been inserted automatically by Rails during an upgrade to v5.1. If this is common issue, shouldn't Rails make these statements conditional? – Andy Harvey Feb 22 '18 at 17:40
  • The error is triggered by `/Users/me/code/myapp/db/structure.sql` which is not a Rails default file, maybe it is an old forgotten stuff in your project that is invoking a `execute` block, which is only a SQL string being executed. – ErvalhouS Feb 22 '18 at 17:46
  • 1
    yes you are correct. In fact, migrating the app from `structure.sql` back to `schema.rb` has been on the todo list. I have just made the change and it does remove the error. I still think that there must be a bug in how Rails 5.1 is treating `structure.sql`. While not a default file, dumping the schema to sql is a core setting http://guides.rubyonrails.org/v3.2.9/migrations.html#types-of-schema-dumps – Andy Harvey Feb 23 '18 at 00:38
  • `ALTER OPERATOR FAMILY btree_hstore_ops USING btree ADD` also works! However, please note that the trailing `;` cuases a syntax and error and must be removed. @ErvalhouS could you update your answer with this syntax change, and the structure.sql vs schema.rb issue. I will then mark your answer. – Andy Harvey Feb 23 '18 at 00:40