search

pat / thinking-sphinx

Sphinx/Manticore plugin for ActiveRecord/Rails
http://freelancing-gods.com/thinking-sphinx
MIT License
1.63k stars 468 forks source link

Patch for Postgres Problem with large identifiers #97

Closed bsantschi-zz closed 13 years ago

bsantschi-zz commented 14 years ago

Hello

Thinking Sphinx has the following problem while indexing models with large identifiers with PostgreSQL databases: ERROR: index 'model_name_core': sql_range_query: ERROR: integer out of range

The following steps have fixed this problem for me:

I've tested it on the following environment:

Regards Bruno

eostrom commented 13 years ago

As has been discussed elsewhere, this problem can occur when using "foxy fixtures," or a database that has previously used them, or just with a tremendous number of records.

I have a fix that seems to solve the problem without requiring a special build of PostgreSQL: in sql_select_clause, I just cast the primary key as bigint before multiplying it. I'd be happy to submit a pull request, but before I try to write a test for it, I'd like to know if this sounds like a good or a bad idea.

pat commented 13 years ago

Took a little while, but I've just pulled Bruno's suggested patch in. Sorry about the delay. Erik, if you think it can be improved, feel free - although you'd need some way of checking whether it should be casted as a bigint.

eostrom commented 13 years ago

Bruno's patch looks good to me - and more comprehensive than whatever I had in mind.

kueda commented 13 years ago

I seem to be experiencing this issue with TS 1.4.6, Postgres 8.4.8, and Sphinx 2.0.1-id64-beta (r2792). I compiled sphinx with id64 support as recommended and updated to a version of TS after freelancing-god added the patch he mentioned (right?), and it seems like it's generating the patched SQL:

sql_query = SELECT "observations"."id" * 3::INT8 + 0 AS "id"

but I still get the error

ERROR: index 'observation_core': sql_range_query: ERROR:  integer out of range

Interestingly, "observations" is definitely not the biggest table I'm indexing, so it shouldn't have particularly large identifiers. I have another table that's 4x as big and it indexes fine. Is this an issue with Sphinx 2? I may try downgrading to see if that changes anything...

kueda commented 13 years ago

Ok, it looks like this was due to an enormous date epoch that wasn't getting cast to bigint. Looks like this was fixed in d9f3df980a6f8441a17b with the use_64_bit config option, which sadly doesn't look like it's available in a version of TS that's compatible with Rails 2.3.x. Bummer.

pat commented 13 years ago

Hi Ken-ichi

That commit is in the master branch as well as the Rails 3 branch - so it's available for Rails 1.2 onwards.