Full-text search on Heroku using pg_search gem

Question

I've implemented full-text search using pg_search gem for my Rails application

My migration to create index looks like

execute(<<-'eosql'.strip)
  CREATE index mytable_fts_idx
  ON mytable
  USING gin(
    (setweight(to_tsvector('english', coalesce("mytable"."name", '')), 'A') ||
    ' ' ||
    setweight(to_tsvector('english', coalesce("mytable"."description",'')), 'B')
    )
  )
eosql

And my controller code looks like

pg_search_scope :full_text_search,
:against => [
  :name, :description],
:using => {
  :tsearch => {
    :prefix => true,
    :dictionary => "english",
    :any_word => true
  }
}

which works totally fine locally on Postgres 9.0.4. However, when I deploy the same to heroku and search for a sample query 'test', it throws up an error

PGError: ERROR:  syntax error in tsquery: "' test ':*"

SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "mytable"  WHERE (((to_tsvector('english', coalesce("mytable"."name", '')) || to_tsvector('english', coalesce("mytable"."description", ''))) @@ (to_tsquery('english', ''' ' || 'test' || ' ''' || ':*')))) LIMIT 12 OFFSET 0) subquery_for_count ):

Any suggestions on where I'm wrong and what I should be looking at to fix this error? Thanks.

Answer 1

I'm the main developer of pg_search. Sorry that you ran into that problem! Right now there is a pg_search bug when using :prefix searches against PostgreSQL 8.3 (the default for Heroku).

https://github.com/Casecommons/pg_search/issues/10

It's my top priority right now. I'm still figuring out the best way to get the test suite to run against both 8.x and 9.x.

Update: Unfortunately, :prefix searches don't work against PostgreSQL 8.3 at all. The functionality was introduced in 8.4. I've released pg_search 0.3.3 which improves the error message. Hopefully Heroku will upgrade to 9.0 across the board soon. I believe they want to do so, but they obviously can't just upgrade everyone wholesale without warning.