Search code examples
ruby-on-railspostgresqlthinking-sphinx

Problems with indexer query with Thinking Sphinx and Postgresql in productio

I am using Rails 3.2.3, Sphinx 2.0.1 beta and Postgresql 9.1.3 in development, with this index:

define_index do
  indexes :name
  indexes :city

  has :id
  has :gender_cd, as :gender, type: :integer
  has "extract(year from age(birthdate))", as: :age, type: :integer
  set_property delta: true
end

It indexes well in dev and works, searches etc. But when I deploy to my production server, I get the following from indexer:

(DSN=blah blah blah).
total 0 docs, 0 bytes
total 0.023 sec, 0 bytes/sec, 0.00 docs/sec
indexing index 'user_delta'...
ERROR: index 'user_delta': sql_range_query: ERROR:  column "users.birthdate" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ... "users"."gender_cd" AS "gender", extract(year from age(birthdate)...

I thought the problem was in 8.4 pgsql, but it remains even with 9.1.4. The sphinx version is newer than in dev, 2.0.4-release.

I know this issue with GROUP BY inclusion, that's specific when using Postgres, but why it's working in dev environment?

Solution

  • In PostgreSQL versions before 9.1 you need to GROUP BY all columns in the result which are not generated by an aggregate function. In version 9.1 and later, you can omit columns which are functionally dependent on a column which is included in the GROUP BY clause. So if the issue remains on 9.1, you might be missing a primary key definition.

    http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-GROUPBY