Problems with indexer query with Thinking Sphinx and Postgresql in productio

Question

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?

score 2 · Accepted Answer · answered Jun 24 '12 at 14:44

2

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

answered Jun 24 '12 at 14:44

kgrittn

18,113
3
39
47

On my localhost I'm not experiencing this problem. Where is the difference? – Nikolay Shebanov Jun 24 '12 at 20:09
1

I'm guessing they are different versions of PostgreSQL or the primary key definitions on the tables are not the same. – kgrittn Jun 24 '12 at 23:04

Problems with indexer query with Thinking Sphinx and Postgresql in productio

1 Answers1