How does one on a mac with homebrew install PostgreSQL's, unaccent, function?

[Robert Jewell]

I am staring at a nasty error: 

ActiveRecord::StatementInvalid at /users/search

PG::UndefinedFunction: ERROR:  function unaccent(text) does not exist

LINE 1: ...LECT "users".*, ((ts_rank((to_tsvector('english', unaccent(c...

                                                             ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This is how I implemented the gem as a scoped method in the User model:

  include PgSearch

  pg_search_scope :search, :against => [:talent_type, :city, :state_abbreviation],

    using: {tsearch: {dictionary: "english"}},

    associated_against: {skills: :name}, 

    :ignoring => :accents

I've googled all over the place, but everything seems to come down to installing this package. However, I have homebrew which has installed postregesql so I really do not know what to do. I'm on a mac.

[Robert Jewell]

I've also implemented a migration file just as Ryan Bates does it in his rails cast:

class AddUnaccentExtension < ActiveRecord::Migration

  def up

    execute "create extension unaccent"

  end

  def down

    execute "drop extension unaccent"

  end

end

however, unfortunately I cannot role back this migration. I do not know if that's a bad thing. 

[Grant Hutchins]

That is the correct way to install / uninstall the unaccent extension. What errors are you seeing when you try to roll back?

Grant Hutchins

--

---
You received this message because you are subscribed to the Google Groups "Case Commons Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to casecommons-d...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Robert Jewell]

@Grant, thank you for your post. 

When I try to roll back the migration I see:

==  AddUnaccentExtension: reverting ===========================================

-- execute("drop extension unaccent")

rake aborted!

StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedObject: ERROR:  extension "unaccent" does not exist

: drop extension unaccent/Users/robertjewell/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `async_exec'

.......

Tasks: TOP => db:rollback

(See full trace by running task with --trace)

Here is the full stack trace. If I have installed postresql with homebrew and have implemented the above migration to implement the unaccent search functionality, then why am I running into this error when I write, :ignoring => :accents

ActiveRecord::StatementInvalid at /users/search

PG::UndefinedFunction: ERROR:  function unaccent(text) does not exist

LINE 1: ...LECT "users".*, ((ts_rank((to_tsvector('english', unaccent(c...

                                                             ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[Steven Harman]

That sounds like it's trying to rollback the migration, but the extension doesn't exist to be rolled back.

I often use migrations that look like this:

class AddPgStatStatementsExtension < ActiveRecord::Migration
  def up
    execute 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements'
  end

  def down
    execute 'DROP EXTENSION pg_stat_statements'
  end
end

[Robert Jewell]

@Steven thank you for your reply. 

I copied the code of your migration and pasted it over my own migration file.

ran rake:db:migrate

and everything appears to have gone well. When I try to execute a search with the options, :ignoring => :accents, in my User model, I'm running into the error:

PG::UndefinedFunction: ERROR:  function unaccent(text) does not exist

LINE 1: ...LECT "users".*, ((ts_rank((to_tsvector('english', unaccent(c...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Here's the error in terminal. As I mentioned before, I installed postgresql with homebrew and from what I've read online that is all that is required. Then again, it appears that this, PG::UndefinedFunction: ERROR:  function unaccent(text) does not exist, is the core problem, and I do not know how to rectify it. I've read this post by Grant, but I am not using ubuntu. Should I follow the advice that Grant adds at the end with regards to pg_search_dmetaphone?

I have no reason to roll back the migration. I was just trying to undo a migration because it appears the file is not allowing me to implement pg unaccent function. 

[Steven Harman]

Oh... the migration I posted was NOT to add unaccent, it was for `pg_stat_statements` (which is on by default in Postgres 9.3+). The point was that you can use the `IF NOT EXIST` to make your migrations more resilient.

What version of PostgreSQL did you install via Homebrew (and is your Homebrew up to date)? And what version of OS X are you on? I as b/c older versions of OS X came with their own version of PostgreSQL installed already, and that could be causing a conflict.

[Robert Jewell]

Doh! I apologize for that. My knowledge of SQL is not that strong so in these situations unfortunately I just google, google and google some more. I definitely need to get stronger with SQL. 

When I run, $ brew list --versions postgresql:

postgresql 9.3.0 9.3.4

When I run, $ brew info postgresql:

$ brew info postgresql

postgresql: stable 9.3.4 (bottled), devel 9.4beta1

http://www.postgresql.org/

Conflicts with: postgres-xc

/usr/local/Cellar/postgresql/9.3.0 (2913 files, 40M)

  Built from source

/usr/local/Cellar/postgresql/9.3.4 (2921 files, 38M) *

  Poured from bottle

From: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/postgresql.rb

==> Dependencies

Required: openssl ✔, readline ✔

Recommended: ossp-uuid ✔

OS X Version: 10.9.4

I just did, $ brew update:

$ brew update
Updated Homebrew from bdd9d8cc to 953a945b.
==> New Formulae
storm
==> Updated Formulae
davix       ejabberd       freeimage     ghc     ledger   orientdb   plt-racket premake scala       ume
docker       elasticsearch  gcc     git-annex     mess   passenger   postgresql riak simple-tiles   xmount

Should I switch postresql to the older version, 9.3.0  ?

[Steven Harman]

No, the newer version of postgres should be fine. You can run `brew cleanup` to remove old packages.

What version of postgres is actually running, and from where? On my system, I see the following:

$ which postgres

/usr/local/bin/postgres

$ postgres --version

postgres (PostgreSQL) 9.3.4

That is the Homebrew Installed Postgres.

Also, open a `psql` connection to your db and then list the installed extensions. Here's an example of what it might look like (be sure to replace DATABASE_NAME with your DB name).

$ psql -d <DATABASE_NAME>

psql (9.3.4)

Type "help" for help.

DATABASE_NAME=# \dx

                                         List of installed extensions

        Name        | Version |   Schema   |                            Description

--------------------+---------+------------+-------------------------------------------------------------------

 hstore             | 1.2     | public     | data type for storing sets of (key, value) pairs

 pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed

 pg_trgm            | 1.1     | public     | text similarity measurement and index searching based on trigrams

 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language

 unaccent           | 1.0     | public     | text search dictionary that removes accents

(5 rows)

[Robert Jewell]

test

Roberts-MacBook-Pro:local robertjewell$ postgres --version

postgres (PostgreSQL) 9.3.1

$ which postgres

/Applications/Postgres93.app/Contents/MacOS/bin/postgres

Roberts-MacBook-Pro:local robertjewell$ psql -d grassroots_development

psql (9.3.1, server 9.3.4)

Type "help" for help.

grassroots_development=# \dx

                 List of installed extensions

  Name   | Version |   Schema   |         Description          

---------+---------+------------+------------------------------

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(1 row)

grassroots_development=#

So it looks like I'm using an older version of postgres. Should I try to follow these instructions on this SO post, Homebrew install specific version of formula? -- to install a specific version of homebrew? Also, if I compare and contrast my extensions with yours, I see a bunch of extensions missing. Is this the problem?

 I recently uninstalled postgres and re-installed it following these instructions so I have no idea why it would install the older version upon freshly installing postgresql.

[Steven Harman]

$ which postgres

/Applications/Postgres93.app/Contents/MacOS/bin/postgres

It looks like you've got Postgres.app on you machine too. My suspicion is that you might actually have two instances running, and that's causing some confusion. You probably want to either go with Postgres.app, or the Homebrew-installed Postgres, not both.

[Robert Jewell]

O boy, that's a big oversight on my part. Thank you for pointing that out. I've removed it and I see the following now:

$ which postgres
/usr/local/bin/postgres

$ postgres --

version
postgres (PostgreSQL) 9.3.5

$ psql -d grassroots_development

psql (9.3.5)

Type "help" for help.

grassroots_development=# \dx

                 List of installed extensions

  Name   | Version |   Schema   |         Description          

---------+---------+------------+------------------------------

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(1 row)

grassroots_development=#

Despite running the migration file, I still do not have the unaccent function:

class AddUnaccentExtension < ActiveRecord::Migration

  def

up
    execute 'CREATE EXTENSION IF NOT EXISTS unaccent'
  end

  def down

    execute 'DROP EXTENSION unaccent'

  end
end

Should I do something like this? Thank you for your support, Steven. You have been tremendously helpful. 

[Steven Harman]

I might just try running the SQL right in the psql console...

[Robert Jewell]

Thank you so much Steven. I installed the function with:

grassroots_development=# CREATE EXTENSION unaccent;

CREATE EXTENSION

grassroots_development=# \dx

                         List of installed extensions

   Name   | Version |   Schema   |                 Description                  

----------+---------+------------+---------------------------------------------

 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language

 unaccent | 1.0     | public     | text search dictionary that removes accents

(2 rows)

The gem is working with the following options, :ignoring => :accents, 

I could not be happier. You have an awesome week, sir. 

&nbsp

...