Hey, Pat. Thanks for the reply. Here's what I figured out. By taking the pieces of the query that TS generates for the Sphinx configuration file, I was able to isolate the source of the error to an element of the SELECT clause of the query: 'cast(extract(epoch from "patients"."birth_date") as integer) AS "birth_date"'. The issue is that some of the people whose birth dates I was trying to work with are very old, and therefore the seconds- since-epoch representation of their date is a large negative number, larger than -2^31.
When I tweaked the cast_to_datetime method in lib/thinking_sphinx/ adapters/postgresql_adapter.rb so that instead of casting to an integer, it casts to a bigint, the error is fixed. Sphinx generates the indexes, and life is beautiful again. (Note that I am using the id64-enabled version of Sphinx.) Summary: * TS casts date/time fields as 32-bit integers (holding seconds since the epoch). * For times far from the epoch, this can overflow the 32-bit limit. * This can be fixed by casting these as 64-bit integers instead. Hope this helps others; thanks again for such a great tool. Terry On Feb 24, 3:07 am, Pat Allan <[email protected]> wrote: > Hi Terry > > I wonder if it's actually PostgreSQL complaining about the values, not > Sphinx... can you try editing the SQL for that first column in the > development.sphinx.conf file to cast the result as a BIGINT? Run rake > ts:reindex instead of ts:index, as the latter will overwrite the manual > changes to the conf file. > > Cheers > > -- > Pat > > On 20/02/2010, at 8:46 AM, Terry wrote: > > > Pat, > > I've been really happy using thinking_sphinx for about a year now, so > > I appreciate your work on it. > > I'm having exactly the same error as JB; right down to the fact that > > we're both running against PostgreSQL databases. > > I tried switching to Sphinx 0.9.9-id64-release (r2117), and it doesn't > > seem to have helped. Do you have any other ideas about how I might > > address this? > > Thanks, > > Terry > > > On Feb 12, 7:53 pm, Pat Allan <[email protected]> wrote: > >> Hi JB > > >> The problem is that with the * 4, the results are bigger than 32 bits... > >> so you'll need to compile Sphinx with the --enable-id64 flag, to allow 64 > >> bit document ids. > > >> Give that a spin, see if it helps. > > >> Cheers > > >> -- > >> Pat > > >> On 13/02/2010, at 4:35 AM, johnnybutler7 wrote: > > >>> Hi, > > >>> I have 4 models to index, 3 of them work fine but 1 keeps hanging when > >>> building the indexes. Ive trawled the forums and read solutions for > >>> similar issues but these don't seem to work for me. > > >>> The problem table which hangs the indexing is called Category and has > >>> the appropriate id key field etc so should in essence work so i am a > >>> bit stumped on that one. > > >>> I have read about the issue with the large id's and this does apply to > >>> my tables. All 4 tables id's are pretty big for example 1900639709 is > >>> an id field for one of the models called product. This table seems to > >>> index ok but the category model who's id starts at 1600578709 just > >>> hangs. > >>> In the config file i see the id * 4 which is probably causing the > >>> issue. > > >>> Basically what can i do to fix this. Below is the output > > >>> distributed index 'host_application' can not be directly indexed; > >>> skipping. > >>> indexing index 'manufacturer_core'... > >>> ERROR: index 'manufacturer_core': sql_range_query: ERROR: integer out > >>> of range > >>> (DSN=pgsql://postgres:*...@localhost:2810/av3_live). > >>> total 0 docs, 0 bytes > >>> total 0.010 sec, 0.00 bytes/sec, 0.00 docs/sec > >>> distributed index 'manufacturer' can not be directly indexed; > >>> skipping. > >>> indexing index 'product_core'... > >>> ERROR: index 'product_core': sql_range_query: ERROR: integer out of > >>> range > >>> (DSN=pgsql://postgres:*...@localhost:2810/av3_live). > >>> total 0 docs, 0 bytes > >>> total 0.014 sec, 0.00 bytes/sec, 0.00 docs/sec > >>> distributed index 'product' can not be directly indexed; skipping. > >>> Loaded suite /opt/local/bin/rake > >>> Started > > >>> thanks > > >>> JB > > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "Thinking Sphinx" group. > >>> To post to this group, send email to [email protected]. > >>> To unsubscribe from this group, send email to > >>> [email protected]. > >>> For more options, visit this group > >>> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "Thinking Sphinx" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/thinking-sphinx?hl=en. -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
