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.

Unanswerable

Is that a search for 'test' including quotes? – Gerek

No, the search query was just test – Unanswerable

Can you compare your local query and the one run on heroku (which you already posted). – Gerek

It is the same, character to character! – Unanswerable

@Unanswerable might be of some help devcenter.heroku.com/articles/… – Palma

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.

Anesthesiologist

Thanks for the quick answer. Appreciate it. – Unanswerable

Just chiming in that the scenario described above works as expected with PostgreSQL 8.4 on my local machine. – Sparky

Thanks for the update, kelly. I'll make sure to check up on that when I work on this issue. – Anesthesiologist

I updated my answer. Unfortunately, it's not possible with Heroku's PostgreSQL 8.3.11. – Anesthesiologist

It looks like heroku updated postgres to 9.0: devcenter.heroku.com/articles/heroku-postgres-documentation – Hackbut

@Hackbut That's the documentation for Heroku's standalone PostgreSQL service (postgres.heroku.com), which is different than the 8.3 database you get with usual PostgreSQL hosting. – Anesthesiologist

@nertzy where? If you found it somewhere else on their site then it's likely wrong. It clearly states 9.0.5 in the link I posted previously as well as here: postgres.heroku.com/pricing – Hackbut

@nertzy ah, I misunderstood your statement. I believe you have to be on cedar+ to get 9.x.x – Hackbut

@Hackbut No, even on Cedar you do not get 9.x for free. You must pay for standalone PostgreSQL service from Heroku to get 9.x. Indeed, the link you posted is to the stand-alone service at postgres.heroku.com – Anesthesiologist

@nertzy, chance: the newish dev plan for the heroku-postgresql addon is postgres 9.1 and free. This was not around when you commented, but is now. – Dunham