How do you install the contrib packages?

[kibaekr]

I've looked everywhere, but I can't seem to find how to install the trigram and fuzzy contrib packages. I am currently using Ubuntu 11.10 and Postgres 9.1.3 (recently updated), but I can't figure out where exactly I am supposed to type 'CREATE EXTENSION' or 'gmake install.'

These are the comments I made while I was trying to figure it out, but I don't think it works still.
#1. go here http://www.cyberciti.biz/faq/linux-installing-postgresql-database-server/
#2. enter '$ su - postgres'
#3. password is either just enter, or you can define pword by 'sudo passwd postgres'
#4. '$ psql template1'
#5. 'CREATE EXTENSION pg_trgm'
#6. 'CREATE EXTENSION fuzzystrmatch'

cd /usr/share/postgresql/9.1/extension

[nertzy]

I should really write a wiki page for this.

Essentially the answer (for PostgreSQL 9.1 and up) is that you will need to run these SQL statements against the Rails database. Probably the easiest way to do this is to write a migration using the execute method.

class InstallSomeContribPackages < ActiveRecord::Migration
  def up
    execute "CREATE EXTENSION pg_trgm;"
    execute "CREATE EXTENSION fuzzystrmatch;"
  end

  def down
    execute "DROP EXTENSION pg_trgm;"
    execute "DROP EXTENSION fuzzystrmatch;"
  end
end

If this fails, then you will need to install the contrib packages through whichever mechanism is best for your OS. In Mac OS X, I use Homebrew to install PostgreSQL, which compiles and installs all the contrib packages by default. Ubuntu appears to have a package called postgresql-contrib.

Note that the contrib packages need to be present on the server running the database, which might be different than the server running the application. Heroku supports a few contrib packages, but I don't know the details on how to set them up there.

You will need to run them for each environment, and you will also want to change the schema format in your config/application.rb file (as explained in the migrations guide):

config.active_record.schema_format = :sql

[Peeja]

Boom.

[kibaekr]

Thank you! Do I need to add "config.active_record.schema_format = :sql", or is that only when the migration option fails? I read the migrations guide, and still had a difficult time understanding the true reasoning behind using :sql rather than :rb. I'm just afraid by changing it to :sql will cause some issues that I don't understand.

I am pretty new to RoR, so please be understanding of my questions :)

[kibaekr]

Update --> I created a migrations file like the one above and migrated, and also changed the schema format to :sql.
My code looks likes:

 pg_search_scope :search_by_weight, :against => {:title => 'B', :content => 'C'}, :using => [:tsearch, :trigram, :dmetaphone]

but I still get an error when I try searching

 PG::Error: ERROR:  function pg_search_dmetaphone(text) does not exist LINE 1: ...')) % 'tag') OR ((setweight(to_tsvector('simple', pg_search_...
                                                         ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts. : SELECT  "missions".*, (ts_rank((setweight(to_tsvector('simple', coalesce("missions"."title"::text, '')), 'B') || setweight(to_tsvector

p.s. It worked when I only used tsearch.

[nertzy]

You need to make the schema_format change so that when you use rake db:setup on new environments, they get the new extensions installed. Same thing for running rake db:test:prepare to set up your tests.

Essentially the default way Rails stores the schema does not have any knowledge or insight into how PostgreSQL uses extensions. The raw SQL format contains everything you need to quickly set up new databases for development, production, and testing.

[Peeja]

The :sql format dumps your entire schema, as Postgres understands it. That means you get everything, including triggers and functions and all sorts of things that ActiveRecord doesn't have a way to represent in a schema.rb file. The major downside is that it's no longer database independent, but if you're using pg_search, you're way past that. It also doesn't always diff as well as a schema.rb. All in all, you don't lose a lot with :sql, and it's the only way to get PG-specific things in there.

[kibaekr]

I see - so I have nothing to worry about in terms of (potentially) losing my production database out of ignorance? I looked at the available Heroku plugins, but wasn't too sure if Heroku would support the things I'm trying to use with PG_Search for free. It might be a good idea to specify whether Heroku supports it for free in the readme docs, as I'm assuming a lot of the ppl using gems don't really understand what's going on and just want to know how to do it and whether it's possible.

[zreitano]

I have all installed all the contrib packages. I can use the dmetaphone command manually with straight sql but when I try to use the command - Transaction.search_venue_name("Example Venue Name") I receive the error PG::UndefinedFunction: ERROR: function pg_search_dmetaphone(text) does not exist.

The code in my model looks like this:

Any help would be very much appreciated.

[zreitano]

Got it!

Update: The comment below was how I solved my issue. Sorry for not updating with the solution right away. Thanks for the catch.

[nertzy]

For future visitors with the same problem as @zreitano, you need to install the pg_search_dmetaphone function by generating a migration. The instructions are at https://github.com/Casecommons/pg_search#dmetaphone-double-metaphone-soundalike-search

[srussking]

Ran into this pg_search_dmetaphone problem, however the rails g migration command listed in the documentation was not working for me. If that happens to you, you can create a migration however you create them (I use rails g migration) then use the guts of the migration here:

https://github.com/Casecommons/pg_search/blob/master/lib/pg_search/migration/templates/add_pg_search_dmetaphone_support_functions.rb.erb

[Babbz75]

For some reason the contrib package migration is not working on heroku. Does someone have a solution for this?

[nodox]

I'm having the same problems as everyone else. I performed a migration and schema.rb update but I still get

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: function pg_search_dmetaphone(text) does not exist

[Babbz75]

@nodox Does you schema have these at the top:

enable_extension "plpgsql"
enable_extension "pg_trgm"
enable_extension "fuzzystrmatch"

I run into this problem every time I use pg_search and quite honestly I never know what fixes it. Possibly try to do:

bundle exec rake db:schema:load
bundle exec rake db:structure:load

Otherwise I've also solved it by doing this:

http://stackoverflow.com/questions/40956518/pg-search-dmetaphone-not-working-after-installing-contrib-package/40956519#40956519

[nodox]

@Babbz75 Thanks for the response. I got it working using the last comment on issue #175
I'm running rails 5.0.x

These steps worked for me:

1. db/schema.rb -> Added these lines manually. Not sure if it'll get overridden later.

ActiveRecord::Schema.define(version: xxx) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"
  enable_extension "pg_trgm"
  enable_extension "fuzzystrmatch"

2. Created a migration to enable the extensions in postgres

class InstallPgPackages < ActiveRecord::Migration[5.0]
  def up
    execute "CREATE EXTENSION IF NOT EXISTS pg_trgm"
    execute "CREATE EXTENSION IF NOT EXISTS fuzzystrmatch"
  end

  def down
    execute "DROP EXTENSION IF EXISTS pg_trgm"
    execute "DROP EXTENSION IF EXISTS fuzzystrmatch"
  end
end

3. Created this migration which might be redundant to step 1

class AddPGExtensions < ActiveRecord::Migration[5.0]
  def change
    enable_extension "pg_trgm" 
    enable_extension "fuzzystrmatch"
  end
end

4. Ran rails g pg_search:migration:dmetaphone to get

class AddPgSearchDmetaphoneSupportFunctions < ActiveRecord::Migration
  def self.up
    say_with_time("Adding support functions for pg_search :dmetaphone") do
      execute <<-'SQL'
CREATE OR REPLACE FUNCTION pg_search_dmetaphone(text) RETURNS text LANGUAGE SQL IMMUTABLE STRICT AS $function$
  SELECT array_to_string(ARRAY(SELECT dmetaphone(unnest(regexp_split_to_array($1, E'\\s+')))), ' ')
$function$;
      SQL
    end
  end

  def self.down
    say_with_time("Dropping support functions for pg_search :dmetaphone") do
      execute <<-'SQL'
DROP FUNCTION pg_search_dmetaphone(text);
      SQL
    end
  end
end

5. Reload rails server and pg server (as a counter measure)

[nertzy]

Here's our documentation for installing PostgreSQL extensions.