ERROR: extension "btree_gist" must be installed in schema "heroku_ext"

Question

Heroku made a change to the way postgressql extensions gets installed

This is screwing up new rails review apps in heroku with the following error.

ERROR: extension "btree_gist" must be installed in schema "heroku_ext"

This is screwing up things as I need to drop existing extensions and re-enable with heroku_ext schema. I use bin/rails db:structure:load which is before running a migration.

Also the structure.sql is going to diverge as heroku add the schema in review app and we need to run the creation manually in local dev machine.

Does anybody came across this issue?

Answer 1

I have developed a monkey-patch solution that is, well, definitely a hack, but better than pre-dating migrations, or deleting lines from schema.rb.

Put this in config/initializers. I called mine _enable_extension_hack.rb to make sure it gets loaded early.

    module EnableExtensionHerokuMonkeypatch
      # Earl was here
      def self.apply_patch!
        adapter_const = begin
          Kernel.const_get('ActiveRecord::ConnectionAdapters::PostgreSQLAdapter')
        rescue NameError => ne
          puts "#{self.name} -- #{ne}"
        end
    
        if adapter_const
          patched_method = adapter_const.instance_method(:enable_extension)
    
          # Only patch this method if it's method signature matches what we're expecting
          if 1 == patched_method&.arity
            adapter_const.prepend InstanceMethods
          end
        end
      end
    
      module InstanceMethods
        def enable_extension(name)
          name_override = name
    
          if schema_exists?('heroku_ext')
            puts "enable_extension -- Adding SCHEMA heroku_ext"
            name_override = "#{name}\" SCHEMA heroku_ext -- Ignore trailing double quote"
          end
    
          super name_override
        end
      end
    end
    
    EnableExtensionHerokuMonkeypatch.apply_patch!

What does it do? It monkey-patches the enable_extension call that's causing the problem in db/schema.rb. Specifically, if it finds that there is a schema called "heroku_ext", it will decorate the parameter given to enable_extension so that the SQL that gets executed specifies the "heroku_ext" schema. Like this:

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" SCHEMA heroku_ext -- Ignore trailing double quote"

Without this hack, the generated SQL looks like this:

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"